Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 27th, 2011, 10:13 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Type Mismatch error

hi

could anyone help me by explaining why this loop is causing an "Type mismatch" error?

Code:
For lngMyCount = 1 To 3

ChDir varFolder(lngMyCount)
strF = Dir("Graphing_*_Actual_*_Year*.csv")
Do While strF <> ""
Set wbResults = Workbooks.Open(varFolder(lngMyCount) & "\" & strF)
        wbResults.Sheets(1).Range("A2:FF15").Copy
Thanks

Jeskit
 
Old April 29th, 2011, 12:32 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

Difficult to say Jeskit, I'm guessing it's highlighting the "Set wbResults=" line in the code you posted. Type Mismatch, i believe, usually occurs when you try to set a variable equal to something it's not meant to hold.

For example, if you declare a string variable, then try to assign a worksheet to it. Hence "mismatch"

I know this is part of the code you posted the other day, I'll see if i can find the code and spot where the issue is. :)
 
Old April 29th, 2011, 12:51 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

I did notice one thing. You've got an extra backslash once that whole folder path file name evaluates out:
Code:
'you've got trailing back slashes here:
strFldr = "C:\Documents and Settings\SeymourJ\My Documents\Tasks\": Lction = "C:\Documents and Settings\SeymourJ\Desktop\"
strFldr2 = "C:\Documents and Settings\SeymourJ\My Documents\Tasks2\": strFldr3 = "C:\Documents and Settings\SeymourJ\My Documents\Tasks3\"

'and these go into your variant array here:
varFolder = Array(strFldr, strFldr2, strFldr3)

'and here you give an extra backslash on the end of your strFlder, which is then accessed in what i'm guessing is the line that's throwing the error
Set wbResults = Workbooks.Open(varFolder(lngMyCount) & "\" & strF)

'take out that backslash, see if this makes it work:
Set wbResults = Workbooks.Open(varFolder(lngMyCount) & strF)
I rarely use the variant type of variable, but from what i know it's supposed to convert itself to whatever necessary. But you are using all variants in what is a string parameter. I know that VBA and excel do have bugs, and it just might be that you need to have a string in there, not a variant. If getting rid of the extra backslash doesn't cure it, try just putting a hardcoded string:
Code:
'try it with a hardcoded string:
Workbooks.Open("C:\Documents and Settings\SeymourJ\My Documents\Tasks\AND WHATEVER strF IS")

Last edited by mtranchi; April 29th, 2011 at 12:53 PM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
Error-0x800A000D-Type mismatch kishy449 Classic ASP Basics 0 July 23rd, 2007 04:48 AM
Type Mismatch error Sheraz Khan Classic ASP Basics 0 May 16th, 2007 08:12 AM
CreateObject Type mismatch error Deepmala Classic ASP Professional 1 February 7th, 2006 04:28 AM
Data Type mismatch error clueless_may Access VBA 1 May 5th, 2004 09:16 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.