T
Trobert1
I have two very quick questions; one that I hope has an easy answer.
Below is some code that I wrote to automate importing a text file in
to Excel. I am using Excel 2003. The people that I am to distribute
this to may or may not have Excel 2003. Some have 2000. My
spreadsheet works on my system, but when I tried it on a system
running 2000 I received errors. Can you tell me what I can do to
correct this? The second question is this: There is a section of my
code that checks to see if the user has cancelled their selection,
but
it gives me a run time error. Have I done something wrong here?
Option Explicit
Private Sub ImportTextFile_Click()
''
'' Code to import txt file for use with the Exceed Exception
Report
'' Written by: Travis Roberts
'' Date: 2/1/08
''
Dim NewFN As String
NewFN = Application.GetOpenFilename(FileFilter:="Test Files (*.txt),
*.txt", Title:="Please select a file")
'If NewFN = False Then
''They pressed Cancel
'MsgBox "Stopping becuase you did not select a file"
'Exit Sub
'End If
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & NewFN,
Destination:=Range("B6"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.Refresh BackgroundQuery:=False
End With
Columns("B:X").EntireColumn.AutoFit
End Sub
Below is some code that I wrote to automate importing a text file in
to Excel. I am using Excel 2003. The people that I am to distribute
this to may or may not have Excel 2003. Some have 2000. My
spreadsheet works on my system, but when I tried it on a system
running 2000 I received errors. Can you tell me what I can do to
correct this? The second question is this: There is a section of my
code that checks to see if the user has cancelled their selection,
but
it gives me a run time error. Have I done something wrong here?
Option Explicit
Private Sub ImportTextFile_Click()
''
'' Code to import txt file for use with the Exceed Exception
Report
'' Written by: Travis Roberts
'' Date: 2/1/08
''
Dim NewFN As String
NewFN = Application.GetOpenFilename(FileFilter:="Test Files (*.txt),
*.txt", Title:="Please select a file")
'If NewFN = False Then
''They pressed Cancel
'MsgBox "Stopping becuase you did not select a file"
'Exit Sub
'End If
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & NewFN,
Destination:=Range("B6"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.Refresh BackgroundQuery:=False
End With
Columns("B:X").EntireColumn.AutoFit
End Sub