Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File
Hi
If I save my file "manually", using File, Save As, I get no issue. If I try saving with the following code: Sub SaveFile() Dim strFileName As String Dim strFilePath As String Dim strYear As String strYear = Worksheets("Dates").range("PrevWrkDay") strYear = Format(strYear, "yyyy") strFileName = "European Exchange Volume " & strYear & ".xls" strFilePath = "\\atkams\Credit Lists\" application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName application.DisplayAlerts = True End Sub I get run-time error '1004': cannot access 'filename' What am I doing wrong. Thanks in advance Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File
Try an explicit path ??
strFilePath = "C:\atkams\Credit Lists\" hth Geoff K "Richard" wrote: Hi If I save my file "manually", using File, Save As, I get no issue. If I try saving with the following code: Sub SaveFile() Dim strFileName As String Dim strFilePath As String Dim strYear As String strYear = Worksheets("Dates").range("PrevWrkDay") strYear = Format(strYear, "yyyy") strFileName = "European Exchange Volume " & strYear & ".xls" strFilePath = "\\atkams\Credit Lists\" application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName application.DisplayAlerts = True End Sub I get run-time error '1004': cannot access 'filename' What am I doing wrong. Thanks in advance Richard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File
Geoff,
I was trying to avoid that, as it isn't a path that everyone has mapped. Is that the only reliable method? Richard "Geoff K" wrote: Try an explicit path ?? strFilePath = "C:\atkams\Credit Lists\" hth Geoff K "Richard" wrote: Hi If I save my file "manually", using File, Save As, I get no issue. If I try saving with the following code: Sub SaveFile() Dim strFileName As String Dim strFilePath As String Dim strYear As String strYear = Worksheets("Dates").range("PrevWrkDay") strYear = Format(strYear, "yyyy") strFileName = "European Exchange Volume " & strYear & ".xls" strFilePath = "\\atkams\Credit Lists\" application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName application.DisplayAlerts = True End Sub I get run-time error '1004': cannot access 'filename' What am I doing wrong. Thanks in advance Richard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File
No.
I think that Geoff was suggesting that you get the guts of the code working using an explicit path. Then when you change to that network path, you'll know that the problem is either your spelling of that network path--or a network problem itself. Can you manually save a file to that location? (Maybe it's a permissions thing.) Richard wrote: Geoff, I was trying to avoid that, as it isn't a path that everyone has mapped. Is that the only reliable method? Richard "Geoff K" wrote: Try an explicit path ?? strFilePath = "C:\atkams\Credit Lists\" hth Geoff K "Richard" wrote: Hi If I save my file "manually", using File, Save As, I get no issue. If I try saving with the following code: Sub SaveFile() Dim strFileName As String Dim strFilePath As String Dim strYear As String strYear = Worksheets("Dates").range("PrevWrkDay") strYear = Format(strYear, "yyyy") strFileName = "European Exchange Volume " & strYear & ".xls" strFilePath = "\\atkams\Credit Lists\" application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName application.DisplayAlerts = True End Sub I get run-time error '1004': cannot access 'filename' What am I doing wrong. Thanks in advance Richard -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File
Dave
Yes I can manually save a file to that location and yes if I explicitly state the drive mapping ie L: it works so it doesn't appear to be a permission issue and beleive me I have checked and re-checked the spelling of the path name and I am certain it is correct. Richard "Dave Peterson" wrote: No. I think that Geoff was suggesting that you get the guts of the code working using an explicit path. Then when you change to that network path, you'll know that the problem is either your spelling of that network path--or a network problem itself. Can you manually save a file to that location? (Maybe it's a permissions thing.) Richard wrote: Geoff, I was trying to avoid that, as it isn't a path that everyone has mapped. Is that the only reliable method? Richard "Geoff K" wrote: Try an explicit path ?? strFilePath = "C:\atkams\Credit Lists\" hth Geoff K "Richard" wrote: Hi If I save my file "manually", using File, Save As, I get no issue. If I try saving with the following code: Sub SaveFile() Dim strFileName As String Dim strFilePath As String Dim strYear As String strYear = Worksheets("Dates").range("PrevWrkDay") strYear = Format(strYear, "yyyy") strFileName = "European Exchange Volume " & strYear & ".xls" strFilePath = "\\atkams\Credit Lists\" application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName application.DisplayAlerts = True End Sub I get run-time error '1004': cannot access 'filename' What am I doing wrong. Thanks in advance Richard -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File
I'm betting that you'll find that you made a mistake in the UNC path.
Maybe opening windows explorer and looking at how the L: was mapped will help find your typo. Richard wrote: Dave Yes I can manually save a file to that location and yes if I explicitly state the drive mapping ie L: it works so it doesn't appear to be a permission issue and beleive me I have checked and re-checked the spelling of the path name and I am certain it is correct. Richard "Dave Peterson" wrote: No. I think that Geoff was suggesting that you get the guts of the code working using an explicit path. Then when you change to that network path, you'll know that the problem is either your spelling of that network path--or a network problem itself. Can you manually save a file to that location? (Maybe it's a permissions thing.) Richard wrote: Geoff, I was trying to avoid that, as it isn't a path that everyone has mapped. Is that the only reliable method? Richard "Geoff K" wrote: Try an explicit path ?? strFilePath = "C:\atkams\Credit Lists\" hth Geoff K "Richard" wrote: Hi If I save my file "manually", using File, Save As, I get no issue. If I try saving with the following code: Sub SaveFile() Dim strFileName As String Dim strFilePath As String Dim strYear As String strYear = Worksheets("Dates").range("PrevWrkDay") strYear = Format(strYear, "yyyy") strFileName = "European Exchange Volume " & strYear & ".xls" strFilePath = "\\atkams\Credit Lists\" application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName application.DisplayAlerts = True End Sub I get run-time error '1004': cannot access 'filename' What am I doing wrong. Thanks in advance Richard -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving File
Unless you have sorted the problem this may be an alternative:
Sub SaveFile() Dim strFileName As String Dim strFilePath As String Dim strYear As String Dim fname As Variant strYear = Worksheets("Dates").Range("PrevWrkDay") strYear = Format(strYear, "yyyy") strFileName = "European Exchange Volume " & strYear & ".xls" fname = Application.GetSaveAsFilename(strFileName) If Not fname = False Then Application.DisplayAlerts = False ActiveWorkbook.SaveAs strFileName Application.DisplayAlerts = True End If End Sub hth Geoff K "Dave Peterson" wrote: I'm betting that you'll find that you made a mistake in the UNC path. Maybe opening windows explorer and looking at how the L: was mapped will help find your typo. Richard wrote: Dave Yes I can manually save a file to that location and yes if I explicitly state the drive mapping ie L: it works so it doesn't appear to be a permission issue and beleive me I have checked and re-checked the spelling of the path name and I am certain it is correct. Richard "Dave Peterson" wrote: No. I think that Geoff was suggesting that you get the guts of the code working using an explicit path. Then when you change to that network path, you'll know that the problem is either your spelling of that network path--or a network problem itself. Can you manually save a file to that location? (Maybe it's a permissions thing.) Richard wrote: Geoff, I was trying to avoid that, as it isn't a path that everyone has mapped. Is that the only reliable method? Richard "Geoff K" wrote: Try an explicit path ?? strFilePath = "C:\atkams\Credit Lists\" hth Geoff K "Richard" wrote: Hi If I save my file "manually", using File, Save As, I get no issue. If I try saving with the following code: Sub SaveFile() Dim strFileName As String Dim strFilePath As String Dim strYear As String strYear = Worksheets("Dates").range("PrevWrkDay") strYear = Format(strYear, "yyyy") strFileName = "European Exchange Volume " & strYear & ".xls" strFilePath = "\\atkams\Credit Lists\" application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName application.DisplayAlerts = True End Sub I get run-time error '1004': cannot access 'filename' What am I doing wrong. Thanks in advance Richard -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving Excel 2007 file in 2003 creates very large file | Excel Discussion (Misc queries) | |||
Excel should let me sort the file directory when saving a file | Excel Discussion (Misc queries) | |||
Saving multi-tab excel file created from comma delimited text file | Excel Programming | |||
How do I stop Excel 2000 from saving file history from file that . | Excel Discussion (Misc queries) | |||
saving an excel file as an ASCII text file without delimiters | Excel Programming |