copy from closed workbook again! ;(

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code which works perfectly on my office computer. I
sent it to the co-worker I made it for who is attached to the LAN and it
crashes on the line:

ChDrive MyPath

Does anyone have any idea what the problem is or how I can fix it. We
commented out that line and it crashed on the next on and we commented out
that line and it crashed on the next one.


Sub GetDataFromClosedWB()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

If FName = False Then
'do nothing
Else

'Get Fridays date and Office city/state/zip
GetData FName, "Saturday", "b1:b2", Sheets("Source").Range("b2"),
False
GetData FName, "Saturday", "b2:b3", Sheets("Source").Range("b3"),
False

End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

ChDrive SaveDriveDir
ChDir SaveDriveDir
End Sub
 
Use this

'Previously posted by Rob Bovey:

Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

' sample usage

Sub Test()
Dim FName As Variant

ChDirNet "\\DELL\testing"
FName = Application.GetOpenFilename
End Sub
 
Christy said:
I have the following code which works perfectly on my office computer. I
sent it to the co-worker I made it for who is attached to the LAN and it
crashes on the line:

ChDrive MyPath

Does anyone have any idea what the problem is or how I can fix it. We
commented out that line and it crashed on the next on and we commented out
that line and it crashed on the next one.


Sub GetDataFromClosedWB()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

If FName = False Then
'do nothing
Else

'Get Fridays date and Office city/state/zip
GetData FName, "Saturday", "b1:b2", Sheets("Source").Range("b2"),
False
GetData FName, "Saturday", "b2:b3", Sheets("Source").Range("b3"),
False

End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

ChDrive SaveDriveDir
ChDir SaveDriveDir
End Sub
Click to expand...

Wouldn't it make sense to use an error handler and then show the error
description?

/Fredrik
 
Hi Christy,

On your co-worker's machine and in Excel, check
Tools/Options General Tab Default File Location. This
field may not have a valid path specified.

Best Regards,
Walt
-----Original Message-----
I have the following code which works perfectly on my office computer. I
sent it to the co-worker I made it for who is attached to the LAN and it
crashes on the line:

ChDrive MyPath

Does anyone have any idea what the problem is or how I can fix it. We
commented out that line and it crashed on the next on and we commented out
that line and it crashed on the next one.


Sub GetDataFromClosedWB()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename
Click to expand...
(filefilter:="Excel Files, *.xls")
 
Thanks Ron (again) I will try it.

Do I understandt right that the ChDirNet "\\DELL\testing" line is calling
the sub and that line should go in my(your)code?

Christy ;)
 
Back
Top