Solved

Type Mismatch Error With xl.QueryTables.Add

Posted on 2001-08-10
19
367 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now