D
Dan M. Marr
I'm Using Windows XP with Excel 2002
I'm writing a macro to save a Spreadsheet to a local hard
drive or a network drive with a suggested file name,
located is cell "A1" of "Sheet1". Cell "A1" has
a "CONCATENATE" statement.
=CONCATENATE(I57," ",(VLOOKUP(B57,'Summary Sheet'!
AZ3:BC11,4))," ",O51)
The Cell Values are:
I57 = 03-09-150
B57 = Dan M. Marr, the returned lookup value is DM
O51 = Fire Alarm for Computer Room
The above CONCATENATE statement results are:
03-09-105 DM Fire Alarm for Computer Room
I don't want the macro to automatically save the file but
rather insert the file name into the "File name" location
of the "File Save" or "SaveAs" dialog box. I want the
user to review the suggested file name before accepting
it.
I had a problem with access to the network but have since
resolved that with the following statement placed at the
top of the module which stores "File_Save_Network()"
macro:
Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long
The following macro was working great in another
spreadsheet I was testing but when inserted into my
application I get an error message:
Run-time error '1004'
Method 'Range' of object '_Global' failed
which points to the 2nd line in my code:
Sub File_Save_Network()
ChDirNet "\\Advint01\D Drive\Quotes\Contracting"
fileSaveName = Application.GetSaveAsFilename(Range _
("A"))
If fileSaveName <> False Then
MsgBox "Save as " & fileSaveName
End If
End Sub
The ChDirNet is actually another Macro which looks like:
Sub ChDirNet(szPath As String)
' Called by the "File_Open_Network" function
Dim AReturn As Long
AReturn = SetCurrentDirectoryA(szPath)
If AReturn = o Then Err.Raise vbOjectError +_
1, "Error setting path"
End Sub
The macro which saves the file to the local hard drive is
similar but uses the "ChDir" statement instead
of "ChDirNet" and generates the same error message,
pointing to the same line of code.
Any Ideas?
I'm writing a macro to save a Spreadsheet to a local hard
drive or a network drive with a suggested file name,
located is cell "A1" of "Sheet1". Cell "A1" has
a "CONCATENATE" statement.
=CONCATENATE(I57," ",(VLOOKUP(B57,'Summary Sheet'!
AZ3:BC11,4))," ",O51)
The Cell Values are:
I57 = 03-09-150
B57 = Dan M. Marr, the returned lookup value is DM
O51 = Fire Alarm for Computer Room
The above CONCATENATE statement results are:
03-09-105 DM Fire Alarm for Computer Room
I don't want the macro to automatically save the file but
rather insert the file name into the "File name" location
of the "File Save" or "SaveAs" dialog box. I want the
user to review the suggested file name before accepting
it.
I had a problem with access to the network but have since
resolved that with the following statement placed at the
top of the module which stores "File_Save_Network()"
macro:
Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long
The following macro was working great in another
spreadsheet I was testing but when inserted into my
application I get an error message:
Run-time error '1004'
Method 'Range' of object '_Global' failed
which points to the 2nd line in my code:
Sub File_Save_Network()
ChDirNet "\\Advint01\D Drive\Quotes\Contracting"
fileSaveName = Application.GetSaveAsFilename(Range _
("A"))
If fileSaveName <> False Then
MsgBox "Save as " & fileSaveName
End If
End Sub
The ChDirNet is actually another Macro which looks like:
Sub ChDirNet(szPath As String)
' Called by the "File_Open_Network" function
Dim AReturn As Long
AReturn = SetCurrentDirectoryA(szPath)
If AReturn = o Then Err.Raise vbOjectError +_
1, "Error setting path"
End Sub
The macro which saves the file to the local hard drive is
similar but uses the "ChDir" statement instead
of "ChDirNet" and generates the same error message,
pointing to the same line of code.
Any Ideas?