使用VBA有条件自动命名excel文件 [英] conditionally auto name excel file using VBA

问题描述

我有以下VB代码，该代码使用数据库默认文件的某些单元格值(用于固定位置)来命名当前工作表，并使用ActiveX按钮将其保存在特定的文件夹中.但是，原始文件中的列位置开始有所不同，VB范围不再选择正确的数据来自动命名该文件.我创建了公式(在下面进一步介绍)，以查找用于自动命名文件所需的正确值.如何将这些公式插入我的VB代码中以完成工作?

I have following VB code that uses certain cell values (used to be fixed location) of a DB default file to name the current worksheet and save it in a specific folder with the use of an ActiveX button. However, column locations started varying in original file and VB ranges no longer pick the correct data to auto name the file. I created formulas (further below) to find the correct values I need to use to auto name the file. How do I insert those formulas in to my VB code for it to do job?

Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String
Dim str As String, strLeft As String
str = Range("A7")
strLeft = Left(str, 9)
FileName1 = strLeft
FileName2 = RepCh(Range("B7").Value)
FileName3 = RepCh(Range("C7").Value)
Path = "…"
ActiveWorkbook.SaveAs FileName:=Path & FileName1 & "_" & FileName2 & "_" & FileName3 & "_" & ".xls", FileFormat:=xlCSV
End Sub

公式:

=LEFT(INDEX($A$7:$AZ$7,MATCH("Departure",$A$6:$AZ$6,0)), SEARCH("",INDEX($A$7:$AZ$7,MATCH("Departure",$A$6:$AZ$6,0)),9))

=INDEX($A$7:$AZ$7,MATCH("Vessel Name",$A$6:$AZ$6,0))

=INDEX($A$7:$AZ$7,MATCH("Voyage Number",$A$6:$AZ$6,0))

推荐答案

还有比我更有效的方法，但有时我只是将公式写入字符串变量，然后保存该字符串变量.

There's more efficient ways than mine, but sometimes I just write formulas to a string variable, and save off the string variable.

但是，当您使用"时，则需要将公式拆分为多个字符串，并在每次使用引号时将其与&连接. (即，将每个"替换为Chr(34))

However when you have " , then you need to split your formula into multiple strings and concatenate with & each time you have a quotation mark. (i.e - replace every " with Chr(34))

以您的顶级公式为例:

Dim vFormula1 As String
Dim vFormula2 As String
Dim vFormula3 As String

'Set up formula into string variable
vFormula1 = "=LEFT(INDEX($A$7:$AZ$7,MATCH(" & Chr(34) & "Departure" & Chr(34) & ",$A$6:$AZ$6,0)), SEARCH(" & Chr(34) & Chr(34) & ",INDEX($A$7:$AZ$7,MATCH(" & Chr(34) & "Departure" & Chr(34) & ",$A$6:$AZ$6,0)),9))"
vFormula2 = "=INDEX($A$7:$AZ$7,MATCH(" & Chr(34) & "Vessel Name" & Chr(34) & ",$A$6:$AZ$6,0))"
vFormula3 = "=INDEX($A$7:$AZ$7,MATCH(" & Chr(34) & "Voyage Number" & Chr(34) & ",$A$6:$AZ$6,0))"


'Then Put formula into a cell range
'Set sheet range here if needed
Range("A1") = vFormula1
Range("A2") = vFormula2
Range("A3") = vFormula3

'Then save code goes here
    'blah blah blah save files.

这篇关于使用VBA有条件自动命名excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！