Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

Type Mismatch Error With xl.QueryTables.Add


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
BWATERS
Asked:
BWATERS
  • 8
  • 5
  • 3
  • +3
1 Solution
 
calacucciaCommented:
Hi BWaters,

What is the content/value of adoRS(2) ?
0
 
BWATERSAuthor Commented:
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
 
Richie_SimonettiIT OperationsCommented:
Interesting...
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
calacucciaCommented:
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
 
BWATERSAuthor Commented:
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
 
BWATERSAuthor Commented:
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
 
calacucciaCommented:
Have you tried the refresh method?
0
 
Richie_SimonettiIT OperationsCommented:
Could you try:
.......Destination:=Range(adoRS(2).value))
0
 
Richie_SimonettiIT OperationsCommented:
Or,
....Destination:=Range(chr$(34) & cstr(adoRS(2).value) & chr$(34)))

Hope not missing any )
Cheers
0
 
Richie_SimonettiIT OperationsCommented:
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
 
BWATERSAuthor Commented:
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
 
Richie_SimonettiIT OperationsCommented:
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
 
Richie_SimonettiIT OperationsCommented:
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
 
Richie_SimonettiIT OperationsCommented:
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
 
BWATERSAuthor Commented:
0
 
Richie_SimonettiIT OperationsCommented:
Hi BWATERS , what happens with this? any progress?
0
 
DanRollinsCommented:
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
 
twalgraveCommented:
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
 
moduloCommented:
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 8
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now