Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
This'll stump you! Problem with Copy Sheet between Excel versions
I wrote a simple macro (code listed below) to individually copy a subset of
sheets from a workbook to create new workbooks. The orginal workbook has 13 worksheets and the user wants to copy out 8 worksheets to separate workbooks to distribute to others. I test it on my machine running Excel 2003 SP1 and everything works fine. I test it on another machine running Excel 2000 SP3 and everything works fine. My wife tests it at work running Excel 2000 and everything works fine. A colleague runs it on his machine and everything works fine. My client, running Excel 2002 SP3, runs the macro and it fails. She can't even manually copy a sheet in the workbook I've sent her! She works in any other workbook and she can manually copy a sheet. The VBA command I use to copy a worksheet to a new workbook, the line on which the macro fails, is simply "Sheets(shtName).Copy". The error is "Path/File access error (Error 75)." So, I'm thinking there's some issue between versions. I save my workbook in Excel 2003 using "Microsft Excel 97- Excel 2003 & 5.0/95 Workbook" file type. Open it on the machine running 2000-SP3 and do a Save As using "Microsoft Excel Workbook" thinking that it'll now be saved as a 2000 workbook. Send it to the client and IT WORKS! ... But Wait ... there are changes ... I make the changes, go through the same silly Save As process listed above, send it back to the client and ... IT DOESN'T WORK!! What's with that? This is just plain rude! If you've read this far, I Thank You for sticking with me. If you have any suggestions as to what the issue may be or how I might solve the problem I would be sincerely grateful! Thanks in advance for any help that may be provided! tdw Private Sub CreateWorkbook(shtName As String, flName As String, asAtDt As String) Application.StatusBar = "Creating the " + flName + " workbook" Sheets(shtName).Copy 'the following formatting replaces existing formulae with values so that the 'user is not continually prompted to update the formulae when they open 'the workbook ActiveSheet.Range("H1").Value = "'" + asAtDt Cells.Find(What:="Plus Already Approved This Fiscal Year", LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False).Cells(1, 2).Activate Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Cells.Find(What:="Target For This Fiscal Year (High End of Range)", LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False).Cells(1, 2).Activate Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues ActiveSheet.Range("A1").Activate With ActiveWorkbook .KeepChangeHistory = True .SaveAs Filename:=flName, AccessMode:=xlShared .Close End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
This'll stump you! Problem with Copy Sheet between Excel versions
I would try not sharing the workbook. Based on your scenario, I don't see
any reason it would need to be shared. With ActiveWorkbook .KeepChangeHistory = True .SaveAs Filename:=flName, AccessMode:=xlShared .Close End With would need to be changed to not share the workbook -- Regards, Tom Ogilvy "tdw" wrote in message ... I wrote a simple macro (code listed below) to individually copy a subset of sheets from a workbook to create new workbooks. The orginal workbook has 13 worksheets and the user wants to copy out 8 worksheets to separate workbooks to distribute to others. I test it on my machine running Excel 2003 SP1 and everything works fine. I test it on another machine running Excel 2000 SP3 and everything works fine. My wife tests it at work running Excel 2000 and everything works fine. A colleague runs it on his machine and everything works fine. My client, running Excel 2002 SP3, runs the macro and it fails. She can't even manually copy a sheet in the workbook I've sent her! She works in any other workbook and she can manually copy a sheet. The VBA command I use to copy a worksheet to a new workbook, the line on which the macro fails, is simply "Sheets(shtName).Copy". The error is "Path/File access error (Error 75)." So, I'm thinking there's some issue between versions. I save my workbook in Excel 2003 using "Microsft Excel 97- Excel 2003 & 5.0/95 Workbook" file type. Open it on the machine running 2000-SP3 and do a Save As using "Microsoft Excel Workbook" thinking that it'll now be saved as a 2000 workbook. Send it to the client and IT WORKS! ... But Wait ... there are changes ... I make the changes, go through the same silly Save As process listed above, send it back to the client and ... IT DOESN'T WORK!! What's with that? This is just plain rude! If you've read this far, I Thank You for sticking with me. If you have any suggestions as to what the issue may be or how I might solve the problem I would be sincerely grateful! Thanks in advance for any help that may be provided! tdw Private Sub CreateWorkbook(shtName As String, flName As String, asAtDt As String) Application.StatusBar = "Creating the " + flName + " workbook" Sheets(shtName).Copy 'the following formatting replaces existing formulae with values so that the 'user is not continually prompted to update the formulae when they open 'the workbook ActiveSheet.Range("H1").Value = "'" + asAtDt Cells.Find(What:="Plus Already Approved This Fiscal Year", LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False).Cells(1, 2).Activate Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Cells.Find(What:="Target For This Fiscal Year (High End of Range)", LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False).Cells(1, 2).Activate Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues ActiveSheet.Range("A1").Activate With ActiveWorkbook .KeepChangeHistory = True .SaveAs Filename:=flName, AccessMode:=xlShared .Close End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
This'll stump you! Problem with Copy Sheet between Excel versions
Tom & Tushar, Thank You for your contributions!
Unfortunately, the problem has nothing to do with the workbook that is created being shared. First, the macro bombs before getting to the command that specifies the access mode. Second, the access mode on the newly created workbook has to be shared in order to track changes. Third, the macro is only accessible by one individual that has the 'master' workbook, the one with all the worksheets, sitting on her computer. The idea is that the macro will copy a subset of the sheets from the master workbook, creating individual workbooks for each sheet in the subset. The problem is that it bombs 'intermittently' on the first copy sheet command. After much hair-pulling and head-scratching we were able to determine that the user running the macro has restricted rights that caused the error. In all cases and versions of Excel where it worked for me I was running it in an environment that was either stand-alone or unrestricted access to network resources. We were able to solve the problem on the client's computer by ensuring the workbook was being saved and opened from their local hard drive (not a network folder) and that the Tools Options General Default file location also pointed to a folder on the local hard drive. Again, Thank You for your input! If you would like more details please feel free to e-mail me. Sincerely, tdw |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
This'll stump you! Problem with Copy Sheet between Excel versions
Sorry, my signature wasn't appended to the previous post ...
Timothy White Contract Programmer Ontario, Canada <my initialshite<atsympatico<dot<countryCode |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save multiple versions of a document/excel sheet | Excel Discussion (Misc queries) | |||
This'll be one for the wizards | Excel Worksheet Functions | |||
Did I stump everyone? Counter that ignores hidden rows | Excel Discussion (Misc queries) | |||
diferrent Excel versions connection problem | Excel Programming | |||
Copy Sheet Problem | Excel Programming |