This'll stump you! Problem with Copy Sheet between Excel versions

[tdw]

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

 

[Tom Ogilvy]

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

 

[Tushar Mehta]

This is speculation but you may be having problems because of the
AccessMode specification. It might be hard to debug remotely but does
the client have problems with the 2nd (or subsequent) attempt to create
the file? If so, it increases the likelihood of the AccessMode causing
the problem.

Can you ensure that the file is always under exclusive control before
you try and overwrite it? Or, as a test, can you create a new file
name each time? Does the problem still occur?

If the file is on a network drive there might be additional
security/sharing issues introduced into the analysis by the network
management software.

Finally, you may want to search support.microsoft.com for your error:
http://support.microsoft.com/search/default.aspx?query=%
22file+access+error%22

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[tdw]

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

 

[tdw]

Sorry, my signature wasn't appended to the previous post ...

Timothy White
Contract Programmer
Ontario, Canada

<my initials>hite<at>sympatico<dot><countryCode>