VBA type mismatch error in QueryTables

Question

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

score 2 · Accepted Answer · 2017-08-24 13:03:39Z

2

Try...

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

Hope this helps!

edited Aug 24, 2017 at 13:03

answered Aug 24, 2017 at 3:54

user6585761

Sign up to request clarification or add additional context in comments.

2 Comments

Khaynes Over a year ago

Thank you very much. Helps a lot!

user6585761 Over a year ago

I've edited my post as per YowE3K's remarks regarding named parameters.

YowE3K · Accepted Answer · 2017-08-24 04:20:40Z

1

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.)

answered Aug 24, 2017 at 4:20

YowE3K

24k7 gold badges28 silver badges45 bronze badges

1 Comment

Khaynes Over a year ago

Thank you for the very thorough answer and additional info, it certainly helps with my very limited vba knowledge.

Collectives™ on Stack Overflow

VBA type mismatch error in QueryTables

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related