I'm not a VB programmer, so I don't know what to do when an error message has a button to debug. So, selected debug in the error window. The VB debug window came up with the following highlighted:
Don't know what the problem is, but there was some error handling missing from that block that once added will give me more info on what the problem is. This will be added in the next version.
Meantime, if you can identify the conditions that the error occurs, that would be great. For example, you state a reboot was required, so did this cause the Workbooks to need to be repaired? Is the repair window open awaiting confirmation? This might cause Excel to open temporary/virtual files. Do you only see the error after a reboot and not at any other time?
Thanks, Steve
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I get the same error.
Private Sub StartTiling()
If Application.Windows.Count > 1 And Not bDisableTiling Then
'Tile windows
TileWindows optAlignVertical, Workbooks.Item(GetFilename(cmbFileOne.Value)).Name, Workbooks.Item(GetFilename(cmbFileTwo.Value)).Name, False
End If
End Sub
I have tried using two different worksheets, and the program stops here. I think the array counter isn't incrementing because when I mouse over cmbFileOne the worksheet name is visible but there is no value for cmbFileTwo.
What can I do?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I find no way to end up with one combobox with a Workbook name and the other blank. How did you manage that? Can you open two Workbooks and see if the problem exists? Do you for example have a personal workbook open with only one other Workbook in which you are trying to compare two sheets? I could see how something like this might be a problem.
Any extra info or screenshots would help me track down what is happening.
Thanks, Steve
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Have been seeing this same error whether I have one or two workbooks open. The workbooks are different versions of the same file, not trying to compare two sheets within a file. Things worked until an update to Excel was forced on me by corporate types... :-( Not a new version, just an update.
Will gather particulars and send them along.
Would have done sooner but corporate security managed to lock us out of sourceforge for a time. (sigh)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I may have found the problem. As with John, there may have been an 'unscheduled' reboot involved.
I checked the settings.ini file and saw old compare references that were no longer in those directories. (attached Spreadsheet Compare Settings.old.ini)
I backed it up and emptied it and did a compare of a couple of test xlsx files and it worked.
The new settings file is also attached, along with the two test spreadsheets.
Something confused the settings so that it couldn't find either file and it didn't like it very well. Will swap the ini files and reproduce and capture some debug output.
Steve, I installed v1.34.4 and I get the same error message Run-time error '9': Subscript out of range.
I did uninstall the previous version prior to upgrading.
The files have the same worksheets. I want to compare all the worksheets in each file. I get the message when I select Full Compare. What program files can I send you to help troubleshooting?
You are running Excel 2013 which I don't have and have not played with. I wonder if that makes a difference. The screenshot you sent shows two instances of Excel side by side rather than two workbooks open in one instance. Don't know if that is a change to Excel in 2013. You would not be able to compare workbooks between instances in 2010 or earlier. Workbooks to be compared must be in the same Excel instance.
Try this. Open one instance of Excel and create two new workbooks (File>New), make some differences and compare. Let me know what happens.
Thanks, Steve
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Try this. Open one instance of Excel and create two new workbooks (File>New), make some differences and compare. Let me know what happens.
Steve, I did as you described and there was no change. The Full Compare gets the subscript error and the Quick Compare works. I think the Quick Compare works OK. I hav en't really studied it in depth.
My opinion is that you should support Excel 2010 and 2013 as 2010 has been in use for a long time and 2013 is becoming more popular.
I am also having a similar problem with the latest version 1.34.4: run-time error 9 when trying to run the Full Compare (Quick Compare works fine). I have Office 2013 with the exception of Excel which is version 2007 for compatibility with another add-in. Debug of 1.34.4 also brought up the StartTiling subroutine. The workbooks are in a single session/instance of Excel. I reverted back to 1.34.3 and everything works fine as before. To the best of my knowledge, Excel2007 hasn't been updated recently. Is there anything I can provide to help debug this issue?
Thank you.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Found another issue resolved in v1.34.5. If a session is saved with filenames and the files exist, but are not open, then you would get that error. I think that is the problem you are having. Check for a new download shortly.
Steve
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have found another cause for this problem.
In frmMain function SessionLoad an attempt is made to initialize cmbFileOne and cmbFileTwo with data stored in CurrentSessionSettings.
But when CurrentSessionSettings.sFirstWorkbook or CurrentSessionSettings.sSecondWorkbook are empty, then the first file from MyDocuments gets inserted:
Relevant Code:
sTemp = Dir(CurrentSessionSettings.sFirstWorkbook)
If sTemp <> "" Then
cmbFileOne.Clear
cmbFileOne.AddItem sTemp
cmbFileOne.ListIndex = 0
End If
When CurrentSessionSettings.sFirstWorkbook is empty ("") then Dir("") gets called, which returns the first file the users My Documents directory. This is obviously not what we want.
Suggested Fix (repeat for second combobox):
If CurrentSessionSettings.sFirstWorkbook <> "" Then
sTemp = Dir(CurrentSessionSettings.sFirstWorkbook)
cmbFileOne.Clear
cmbFileOne.AddItem sTemp
cmbFileOne.ListIndex = 0
End If
HTH
Last edit: gcalis 2015-02-03
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Just reporting that i was getting this error and it was because of a messed up settings.ini file. I used the file Patrick provided and put it in C:\Users\me\AppData\Roaming\Spreadsheet Compare\ and now it works! Thanks Patrick.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm not a VB programmer, so I don't know what to do when an error message has a button to debug. So, selected debug in the error window. The VB debug window came up with the following highlighted:
TileWindows optAlignVertical, Workbooks.Item(GetFilename(cmbFileOne.Value)).Name, Workbooks.Item(GetFilename(cmbFileTwo.Value)).Name, False
I've attached a screen shot fron the VB debugger.
What should I do next?
Last edit: John Schuster 2014-07-08
Don't know what the problem is, but there was some error handling missing from that block that once added will give me more info on what the problem is. This will be added in the next version.
Meantime, if you can identify the conditions that the error occurs, that would be great. For example, you state a reboot was required, so did this cause the Workbooks to need to be repaired? Is the repair window open awaiting confirmation? This might cause Excel to open temporary/virtual files. Do you only see the error after a reboot and not at any other time?
Thanks, Steve
I get the same error.
Private Sub StartTiling()
If Application.Windows.Count > 1 And Not bDisableTiling Then
'Tile windows
TileWindows optAlignVertical, Workbooks.Item(GetFilename(cmbFileOne.Value)).Name, Workbooks.Item(GetFilename(cmbFileTwo.Value)).Name, False
End If
End Sub
I have tried using two different worksheets, and the program stops here. I think the array counter isn't incrementing because when I mouse over cmbFileOne the worksheet name is visible but there is no value for cmbFileTwo.
What can I do?
I find no way to end up with one combobox with a Workbook name and the other blank. How did you manage that? Can you open two Workbooks and see if the problem exists? Do you for example have a personal workbook open with only one other Workbook in which you are trying to compare two sheets? I could see how something like this might be a problem.
Any extra info or screenshots would help me track down what is happening.
Thanks, Steve
Have been seeing this same error whether I have one or two workbooks open. The workbooks are different versions of the same file, not trying to compare two sheets within a file. Things worked until an update to Excel was forced on me by corporate types... :-( Not a new version, just an update.
Will gather particulars and send them along.
Would have done sooner but corporate security managed to lock us out of sourceforge for a time. (sigh)
I may have found the problem. As with John, there may have been an 'unscheduled' reboot involved.
I checked the settings.ini file and saw old compare references that were no longer in those directories. (attached Spreadsheet Compare Settings.old.ini)
I backed it up and emptied it and did a compare of a couple of test xlsx files and it worked.
The new settings file is also attached, along with the two test spreadsheets.
Something confused the settings so that it couldn't find either file and it didn't like it very well. Will swap the ini files and reproduce and capture some debug output.
Last edit: Patrick 2014-12-02
Screenshots of steps to reproduce and of debugger showing SC is looking for wrong files.
view in time stamp order
Think I have got it. If the session file name does not exist, it is not included in the dropdown. Try v1.34.4.
Thanks, Steve
Steve, I installed v1.34.4 and I get the same error message Run-time error '9': Subscript out of range.
I did uninstall the previous version prior to upgrading.
The files have the same worksheets. I want to compare all the worksheets in each file. I get the message when I select Full Compare. What program files can I send you to help troubleshooting?
James Hill
[cid:image003.jpg@01D01305.62FDD170]
Last edit: SteveT 2014-12-09
You are running Excel 2013 which I don't have and have not played with. I wonder if that makes a difference. The screenshot you sent shows two instances of Excel side by side rather than two workbooks open in one instance. Don't know if that is a change to Excel in 2013. You would not be able to compare workbooks between instances in 2010 or earlier. Workbooks to be compared must be in the same Excel instance.
Try this. Open one instance of Excel and create two new workbooks (File>New), make some differences and compare. Let me know what happens.
Thanks, Steve
Try this. Open one instance of Excel and create two new workbooks (File>New), make some differences and compare. Let me know what happens.
Steve, I did as you described and there was no change. The Full Compare gets the subscript error and the Quick Compare works. I think the Quick Compare works OK. I hav en't really studied it in depth.
My opinion is that you should support Excel 2010 and 2013 as 2010 has been in use for a long time and 2013 is becoming more popular.
James Hill
Last edit: SteveT 2014-12-09
I am also having a similar problem with the latest version 1.34.4: run-time error 9 when trying to run the Full Compare (Quick Compare works fine). I have Office 2013 with the exception of Excel which is version 2007 for compatibility with another add-in. Debug of 1.34.4 also brought up the StartTiling subroutine. The workbooks are in a single session/instance of Excel. I reverted back to 1.34.3 and everything works fine as before. To the best of my knowledge, Excel2007 hasn't been updated recently. Is there anything I can provide to help debug this issue?
Thank you.
Found another issue resolved in v1.34.5. If a session is saved with filenames and the files exist, but are not open, then you would get that error. I think that is the problem you are having. Check for a new download shortly.
Steve
Steve,
V!.34.5 ran Full Compare ran to completion. I will try a few more
Thanks
James Hill
Last edit: SteveT 2014-12-09
I downloaded 1.34.5 and it works perfectly! Thank you.
I have found another cause for this problem.
In frmMain function SessionLoad an attempt is made to initialize cmbFileOne and cmbFileTwo with data stored in CurrentSessionSettings.
But when CurrentSessionSettings.sFirstWorkbook or CurrentSessionSettings.sSecondWorkbook are empty, then the first file from MyDocuments gets inserted:
Relevant Code:
sTemp = Dir(CurrentSessionSettings.sFirstWorkbook)
If sTemp <> "" Then
cmbFileOne.Clear
cmbFileOne.AddItem sTemp
cmbFileOne.ListIndex = 0
End If
When CurrentSessionSettings.sFirstWorkbook is empty ("") then Dir("") gets called, which returns the first file the users My Documents directory. This is obviously not what we want.
Suggested Fix (repeat for second combobox):
If CurrentSessionSettings.sFirstWorkbook <> "" Then
sTemp = Dir(CurrentSessionSettings.sFirstWorkbook)
cmbFileOne.Clear
cmbFileOne.AddItem sTemp
cmbFileOne.ListIndex = 0
End If
HTH
Last edit: gcalis 2015-02-03
Thanks. Will include in the next release. The check of sTemp <> "" is still required to eliminate bad file names from being added to the list.
Just reporting that i was getting this error and it was because of a messed up settings.ini file. I used the file Patrick provided and put it in C:\Users\me\AppData\Roaming\Spreadsheet Compare\ and now it works! Thanks Patrick.
@John,
Glad it worked for you!