Importing a CSV via macro

[Rkeev]

Hello all,

I am trying to edit code from an existing module that I inherited as a task.

Can anyone tell me why this code errors out when trying to import a CSV?

On Error GoTo 0
    tmpFileType = Right(fd.SelectedItems(lngItems), Len(fd.SelectedItems(lngItems)) - InStrRev(fd.SelectedItems(lngItems), "."))
    Debug.Print "File Type = " & tmpFileType
        Select Case tmpFileType
            Case "txt"
                DoCmd.TransferText acImportDelim, "", "TBL_IMPORT", fd.SelectedItems(lngItems), True, ""
            Case "xlsx"
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBL_IMPORT", fd.SelectedItems(lngItems), False, ""
            Case Else
                MsgBox "Could not determine file type"
        End Select

works on xlsx files but the csv errors out???

Any help would be appreciated

thx
Rkeev

 

[=ODIN=]

The code is set to work for only .txt and .xlsx files. You can try a few things that might work:

-change the csv extension to either of the above
- try this code:

On Error GoTo 0
    tmpFileType = Right(fd.SelectedItems(lngItems), Len(fd.SelectedItems(lngItems)) - InStrRev(fd.SelectedItems(lngItems), "."))
    Debug.Print "File Type = " & tmpFileType
        Select Case tmpFileType
            Case "txt"
                DoCmd.TransferText acImportDelim, "", "TBL_IMPORT", fd.SelectedItems(lngItems), True, ""
            Case "csv"
                DoCmd.TransferText acImportDelim, "", "TBL_IMPORT", fd.SelectedItems(lngItems), True, ""
            Case "xlsx"
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBL_IMPORT", fd.SelectedItems(lngItems), False, ""
            Case Else
                MsgBox "Could not determine file type"
        End Select

-try this code:

On Error GoTo 0
    tmpFileType = Right(fd.SelectedItems(lngItems), Len(fd.SelectedItems(lngItems)) - InStrRev(fd.SelectedItems(lngItems), "."))
    Debug.Print "File Type = " & tmpFileType
        Select Case tmpFileType
            Case "txt"
                DoCmd.TransferText acImportDelim, "", "TBL_IMPORT", fd.SelectedItems(lngItems), True, ""
            Case "csv"
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBL_IMPORT", fd.SelectedItems(lngItems), False, ""
            Case "xlsx"
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBL_IMPORT", fd.SelectedItems(lngItems), False, ""
            Case Else
                MsgBox "Could not determine file type"
        End Select

 

[Rkeev]

Thanks ODIN, I tried that but didn't seem to work.