Type Mismatch error

jeskit · April 27th, 2011, 10:13 AM

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

mtranchi · April 29th, 2011, 12:32 PM

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. :)

mtranchi · April 29th, 2011, 12:51 PM

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")