K
Kieran1028
I have code for a macro (which I recorded) to import some data from
.txt file. I'd like to add to the front of the macro, a .getfileope
routine to prompt the user to select the text file to be imported.
I thought I could just replace all the instances of the path/filenam
in my recorded macro with the variable defined in the getfileope
routine, but that didn't work.
Any hints? Below I've pasted the two blocks of code I'm talkin
about.
(Yes, I'm a VBA newbie... but I used to program in Fortran and Matla
years ago...)
Sub testing()
'
' testing Macro
' Macro recorded 10/27/2004 by Kieran Coghlan
'
' Keyboard Shortcut: Ctrl+Shift+J
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Kieran\posturedata\AJ.txt", Destination:=Range("A1"))
.Name = "AJ"
.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 = 12085
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = """"
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.SaveAs Filename:="C:\Kieran\posturedata\AJTEST.xls"
_
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
...and the getfileopen block:
Dim FName As Variant
FName = Application.GetOpenFilename("Tex
files(*.TXT),*.TXT", , PatientFile)
(which I copied from another post here)
If I put that block at the top of the other block, I get a run-error a
the following line:
.Refresh BackgroundQuery:=False
Saying that the program can not find the text file for the quer
refresh. I don't understand this.
Thanks for any help,
-Kiera
.txt file. I'd like to add to the front of the macro, a .getfileope
routine to prompt the user to select the text file to be imported.
I thought I could just replace all the instances of the path/filenam
in my recorded macro with the variable defined in the getfileope
routine, but that didn't work.
Any hints? Below I've pasted the two blocks of code I'm talkin
about.
(Yes, I'm a VBA newbie... but I used to program in Fortran and Matla
years ago...)
Sub testing()
'
' testing Macro
' Macro recorded 10/27/2004 by Kieran Coghlan
'
' Keyboard Shortcut: Ctrl+Shift+J
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Kieran\posturedata\AJ.txt", Destination:=Range("A1"))
.Name = "AJ"
.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 = 12085
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = """"
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.SaveAs Filename:="C:\Kieran\posturedata\AJTEST.xls"
_
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
...and the getfileopen block:
Dim FName As Variant
FName = Application.GetOpenFilename("Tex
files(*.TXT),*.TXT", , PatientFile)
(which I copied from another post here)
If I put that block at the top of the other block, I get a run-error a
the following line:
.Refresh BackgroundQuery:=False
Saying that the program can not find the text file for the quer
refresh. I don't understand this.
Thanks for any help,
-Kiera