load file macro compatability with Excel 2000

[mitchellr]

The following code was designed using a macro in Excel 2002.

It is designed so that when a user clicks a command button it exports external data from a .csv file.

This code is not running however in Excel 2000.

Am hoping to find a solution that will work in either Excel 97 or 2000 for our client.

My coding is not quite finished so excuse any poor commenting!

Appreciate any assistance you may be able to provide

Thanks!!






Private Sub cmdExSummary_Click()
Dim msgalert As Integer
On Error GoTo General_error
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section of code clears the exSummary sheet of Data ready for the new load
msgalert = MsgBox("BEFORE PROCEEDING. Please ensure the receipts sheet you wish to load from is in the correct format is named ccms_receipts with normal .csv extension.", vbOKCancel, "Load receipts file")
If msgalert = 1 Then

Sheets("1st receipts").Select
ActiveSheet.Cells.Select
Selection.Delete Shift:=xlUp
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section of code populates the exSummary sheet
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\ACE\ccms_receipts.csv", Destination:=Sheets( _
"1st receipts").Range("A1"))
.Name = "ExternalData_3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(11, 7, 4, 4)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("1st receipts").Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

MsgBox "Load complete"
End If
Exit Sub
General_error:
MsgBox "Problem Opening File"
Exit Sub
End Sub

 

[PHV]

This code is not running however in Excel 2000
Any error message you could post ?
Try to comment out this line:
.TextFileTrailingMinusNumbers = True

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244