Discussion Article

Subject:
Load file macro compatability with Excel 2000
Name:
Mitch Robertson
Email (spam proof):

Email the originator of this post
Date:
Tuesday, June 01, 2004
Hits:
70
Discussion (Terms and Conditions):

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

Sponsored by the best FTP application on the internet: