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

 

[johnwm]

If you're trying to do this in Excel, try posting in the VBA forum, or the Office forum. This is the VB5/6 forum. You will find links to those forums in the panel on the right of the page <Related Forums>

faq222-2244 may also guide you to ways of getting better answers. You may need to indicate where the error appears, and what it is, for instance

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'