Excel 2003 and 2000 and VBA Macro
 

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

Excel 2003 and 2000 and VBA Macro
 

I don't have xl2k to find the parm that's new, but the solution to the second
part of the question is to declare newFN as a variant. It'll be a string if the
user doesn't cancel. It'll be a boolean (false) if the user hits cancel.

Can you get to the xl2k pc? And run the code? Maybe you'll see the parm that
doesn't work in xl2k (maybe it was added in xl2002 or xl2003???).

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 Variant '<-- changed this
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

Trobert1 wrote:

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

--

Dave Peterson

Excel 2003 and 2000 and VBA Macro
 

I am sure it will be because QueryTables has more arguments in 2003 than in
2000. Most are defaulted so I would strip them all out

Change

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

to

ActiveSheet.QueryTables.Add(Connection:="TEXT;" & NewFN,
Destination:=Range("B6"))

and see what it complains about on your machine.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trobert1" wrote in message
...
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