Selecting a file in a macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a macro that imports a file and makes necessary changes. The
file location and name are embedded in the macro and must be change prior to
running the macro.

How can I present a dialog box that allows the user to select the file to be
imported?

My current macro is below:

Sub FixWorksheet()
'
' FixWorksheet Macro
' Macro recorded 8/10/2007 by Robert Harris
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\server\folder\filename.txt" _
, Destination:=Range("A1"))
.Name = "filename"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 3, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 1, 2, _
1, 3, 1, 1, 3, 3)
.Refresh BackgroundQuery:=False
End With
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Range("AB2").Select
ActiveCell.FormulaR1C1 = _

"=IF(AND(OR(RC[-16]=""OTHER"",RC[-16]=""SELF""),LEFT(RC[-26],LEN(RC[-17]))=RC[-17]),IF(RC[-27]=0,"""",RC[-27]),"""")"
Range("AC2").Select
ActiveCell.FormulaR1C1 =
"=IF(AND(OR(RC[-17]=""OTHER"",RC[-17]=""SELF""),LEFT(RC[-27],LEN(RC[-18]))=RC[-18]),RC[-20],"""")"
Range("AB2:AC2").Select
Selection.AutoFill Destination:=Range("AB2:AC10000"), Type:=xlFillDefault
Columns("AC:AC").Select
Selection.NumberFormat = "m/d/yyyy"

'
End Sub

Thanks for your help. (I am just starting to explore programming so be as
specific as possible)
 
Hi Rob

You can use

GetOpenFilename together with the ChDirNet function
When you use ChDirNet you can start in the networkfolder

See how I use it in this macro
http://www.rondebruin.nl/txtcsv.htm


I use multiselect here to give the user a option to select for more files.
Set it to false for one file and change this line

If you do that

Change
If IsArray(TxtFileNames) Then

to

If TxtFileNames<> False Then
 
Thank you - Your code had exact the information required to resolve my issue.

Ron de Bruin said:
Hi Rob

You can use

GetOpenFilename together with the ChDirNet function
When you use ChDirNet you can start in the networkfolder

See how I use it in this macro
http://www.rondebruin.nl/txtcsv.htm


I use multiselect here to give the user a option to select for more files.
Set it to false for one file and change this line

If you do that

Change
If IsArray(TxtFileNames) Then

to

If TxtFileNames<> False Then



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Rob said:
I have created a macro that imports a file and makes necessary changes. The
file location and name are embedded in the macro and must be change prior to
running the macro.

How can I present a dialog box that allows the user to select the file to be
imported?

My current macro is below:

Sub FixWorksheet()
'
' FixWorksheet Macro
' Macro recorded 8/10/2007 by Robert Harris
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\server\folder\filename.txt" _
, Destination:=Range("A1"))
.Name = "filename"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 3, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 1, 2, _
1, 3, 1, 1, 3, 3)
.Refresh BackgroundQuery:=False
End With
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Range("AB2").Select
ActiveCell.FormulaR1C1 = _

"=IF(AND(OR(RC[-16]=""OTHER"",RC[-16]=""SELF""),LEFT(RC[-26],LEN(RC[-17]))=RC[-17]),IF(RC[-27]=0,"""",RC[-27]),"""")"
Range("AC2").Select
ActiveCell.FormulaR1C1 =
"=IF(AND(OR(RC[-17]=""OTHER"",RC[-17]=""SELF""),LEFT(RC[-27],LEN(RC[-18]))=RC[-18]),RC[-20],"""")"
Range("AB2:AC2").Select
Selection.AutoFill Destination:=Range("AB2:AC10000"), Type:=xlFillDefault
Columns("AC:AC").Select
Selection.NumberFormat = "m/d/yyyy"

'
End Sub

Thanks for your help. (I am just starting to explore programming so be as
specific as possible)
Click to expand...
Click to expand...
 
Back
Top