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

--
Kieran102
-----------------------------------------------------------------------
Kieran1028's Profile: http://www.excelforum.com/member.php...fo&userid=1567
View this thread: http://www.excelforum.com/showthread.php?threadid=27289

here is a snippet demo using a userform and the
commondialog object

Screen.MousePointer = vbHourglass
Dim ApExcel As Object
Set ApExcel = CreateObject("Excel.application")
On Error GoTo ErrHandler1
CommonDialog1.ShowOpen
ApExcel.Workbooks.Open CommonDialog1.FileName

maybe this will get u started.


-----Original Message-----

I have code for a macro (which I recorded) to import some
data from a
.txt file. I'd like to add to the front of the macro,
a .getfileopen
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/filename
in my recorded macro with the variable defined in the
getfileopen
routine, but that didn't work.

Any hints? Below I've pasted the two blocks of code I'm
talking
about.

(Yes, I'm a VBA newbie... but I used to program in
Fortran and Matlab
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("Text
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 at
the following line:
.Refresh BackgroundQuery:=False
Saying that the program can not find the text file for
the query
refresh. I don't understand this.

Thanks for any help,
-Kieran


--
Kieran1028
----------------------------------------------------------
--------------
Kieran1028's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=15678
View this thread:
http://www.excelforum.com/showthread...hreadid=272897

.