|
problem with type mismatch error
The "'C:/" is incorrect. Should be "'C:\" No clue otherwise.
-- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "John Keith" wrote: 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 |
problem with type mismatch error
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..._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. John Keith wrote: 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 -- Dave Peterson |
problem with type mismatch error
On Sun, 12 Oct 2008 09:27:03 -0600, "Dave Patrick"
wrote: 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 |
problem with type mismatch error
On Sun, 12 Oct 2008 10:29:03 -0500, Dave Peterson
wrote: 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 |
problem with type mismatch error
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. "John Keith" wrote: On Sun, 12 Oct 2008 10:29:03 -0500, Dave Peterson wrote: 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 |
problem with type mismatch error
xl2003 and winXP home didn't mind the slash in place of the backslash.
John Keith wrote: On Sun, 12 Oct 2008 09:27:03 -0600, "Dave Patrick" wrote: 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 -- Dave Peterson |
problem with type mismatch error
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 John Keith wrote: <<snipped 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 -- Dave Peterson |
problem with type mismatch error
On Sun, 12 Oct 2008 12:31:00 -0700, JLGWhiz
wrote: 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 |
problem with type mismatch error
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. "John Keith" wrote: On Sun, 12 Oct 2008 12:31:00 -0700, JLGWhiz wrote: 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 |
problem with type mismatch error
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. -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... 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. "John Keith" wrote: On Sun, 12 Oct 2008 12:31:00 -0700, JLGWhiz wrote: 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 |
problem with type mismatch error
On Sun, 12 Oct 2008 15:48:28 -0500, Dave Peterson
wrote: 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 |
problem with type mismatch error
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. -- Rick (MVP - Excel) "John Keith" wrote in message ... On Sun, 12 Oct 2008 15:48:28 -0500, Dave Peterson wrote: 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 |
problem with type mismatch error
On Sun, 12 Oct 2008 22:47:35 -0400, "Rick Rothstein"
wrote: 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 |
problem with type mismatch error
On Sun, 12 Oct 2008 20:19:39 -0600, John Keith wrote:
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 |
problem with type mismatch error
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.) John Keith wrote: On Sun, 12 Oct 2008 15:48:28 -0500, Dave Peterson wrote: 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 -- Dave Peterson |
problem with type mismatch error
Oops. I didn't see Rick's response.
Ignore the first half of my reply. Dave Peterson wrote: 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.) John Keith wrote: On Sun, 12 Oct 2008 15:48:28 -0500, Dave Peterson wrote: 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 -- Dave Peterson -- Dave Peterson |
problem with type mismatch error
On Mon, 13 Oct 2008 06:59:31 -0500, Dave Peterson
wrote: 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 before you convert the range to values. Or even do an edit|replace in that range: 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 |
problem with type mismatch error
On Mon, 13 Oct 2008 06:59:31 -0500, Dave Peterson
wrote: Dave, 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. 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 |
problem with type mismatch error
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 ..... John Keith wrote: On Mon, 13 Oct 2008 06:59:31 -0500, Dave Peterson wrote: Dave, 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. 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 -- Dave Peterson |
problem with type mismatch error
On Wed, 15 Oct 2008 07:41:11 -0500, Dave Peterson
wrote: 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 |
| All times are GMT +1. The time now is 12:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com