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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
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
 

Forum statistics

Threads
1,141,011
Messages
5,703,723
Members
421,311
Latest member
tanujath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top