| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy data from a closed workbook (ADO) | Excel Programming | |||
| copy data from a closed workbook | Excel Programming | |||
| Possible to copy sheets into another (closed!) workbook? | Excel Discussion (Misc queries) | |||
| copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions | |||
| How to copy a range to a closed workbook | Excel Programming | |||
Linear Mode
