Solved

Type Mismatch Error With xl.QueryTables.Add

Posted on 2001-08-10
19
371 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

809 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