CODEWITHSUNDEEP
vbacsvscripting
Witloofdc
Witloofdc

Reputation: 81

VBA importing CSV file to OjbExcel

I've been trying to generate an excel by feeding it a .Csv file in VBA. We are using a inhouse business program that uses vba so I have to reference to objExcel.

This:

Function load_csv()
Dim objExcel
Dim objWorkbook
Dim baseSheet
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.workbooks.Add()
Set baseSheet = objWorkbook.worksheets(1)


With objExcel.ThisWorkbook.Sheets("Data").QueryTables.Add(Connection:="TEXT;FILENAME.csv", Destination:=Range("$A$1"))
    .Name = "CAPTURE"
    .fieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

End With
objExcel.ActiveWorkbook.SaveAs

Set objExcel = Nothing
Set objWorkbook = Nothing
Set baseSheet = Nothing
End Function

Gives me an application defined or object defined error.

What am I doing wrong?

Thank you for your time.

Upvotes: 1

Views: 135

Answers (1)

Tim Williams
Tim Williams

Reputation: 166521

This will be closer. You will still need to define all Excel constants such as xlInsertDeleteCells though. You can find their values in the VB editor Object Browser (press F2) in Excel. You should also provide the full path to the CSV file.

Function load_csv()

Const xlInsertDeleteCells = 1
Const xlDelimited = 1
Const xlTextQualifierDoubleQuote = 1

Dim objExcel As Object
Dim objWorkbook As Object
Dim baseSheet As Object
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.workbooks.Add()
Set baseSheet = objWorkbook.worksheets(1)


With baseSheet.QueryTables.Add(Connection:="TEXT;FILENAME.csv", _
                              Destination:=baseSheet.Range("$A$1"))
    .Name = "CAPTURE"
    .fieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

End With

objWorkbook.SaveAs "C:\Temp.xlsx" '<<<< needs a path
objWorkbook.Close False

Set objExcel = Nothing


End Function

Upvotes: 2

Related Questions