?
Solved

Type Mismatch Error With xl.QueryTables.Add

Posted on 2001-08-10
19
Medium Priority
?
426 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
  • 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
Independent Software Vendors: 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month9 days, 22 hours left to enroll

569 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