Solved

Type Mismatch Error With xl.QueryTables.Add

Posted on 2001-08-10
19
377 Views
Last Modified: 2008-10-13

The following creates an Excel object, clears a specific range the imports a commas-delimited text file.
Works great the first time; no problems.  When I perform the action again, it creates an Excel object, clears a specific range then returns a 'type mismatch' error when it tries to set the '...Destination:=Range(adoRS(2)))' to import the text file.  Yes I'm storing cell range values in a SQL database.

Of course ending the application and starting is apparently clear something residxent...but what?

Help!

  Set xl = CreateObject("Excel.Application")
  With xl
     .Workbooks.Open cmd.FileName
     .Visible = True
     .Sheets("Preliminary").Select
     .Range(adoRS(0)).Select
     .Selection.ClearContents
     .Range(adoRS(1)).Select
  End With

  With xl.ActiveSheet.QueryTables.Add(Connection:="TEXT;" & ExcelImportFile, Destination:=Range(adoRS(2)))
     .Name = LCase(cboTemplates.Text) & "_1"
     .FieldNames = True
     .RowNumbers = False
     .FillAdjacentFormulas = False
     .PreserveFormatting = True
     .RefreshOnFileOpen = False
     .RefreshStyle = xlInsertDeleteCells
     .SavePassword = False
     .SaveData = True
     .AdjustColumnWidth = True
     .RefreshPeriod = 0
     .TextFilePromptOnRefresh = False
     .TextFilePlatform = xlWindows
     .TextFileStartRow = 1
     .TextFileParseType = xlDelimited
     .TextFileTextQualifier = xlTextQualifierDoubleQuote
     .TextFileConsecutiveDelimiter = False
     .TextFileTabDelimiter = False
     .TextFileSemicolonDelimiter = False
     .TextFileCommaDelimiter = True
     .TextFileSpaceDelimiter = False
     .TextFileColumnDataTypes = Array(1)
     .Refresh BackgroundQuery:=False
  End With

  Do Until xl.Workbooks.Count = 0
     DoEvents
  Loop
 
  xl.Workbooks.Close
  xl.Quit
  Set xl = Nothing
0
Comment
Question by:BWATERS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
  • +3
19 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 6372477
Hi BWaters,

What is the content/value of adoRS(2) ?
0
 

Author Comment

by:BWATERS
ID: 6373832
adoRS(0)='C13:AA500'
adoRS(1)='C13:C13'
adoRS(2)='C13'
The thing that gets me is that it performs perfectly the first time in...sigh...
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6374302
Interesting...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:calacuccia
ID: 6374480
Why don't you try to use the Refresh method the second and later times, if the first time runs fine?

ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False 'or true

calacuccia

P.S. I have not found a reason for your type mismatch error.
Maybe try this

Destination:=ActiveSheet.Range(adoRS(2))

What is ExcelImportFile variable, is it a string or numberic?
   
0
 

Author Comment

by:BWATERS
ID: 6375301
The ExcelImportFile variable contains the path\filename.txt that is being imported.
Thanks for the tips; unfortunately I have to wait until I go to client site on Monday to try them.
Let's throw a few more points into the pot...this does look like an interesting question.
0
 

Author Comment

by:BWATERS
ID: 6385451
Sorry for the delay in returing to you...been busy...alas! no, your suggestion didn't make any difference.  Still works the first time, not after that.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6390526
Have you tried the refresh method?
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6393572
Could you try:
.......Destination:=Range(adoRS(2).value))
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6393577
Or,
....Destination:=Range(chr$(34) & cstr(adoRS(2).value) & chr$(34)))

Hope not missing any )
Cheers
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6393584
And do a check what is returned in adoRS(2) before setting detination, just for sure (debug.print maybe).
it appears to me that destination is specting a valid range and gets other thing (type mismatch).
0
 

Author Comment

by:BWATERS
ID: 6411406
Sigh...no good, people.  Yes I did check the variable and it does contains a "C13"
One thing I did notice: even though I 'xl.quit' and 'Set xl = Nothing', I noticed in NT Task Manager that the Excel PROCEES was still in effect (Process Tab).
This is getting hairy...another 50 in the pot.
I do appreciate the feedback though, thanks.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6411424
Then, in some place, excel is unloaded due to a object var still "trapping" that instance.
This appears to that not all instances were clearly "discharged".
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6411443
Try changing this:
Set xl = CreateObject("Excel.Application")
 
to:
On Error GoTo errTrap
Set xl = GetObject(, "Excel.Application")
xl.Visible = True
Exit Sub
errTrap:
If Err = 429 Then
    Err.Clear
    Set xl = CreateObject("Excel.Application")
    Resume Next
End If

 
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6411451
Just a thinking....

xl.Workbooks.Close
xl.Quit
Set xl = Nothing

You are not specify which workbook to close and without a statement like: xl.displayalerts=false the workbook is never closed since there is a message box asking you to save it or not.
0
 

Author Comment

by:BWATERS
ID: 6564114
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6967149
Hi BWATERS , what happens with this? any progress?
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7199893
Hi BWATERS,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Save as PAQ -- No Refund.
    *** no way to know if any answer was correct.

BWATERS, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 16

Expert Comment

by:twalgrave
ID: 7707794
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- PAQ/no refund
Please leave any comments here within the
next seven days.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 7758305
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question