VBA importing CSV file to OjbExcel

Question

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.

ThisWorkbook is used to refer to the workbook containing the currently-running code . That's not applicable in this case so you should use ActiveWorkbook (as you have later in your posted code). Also unless you've included a reference to the Excel object library you can't reference Excels built-in constants without defining a value for them in your code. For example you code has no idea what the value of xlDelimited is, since that's defined in the Excel library. — Tim Williams
– Tim Williams, Commented Sep 7, 2015 at 15:20
Hi, thank you for your comment. I've changed the code to 'activeworkbook', now receiving subscript out of range error? — Witloofdc
– Witloofdc, Commented Sep 8, 2015 at 11:23

Tim Williams · Accepted Answer · 2015-09-08 20:53:34Z

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

Collectives™ on Stack Overflow

VBA importing CSV file to OjbExcel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related