CODEWITHSUNDEEP
ms-accesscsvvba
user2395059
user2395059

Reputation: 80

MS Access: Import Specification not working through VBA

I have an import specification for csv files which, when I run it on a file through the GUI, works just fine. However, when I run it through VBA, for some reason it forgets that one column is supposed to be a Text column and makes it a Number column instead, thereby causing tons of errors.

My code is below. It works in the sense that everything runs properly, but for some reason the import specification for the CSVs does not run properly. The meaningless case switch is a place holder, as I will need to add more types of reports after I get the first working.

Sub ImportDE(Folder As Object)
Dim db As DAO.Database
Dim names As DAO.Recordset
Dim Files As Object, file As Object, SubFolders As Object, subfolder As Object
Dim ExString As Variant
Dim check As Boolean
Dim FileChange As String

Set db = CurrentDb
On Error Resume Next:   db.TableDefs.Delete "tblImport":   On Error GoTo 0
db.TableDefs.Refresh

Set names = CurrentDb.OpenRecordset("SELECT Old FROM DENames")

Set Files = Folder.Files
Set SubFolders = Folder.SubFolders

For Each subfolder In SubFolders
    ImportDE subfolder
Next

With names
    Do While Not .EOF
        ExString = .Fields(0)
        For Each file In Files
            If InStr(file.Type, "Worksheet") > 0 Then
                If InStr(file.Path, ExString & ".xls") > 0 Then
                    DoCmd.TransferSpreadsheet _
                        TransferType:=acImport, _
                        SpreadsheetType:=acSpreadsheetTypeExcel9, _
                        TableName:="tblImport_" & ExString, _
                        filename:=file.Path, _
                        HasFieldNames:=True, _
                        Range:="A:CT"
                    db.TableDefs.Refresh
                End If
            ElseIf InStr(file.Type, "Comma Separated") > 0 Then
                If InStr(file.Path, ExString & ".csv") > 0 Then
                    Select Case ExString
                    Case "Usage"
                        DoCmd.TransferText _
                            TransferType:=acImportDelim, _
                            SpecificationName:=UsageCSV, _
                            TableName:="tblImport_" & ExString, _
                            filename:=file.Path, _
                            HasFieldNames:=True
                        db.TableDefs.Refresh
                    End Select
                End If
            End If
        Next
        .MoveNext
    Loop
End With
db.Close:   Set db = Nothing
End Sub

Am I missing something obvious? Why doesn't the import spec work properly?

Upvotes: 1

Views: 3846

Answers (2)

Simon Van Duffelen
Simon Van Duffelen

Reputation: 21

At the top of your code module After Option Compare Database Add Option Explicit. This will require all of your variable to be declared and you will never have this issue again

Upvotes: 2

HansUp
HansUp

Reputation: 97101

The TransferText SpecificationName parameter is supposed to be a string expression. Since the code does not declare a variable named UsageCSV, I'm guessing that is the literal name of the specification. If that's correct, enclose the name in double quotes.

DoCmd.TransferText _
    TransferType:=acImportDelim, _
    SpecificationName:="UsageCSV", _
    TableName:="tblImport_" & ExString, _
    filename:=file.Path, _
    HasFieldNames:=True

Upvotes: 2

Related Questions