 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / October 2008problem with type mismatch error
Running the following code produces a type mismatch error when the line with the ExecuteExcel4Macro is reached. But if the code is re-run again with no changes the error does not occur. I've done some web searching and found some suggestions that I've tried (Dim arg as Variant and double quotes for example) but nothing has eliminated the error yet. If all else fails I'll do an on error resume next but first I'd like to see if the problem can be corrected. Any ideas? Thanks Dim arg As Variant Application.ScreenUpdating = False Max_Row = 500 Path = "'C:/" file = "[Analysis.xls]" Sheet = "Pn Summary'!" string = Path & file & Sheet source_c = 1 dest_c = 3 For r = 1 To Max_Row a = Cells(r + 4, source_c).Address arg = string & Range(a).Range("A1").Address(, , xlR1C1) Cells(r, dest_c) = UCase(ExecuteExcel4Macro(arg)) If Cells(r, dest_c) = 0 Then Cells(r, dest_c).ClearContents Next r John Keith kd0gd@juno.com The "'C:/" is incorrect. Should be "'C:\" No clue otherwise.  SignatureRegards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect > Running the following code produces a type mismatch error when the > line with the ExecuteExcel4Macro is reached. But if the code is re-run [quoted text clipped - 30 lines] > John Keith > kd0gd@juno.com >The "'C:/" is incorrect. Should be "'C:\" No clue otherwise. Sorrt, I actually edited my code before posting so as not to publish the lengthy path which was actually to an internet location which didn't need to be broadcast to the group. But thanks for looking. John Keith kd0gd@juno.com xl2003 and winXP home didn't mind the slash in place of the backslash. > >The "'C:/" is incorrect. Should be "'C:\" No clue otherwise. > [quoted text clipped - 6 lines] > John Keith > kd0gd@juno.com SignatureDave Peterson Your code worked ok for me. It was based on the GetValue function from John Walkenbach's site: http://j-walk.com/ss/excel/eee/eee009.txt or http://spreadsheetpage.com/index.php/eee/issue_no_9_july_15_1999/ I'm not sure if your code is a skinnied down example of what you're doing, but if you're just plopping the value into a cell, it may be easier to find the problem if you just put a formula to return the value directly into your cell: For r = 1 To Max_Row a = Cells(r + 4, Source_C).Address arg = myString & Range(a).Range("A1").Address(, , xlR1C1) With Cells(r, Dest_C) .numberformat = "General" '???? just not text! .Formula = "=" & arg '.Value = .Value 'If .Value = 0 Then ' .ClearContents 'End If End With Next r When you're done debugging, you can remove the commented lines. > Running the following code produces a type mismatch error when the > line with the ExecuteExcel4Macro is reached. But if the code is re-run [quoted text clipped - 30 lines] > John Keith > kd0gd@juno.com SignatureDave Peterson Dave, >Your code worked ok for me. My code works fine the second time it is run after I get the erro rmessage :-( >It was based on the GetValue function from John Walkenbach's site: >http://j-walk.com/ss/excel/eee/eee009.txt You're very astute! The url looks different from what I remember but yes, I did pick up the tip from John W. >I'm not sure if your code is a skinnied down example of what you're doing, The code is the same as what I am running with the exception of changing the path, file and string variables for a little privacy as I noted in the reply to the other post in this string. >if you're just plopping the value into a cell Yes, that is all I am doing. > it may be easier to find the >problem if you just put a formula to return the value directly into your cell: I'll play with your suggestion. If it works why wouldn't I just continue to use this process? Thanks! John Keith kd0gd@juno.com It could be balking at the variable "string" since that is a reserved word. Try changing it to "strg" or "myPath" or anything not reserved for VBA constants or data types nad see if it still hiccups on the first loop. > Dave, > [quoted text clipped - 28 lines] > John Keith > kd0gd@juno.com >It could be balking at the variable "string" since that is a reserved word. >Try changing it to "strg" or "myPath" or anything not reserved for VBA >constants or data types nad see if it still hiccups on the first loop. My bad. My original code had very long variable names that I shortened before posting. I changed "blah_blah_blah_string" to just "string". I'm still experimenting but without success so far. I'm still perplexed by the observation that the code will fail when I first open the workbook and call the macro, and then when called again immediately following the failure it will pass. ( I had been doing all my development with the workbook open continuously and I didn't see this problem until I closed and re-opened the workbook.) John Keith kd0gd@juno.com that is what I was suggesting, that the word "string" might be the cause of the message popping up. String is a reserved word for data type. Sometimes when reserved words are used and are not capitalized, the compiler sees it as a mistake and sends what it believes to be the appropriate error message. Other times it picks it up as the variable that the writer intended it to be and runs without the message. I don't know that it is happening in this case, but I don't see anything else that would cause the problem. > >It could be balking at the variable "string" since that is a reserved word. > >Try changing it to "strg" or "myPath" or anything not reserved for VBA [quoted text clipped - 16 lines] > John Keith > kd0gd@juno.com String is also the name of a built in function in VB, so it should doubly be avoided as a variable name. By way of information, the String function returns repeated characters; for example String(10,"@") will return 10 "at" symbols. SignatureRick (MVP - Excel) > that is what I was suggesting, that the word "string" might be the cause > of [quoted text clipped - 29 lines] >> John Keith >> kd0gd@juno.com I would continue to just plop the value into cell. I'd drop the old xlm code completely. Ps. I did change some of the variables (like JLGWhiz suggested). I should have shared the modified code: Option Explicit Sub testme() Dim arg As Variant Dim myString Dim Max_Row As Long Dim Source_C As Long Dim Dest_C As Long Dim myPath As String Dim mySheet As String Dim myFile As String Dim r As Long Dim a As String Dim res As Variant Application.ScreenUpdating = False Max_Row = 500 myPath = "'C:/" myFile = "[Analysis.xls]" mySheet = "Pn Summary'!" myString = myPath & myFile & mySheet 'myString = "'C:\my documents\excel\[book1.xls]sheet1'!" Source_C = 1 Dest_C = 3 For r = 1 To Max_Row a = Cells(r + 4, Source_C).Address arg = myString & Range(a).Range("A1").Address(, , xlR1C1) With Cells(r, Dest_C) .Formula = "=" & arg '.Value = .Value 'If .Value = 0 Then ' .ClearContents 'End If End With Next r Application.ScreenUpdating = True End Sub <<snipped>> > >if you're just plopping the value into a cell > [quoted text clipped - 9 lines] > John Keith > kd0gd@juno.com SignatureDave Peterson >I would continue to just plop the value into cell. I'd drop the old xlm code >completely. > >Ps. I did change some of the variables (like JLGWhiz suggested). > >I should have shared the modified code: Dave, Thank you for the follow up. I've learned a couple new things which I'm always thankful for. I have tried your code and have a few comments and questions. I did rip out all the unnecessary code (like clearing the cell contents if 0) just to simplify the isolation of the cause of any error, and I shortened the loop to speed up the process of getting results, and finally I eliminated some variables and put explicit values in, again just to simplify the code down to bare minimum. The Option Explicit, this appears to require the need for every variable to be declared in a Dim statement? What was the usefulness of this? Your code inserts the formula to reference the external workbook. And I think I understand that .value = .value will replace the formula with the value of the cell. But here's the interesting result. When I run this code I get #REF in each cell. Each time I subsequently run the macro the right contents are inserted into the cell. Is there some command needed to cause the formula inserted into the cell to be evaluated? BTW, the results were the same with the source file located at its url over the internet or with a copy of the file located in My Documents on my local drive. Wanting simplify a little more I made a small spreadsheet called test with data in a1 through A10 and put it in My Documents. Then I ran the following code (which is identical to the simplifid code I used above but now references this simple spreadsheet) and I get a different error. The error is 1004, application or obect defined error on the .formula statement. I'm baffled by what is going on. Here's the code for the last experiement I described. Option Explicit Sub Build_Report() Dim arg As Variant Dim myString As String Dim myPath As String Dim mySheet As String Dim myFile As String Dim r, i As Long Dim a, Answer As String Dim res As Variant Dim test_string, both As String myPath = "C:\Documents and Settings\keithjo\My Documents\" myFile = "[test.xls]" mySheet = "Sheet1'!" myString = myPath & myFile & mySheet MsgBox myString For r = 1 To 10 a = Cells(r, 1).Address arg = myString & Range(a).Range("A1").Address(, , xlR1C1) With Cells(r, 1) .Formula = "=" & arg .Value = .Value End With Next r End Sub John Keith kd0gd@juno.com A few comments... > The Option Explicit, this appears to require the need for > every variable to be declared in a Dim statement? What > was the usefulness of this? One major benefit is it helps you to spot typing errors in your variable names. For example, if you declared this... Dim AdjustmentFactor As Double and then later on mistyped it in an assignment operation like this... AdjutsmentFactor = 12.34 then VB would flag your use of AdjutsmentFactor as an error helping you to spot the mistyping. Without Option Explicit, the variable would default to zero... if you never noticed the typing error, then (depending on how the variable is used) there is a strong possibility that your code would generate incorrect results and you might never know it. > Dim r, i As Long > Dim a, Answer As String The above two lines are not doing what you think. Only i and Answer are declared like you expect... r and a are both declared as Variants. In VB, you have to individually declare the type of a variable. So, either this... Dim r As Long, i As Long Dim a As String, Answer As String or this... Dim r As Long Dim i As Long Dim a As String Dim Answer As String > myPath = "C:\Documents and Settings\keithjo\My Documents\" > myFile = "[test.xls]" > mySheet = "Sheet1'!" See the apostrophe after Sheet1 in mySheet... it needs a companion in front of the C: in myPath. SignatureRick (MVP - Excel) >>I would continue to just plop the value into cell. I'd drop the old xlm >>code [quoted text clipped - 72 lines] > John Keith > kd0gd@juno.com Rick, >A few comments... Thank you for the tutorial! >> myPath = "C:\Documents and Settings\keithjo\My Documents\" >See the apostrophe after Sheet1 in mySheet... it needs a companion in front >of the C: in myPath. Hey eagle eyes, right on! That solved the problem with that test case. Now back to the others to see if I can find what wrong with them. Thank you Rick. John Keith kd0gd@juno.com >But here's the interesting result. When I run this code I get #REF in >each cell. Each time I subsequently run the macro the right contents >are inserted into the cell. Is there some command needed to cause the >formula inserted into the cell to be evaluated? BTW, the results were >the same with the source file located at its url over the internet or >with a copy of the file located in My Documents on my local drive. In my playing with this issue I just learned anothe interesting piece of info. Let me first reiterate the behavior I tried to describe above: 1) open file 2) run macro - cells are filled with #REF 3) change nothing, run macro again, get correct data in cells (This behavior is the same if the file is on my local disk or accessed across the network.) The new peice of data is: I put a breakpoint at the end of the loop (next r statement) and here is what comes out: 1) open file 2) run macro 3) stops at first pass through loop, first cell filled with #REF 4) hit continue - next cell filled with correct data 5) hit continue - all remaining cells filled with correct data (Again, this behavior is the same if the file is on my local disk or accessed across the network.) John Keith kd0gd@juno.com First, "Option Explicit" does require you to declare your variables. So if you make a typing mistake and mistype one of the variable names, then your code won't even compile. If I were you I'd always use it. Second, when you do this: Dim r, i As Long Dim a, Answer As String Dim test_string, both As String You are actually doing this: Dim r as variant, i As Long Dim a as variant, Answer As String Dim res As Variant Dim test_string as variant, both As String I bet that's not what you meant. Third, you dropped the leading apostrophe in this statement: myPath = "C:\Documents and Settings\keithjo\My Documents\" should be: myPath = "'C:\Documents and Settings\keithjo\My Documents\" Fourth (and it's just a guess since it doesn't explain the problem with the local "sending" file)--maybe there's a network delay. Maybe you could add: application.calculate '.calculatefull 'depending on your version before you convert the range to values. Or even do an edit|replace in that range: change = to = to see if excel will recalc. If that doesn't work, I think I'd open the "sending" file and just copy|paste values--or build a formula to the cells in the open file. (Depending on how many of those formulas you're building, this may even work more quickly.) > >I would continue to just plop the value into cell. I'd drop the old xlm code > >completely. [quoted text clipped - 71 lines] > John Keith > kd0gd@juno.com SignatureDave Peterson Oops. I didn't see Rick's response. Ignore the first half of my reply. > First, "Option Explicit" does require you to declare your variables. So if you > make a typing mistake and mistype one of the variable names, then your code [quoted text clipped - 113 lines] > > Dave Peterson SignatureDave Peterson Dave, > So if you make a typing mistake and mistype one of the variable names, > then your code won't even compile. If I were you I'd always use it. With my poor typing skills a good suggestion. >Fourth (and it's just a guess since it doesn't explain the problem with the >local "sending" file)--maybe there's a network delay. I've wondered about a network issue as well, but the problem remaining with the local file is confusing. I'm going to concentrate on the local file and see if I canfix it then try the network file. BTW, my simplified code to experiment with has a message box asking if I want local or network source and I select the path varialbe accordingly, all the rest of the code is identical (and yes, I did check the apostrophe!) It's almost like the first time through the loop excel catches the error and then says, oh, I knlw what you mean and works OK after that. Another piece of data for anyone's consideration: The original code actualy reads data from two different network files (for comparison using later code, which all works great at this point!) usig the same process to download the data, just different path, etc. The behavior of the orginal code is: 1) open file 2) run macro 3) get error on first attempt to download from first file 4) run macro again, successfully download data from first file but get error message on second file 5) run macro again and all data is downloaded and processing of info proceeds successfully 6) at this point edits/changes can be made to the code and rerunning the macro continues to work perfectly >Maybe you could add: >application.calculate '.calculatefull 'depending on your version [quoted text clipped - 3 lines] >change = to = >to see if excel will recalc. I'll try these tonight (bummer I have to go to work soon). I've also thought about trapping on the error and then entering the loop but I haven't succeeded with that process yet. >If that doesn't work, I think I'd open the "sending" file and just copy|paste >values--or build a formula to the cells in the open file. (Depending on how >many of those formulas you're building, this may even work more quickly.) My very first version of a workbook to compare the data between the two network files did have a formula in every necessary cell to read the data and then a macro was started to do the analysis. But the opportunity to all the work in a macro was very attractive to me for a lot of reasons (not the least of which is this is a huge learning opportunity). Thank you for continuing to follow up with me! John Keith kd0gd@juno.com Dave, >Maybe you could add: >application.calculate '.calculatefull 'depending on your version [quoted text clipped - 3 lines] >change = to = >to see if excel will recalc. All good suggestions but no success. I removed the .value = .value statement and put it in a second loop to be executed after putting the formulas in the cells with no success, even with adding a pause between the execution of the two loops. I'm suspicious there is some timing issue here but I don't see a way to work around it yet. >If that doesn't work, I think I'd open the "sending" file and just copy|paste >values--or build a formula to the cells in the open file. (Depending on how >many of those formulas you're building, this may even work more quickly.) I'm leaning this way now, but there are a couple of questions since this will put me in some uncharted territory. 1) Is there a way to open a file with only the target sheet there? (There are a LOT of sheets in the workbook) 2) Lacking #1 is there an easy way to delete all but the target sheet? (Something like select the target sheet, toggle the selection then delete?) 3) I am actually loading data from two different files, I can open the first file, extract all the data I need, but then what happens when I open the second file, what do I need to learn about having two files open, or is that even possible? John Keith kd0gd@juno.com I don't have any more suggestions for the formula evaluation problem. But... #1. Nope. You open the entire workbook. You open it in readonly mode and delete all the other worksheets, but I'm not sure what that would save. #2. Nope. Make sure that the target sheet is visible (at least one sheet has to be visible), then loop through the worksheets and delete them if the name doesn't match. Dim wks as worksheet dim myName as string myName = "Target" set wks = nothing on error resume next set wks = otherworkbook.worksheets(myname) on error goto 0 if wks is nothing then msgbox "no sheet named target" exit sub end if wks.visible = xlsheetvisible for each wks in otherworkbook.worksheets if lcase(wks.name) = lcase(myname) then 'skip it else application.displayalerts = false wks.delete application.displayalerts = true end if next wks ======== An alternative would be to just copy (temporarily) the target sheet into a new workbook and close the otherworkbook. otherworkbook.worksheets(myname).copy 'to a new workbook otherworkbook.close savechanges:=false (or copy it to your current workbook (and delete it later???) #3. Use variables to represent both workbooks. Dim Wkbk1Name as string dim Wkbk2Name as string dim Wkbk1 as workbook dim wkbk2 as workbook wkbk1name = "C:\somepath\somename.xls" wkbk2name = "c:\someotherpath\someothername.xls" 'the names must be different (disregarding the drive/path) set wkbk1 = workbooks.open(filename:=wkbk1name, readonly:=true) set wkbk2 = workbooks.open(filename:=wkbk2name, readonly:=true) for each wks in wkbk1.wks .... > Dave, > [quoted text clipped - 35 lines] > John Keith > kd0gd@juno.com SignatureDave Peterson >I don't have any more suggestions for the formula evaluation problem. I'm also at the end of my ideas :-( Thank you for the other tips on dealing with mutliple worksheets/workbooks. I'll work on this in a few days after I get my routine, required tasks done and then get back to the fun stuff. John Keith kd0gd@juno.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.