Opening an Excel file with VBA

Erich Duff

Board Regular
Joined
Apr 20, 2005
Messages
129
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?

Code:
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
Erich

Why the API function?

Couldn't you just use Workbooks.Open?
 

Erich Duff

Board Regular
Joined
Apr 20, 2005
Messages
129
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,847
Messages
5,544,637
Members
410,627
Latest member
georgealice
Top