Excel 2007 Macro: Open a file from any folder

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

C C - 27 Feb 2008 14:28 GMT

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.

Reply to this Message

Bernie Deitrick - 27 Feb 2008 14:38 GMT

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

> 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.

Reply to this Message

C C - 27 Feb 2008 20:07 GMT

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

> Same as always:
>
[quoted text clipped - 15 lines]
>>
>> Thanks in advance.

Reply to this Message

Dave Peterson - 27 Feb 2008 20:24 GMT

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"))
....

> Bernie, thanks. What I actually need is how to popup the
> open file dialog box when in Importing a .csv file into Excel 2007.
[quoted text clipped - 57 lines]
> >>
> >> Thanks in advance.

Signature

Dave Peterson

Reply to this Message

C C - 27 Feb 2008 21:46 GMT

Thank you.

> Dim myFileName as variant 'could be False (a boolean)
>
[quoted text clipped - 72 lines]
>> >>
>> >> Thanks in advance.

Reply to this Message