Opening an Excel file with VBA

[Erich Duff]

All,

I have the following code in a master workbook that I use to open a folder and then extract a file from it, but it seems to do some weird things: it treats the opened file as if it's another application and, for instance, I can't do a vlookup between that file and another one. There are other inter-workbook functions that it won't let me use as well. Any ideas about why that's happening? Is there other code I could be using? Anybody else have this problem?

Private Declare Function ShellExecute _
    Lib "shell32.dll" Alias "ShellExecuteA" ( _
    ByVal hwnd As Long, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, _
    ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Private Sub CommandButton7_Click()
Dim flOpen As String

ChDrive "G"
ChDir "G:\Inventory Turn Report"
flOpen = Application.GetOpenFilename( _
    fileFilter:="All Files (*.*), *.*")
If Not flOpen = "False" Then
    myDir = Left(test, InStrRev(test, "\"))
    Call ShellExecute(Application.hwnd, "Open", _
        Dir(flOpen), vbNullString, _
        myDir, 3)
End If
End Sub

 

[Norie]

Erich

Why the API function?

Couldn't you just use Workbooks.Open?

 

[Erich Duff]

Hi Norie,

I use that code because the folder may contain other types of files that I want to open as well, like PDF's or Word docs.

Erich