vba - workbooks.open 拉错文件

Question

我正在编写一些将在按下按钮时运行的代码，打开一个 .csv 文件(从另一个程序导出)，复制一些信息，然后将其粘贴到第一个文件中。有时(并且只有某些时候)它实际上会粘贴来自不同文件的信息。它似乎在打开另一个文件时发生，但并不一致。我试着看看它是否与哪个窗口在顶部有关，或者我使用的最后一个窗口，但它仍然不一致。用户在使用此工作表时很可能会打开多个文件。

Public Sub getData()

Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet

Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet

Dim SCOBlankLine As Integer
Dim importBlankLine As Integer
Dim importLastLine As Integer 


'-------------------------------------------------------------
'Open file with data to be copied

vFile = Application.GetOpenFilename("Excel Files (*.csv*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)

'If Cancel then Exit
If TypeName(vFile) = "Boolean" Then
    Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)
End If

Debug.Print vFile
Debug.Print wbCopyFrom.FullName
Debug.Print wsCopyFrom.Name

最后拉取错误信息时的debug值:
文件路径\lc_equip_20180816103532.csv
文件路径\lc_equip_20180727090447.csv
lc_equip_20180727090447

想法？

Answer 1

基于发现它正在使用最近新打开的文件，我选择使用不同的方法打开文件并激活工作簿。从那以后尝试了很多文件打开组合，而且还没有失败。

对于任何感兴趣的人:

'-------------------------------------------------------------
'Open file with data to be copied

vFile = Application.GetOpenFilename("Excel Files (*.csv*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)

'If Cancel then Exit
If TypeName(vFile) = "Boolean" Then
    Exit Sub
Else

Workbooks.Open Filename:=vFile
Set wbCopyFrom = Application.ActiveWorkbook
Set wsCopyFrom = wbCopyFrom.Worksheets(1)
End If

Debug.Print vFile
Debug.Print wbCopyFrom.FullName
Debug.Print wsCopyFrom.Name