excel - 编译 Excel VBA 脚本以修改另一个工作簿中的连接属性

Question

我有一个工作簿，其中包含一个宏，我希望用它来更新另一个工作簿中的连接位置。 VBA 脚本创建一个文件夹，并在其中填充一个包含名为 log.txt 的数据的日志文件和一个 Excel 文件的副本，该文件已预先格式化以填充数据，从而允许用户查看图表和数据的详细分割。它是一个开门日志，跟踪门的使用次数。

这是我迄今为止编写的 VBA 代码。注意:我用 C++ 编程了几年，但已经有十年没有碰过它了。我尝试搜索代码，甚至记录手动刷新连接时所采取操作的宏。但是，如果我尝试使用该代码，它会给出“运行时错误 1004”应用程序定义或对象定义错误。

这是代码。底部注释掉的位是手动更改连接记录的宏的结果。

我们将不胜感激任何帮助。

Sub Lof_File_Macro()
' Log_file_Macro Macro
' Runs script for monthly counts    '
Dim strfolder1, strmonthno, strmonth, stryear, strfoldername, strfile, strmonyr, stlogfile, strfutfile    
'date strings defined using date functions - ofset for 28 days to allow running anytime within 20 days into the next month whilereturning correct month
strmonthno = Month(Date - 28)
strmonth = MonthName((strmonthno), True)
stryear = Year(Date - 28)    

strmonyr = " " & strmonth & " " & stryear
strfolder = "C:\Users\jtaylor7\Desktop\futures\People Counter" & strmonyr
strfile = "Futures People" & strmonyr & ".xls"    

strlogfile = strfolder & "\" & "log" & strmonyr & ".txt"
strfutfile = strfolder & "\" & strfile   

MkDir (strfolder)    

FileCopy "C:\Users\jtaylor7\Desktop\futures\log.log", strlogfile
FileCopy "C:\Users\jtaylor7\Desktop\futures\template.xls", strfutfile    

'Workbooks.Open Filename:=strfutfile
'ActiveWorkbook.Connections.AddFromFile (strlogfile)
'
'     
' Perform data connection modification on file

''    Windows(strfutfile).Activate
'    With ActiveWorkbook.Connections("log")
'        .Name = "log"
'        .Description = ""
'  End With
'    Range("$A$1:$H$1").Select
'With Selection.QueryTable
'        .Connection = "TEXT;strlogfile"
'        .TextFilePlatform = 850
'        .TextFileStartRow = 1
'        .TextFileParseType = xlDelimited
'        .TextFileTextQualifier = xlTextQualifierDoubleQuote
'        .TextFileConsecutiveDelimiter = False
'        .TextFileTabDelimiter = False
'        .TextFileSemicolonDelimiter = False
'        .TextFileCommaDelimiter = True
'        .TextFileSpaceDelimiter = False
'        .TextFileOtherDelimiter = "/"
'        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
'        .TextFileTrailingMinusNumbers = True
'        .Refresh BackgroundQuery:=False
'    End With
'    Range("I4").Select
'    ActiveWorkbook.Connections("log").Refresh
''    Windows("Run Me.xls").Activate
    '
End Sub

我知道它有点困惑，如果有人需要任何进一步的数据，请询问。

Answer 1

像这样的事情应该可以解决问题。

请根据我下面的测试更新您的路径

Sub LogFile_Macro()
Dim strFolder As String
Dim strMonthno As String
Dim strMonth As String
Dim strYear As String
Dim strFoldername As String
Dim strFile As String
Dim strMonyr As String
Dim strLogfile As String
Dim strFutfile As String
Dim wb As Workbook

'date strings defined using date functions - ofset for 28 days to allow running anytime within 20 days into the next month whilereturning correct month
strMonthno = Month(Date - 28)
strMonth = MonthName((strMonthno), True)
strYear = Year(Date - 28)
strMonyr = " " & strMonth & " " & strYear
strFolder = "C:\temp\People Counter" & strMonyr
strFile = "Futures People" & strMonyr & ".xls"

strLogfile = strFolder & "\" & "log" & strMonyr & ".txt"
strFutfile = strFolder & "\" & strFile

On Error Resume Next
MkDir strFolder
If Err.Number <> 0 Then
    MsgBox "cannot create path", vbCritical
    Exit Sub
End If
On Error GoTo 0

FileCopy "C:\temp\futures\log.log", strLogfile
FileCopy "C:\temp\futures\template.xls", strFutfile

Set wb = Workbooks.Open(strFutfile)
With wb.Sheets(1).QueryTables.Add(Connection:="TEXT;" & strLogfile, Destination:=Range("A1:H1"))
    .Name = "log"
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileCommaDelimiter = True
    .TextFileOtherDelimiter = "/"
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    .Refresh
End With
Windows("Run Me.xls").Activate
End Sub