CODEWITHSUNDEEP
vba
Khaynes
Khaynes

Reputation: 1986

VBA type mismatch error in QueryTables

I'm getting a type mismatch range error at line 8. Can anyone please so kindly explain what I'm doing wrong?

Thanks

Set objExcel = CreateObject("Excel.application")
Set objWorkbook = objExcel.Workbooks.Add()
Set objSheet = objWorkbook.Worksheets.Add
objExcel.Visible = True
objExcel.DisplayAlerts = True
objExcel.Workbooks.Add(1)

With objExcel.ActiveSheet.QueryTables.Add(Connection="TEXT;C:\temp\file.csv", Destination=Range("$A$1"))
    '.CommandType = 0
    .Name = "test_ITS_ExtractX"
    .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 = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2, 2)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery=False
End With

objExcel.ActiveWorkbook.SaveAs Filename="c:/temp/file.xlsx", FileFormat=xlOpenXMLWorkbook, CreateBackup=False

objExcel.DisplayAlerts = True objExcel.Quit

Upvotes: 0

Views: 412

Answers (2)

Domenic
Domenic

Reputation: 8104

Try...

With objExcel.ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\temp\file.csv", Destination:=objExcel.ActiveSheet.Range("$A$1"))

Hope this helps!

Upvotes: 2

YowE3K
YowE3K

Reputation: 23974

If your code actually is written in one of the VBA variants, the named parameters should be specified using := instead of just =. This generates a "Type mismatch" error when I run your code.

You also have an unqualified Range object (see answer by Domenic), which will cause problems once you correct the syntax errors.

So the correct line should be:

With objExcel.ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\temp\file.csv", _
                                          Destination:=objExcel.ActiveSheet.Range("$A$1"))

and one of your later lines should be

objExcel.ActiveWorkbook.SaveAs Filename:="c:\temp\file.xlsx", _
                               FileFormat:=xlOpenXMLWorkbook, _
                               CreateBackup:=False

and I just noticed

.Refresh BackgroundQuery:=False

(Note: Please ensure that the xlOpenXMLWorkbook constant is available in whichever application you are running this in. If not, you will need to assign the correct value to it yourself. The same will apply for xlInsertDeleteCells, etc.)

Upvotes: 2

Related Questions