ExcelBanter - copy from closed workbook again! ;(

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - copy from closed workbook again! ;( (https://www.excelbanter.com/excel-programming/324619-copy-closed-workbook-again-%3B.html)

Christy

copy from closed workbook again! ;(

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

Ron de Bruin

copy from closed workbook again! ;(

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Christy" wrote in 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(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

Fredrik Wahlgren

copy from closed workbook again! ;(

"Christy" wrote in 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(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

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

/Fredrik

Walt Weber[_2_]

copy from closed workbook again! ;(

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
(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

.

Christy

copy from closed workbook again! ;(

Thank you Walt, I will have him check that setting.

"Walt Weber" wrote:

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
(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

.

Christy

copy from closed workbook again! ;(

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 ;)

"Ron de Bruin" wrote:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Christy" wrote in 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(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

Ron de Bruin

copy from closed workbook again! ;(

Yes

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Christy" wrote in message ...
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 ;)

"Ron de Bruin" wrote:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Christy" wrote in 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(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

All times are GMT +1. The time now is 01:46 PM.