Excel 2007 Macro: Open a file from any folder

[C C]

Hello. How do you code a macro so that you can pop up a file dialog screen
to open a worksheet from any folder of your choice?

Thanks in advance.

 

[Bernie Deitrick]

Same as always:

Dim myName As String

myName = Application.GetOpenFilename
If myName = False Then
MsgBox "You pressed cancel"
Exit Sub
End If
Workbooks.Open myName

HTH,
Bernie
MS Excel MVP

 

[C C]

Bernie, thanks. What I actually need is how to popup the
open file dialog box when in Importing a .csv file into Excel 2007.

Below is part of a macro I created to import a .csv invoice file created
from our mainframe. I want the filename to be variant. How do I do it?

Thanks in advance.

Here's part of my macro:
'''''
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;T:\My_Invoice\MM0426-20080226-1.CSV",
Destination:=Range("$A$1"))
.Name = "MM0426-20080226-1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "~"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

 

[Dave Peterson]

Dim myFileName as variant 'could be False (a boolean)

myfilename = application.getopenfilename("Text files, *.csv")
if myfilename = false then
exit sub
end if

....

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myfilename, _
Destination:=activesheet.Range("A1"))
....

 

[C C]

Thank you.

Dim myFileName as variant 'could be False (a boolean)

myfilename = application.getopenfilename("Text files, *.csv")
if myfilename = false then
exit sub
end if

...

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myfilename, _
Destination:=activesheet.Range("A1"))
....