Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 1004 - File could not be accessed
Hi there, i am getting a runtime error 1004...that the file could not be
accessed. The code it below, and the line in question is: sbFile.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\Copreco Reading.xlsx """ ================================================ Sub ExportCoprecoReadingData() Const sourceSheet = "Site Reading Log" ' source Dim sourceBook As String Dim destBook As String Dim sourceRange As Range Dim destRange As Range Application.ScreenUpdating = False sourceBook = ThisWorkbook.Name 'get the new workbook object Set sbFile = Workbooks.Add ' create new book destBook = ActiveWorkbook.Name Windows(sourceBook).Activate Worksheets(sourceSheet).Select Set sourceRange = ActiveSheet.Rows("1:1") Set destRange = Workbooks(destBook).Worksheets("Sheet1").Rows("1:1 ") destRange.Value = sourceRange.Value 'change "A" to column with data in last row Range("A" & Rows.Count).End(xlUp).Select Set sourceRange = ActiveSheet.Rows(ActiveCell.Row & ":" & ActiveCell.Row) Set destRange = Workbooks(destBook).Worksheets("Sheet1").Rows("2:2 ") destRange.Value = sourceRange.Value Set sourceRange = Nothing Set destRange = Nothing 'get the desktop path, save and close new workbook Set WshShell = CreateObject("WScript.Shell") sbFile.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\Copreco Reading.xlsx """ sbFile.Close Set WshShell = Nothing Set sbFile = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 1004 - File could not be accessed
It failed the second time for me because the item was already on my desktop.
The 1st time it ran perfectly. "Carlee" wrote: Hi there, i am getting a runtime error 1004...that the file could not be accessed. The code it below, and the line in question is: sbFile.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\Copreco Reading.xlsx """ ================================================ Sub ExportCoprecoReadingData() Const sourceSheet = "Site Reading Log" ' source Dim sourceBook As String Dim destBook As String Dim sourceRange As Range Dim destRange As Range Application.ScreenUpdating = False sourceBook = ThisWorkbook.Name 'get the new workbook object Set sbFile = Workbooks.Add ' create new book destBook = ActiveWorkbook.Name Windows(sourceBook).Activate Worksheets(sourceSheet).Select Set sourceRange = ActiveSheet.Rows("1:1") Set destRange = Workbooks(destBook).Worksheets("Sheet1").Rows("1:1 ") destRange.Value = sourceRange.Value 'change "A" to column with data in last row Range("A" & Rows.Count).End(xlUp).Select Set sourceRange = ActiveSheet.Rows(ActiveCell.Row & ":" & ActiveCell.Row) Set destRange = Workbooks(destBook).Worksheets("Sheet1").Rows("2:2 ") destRange.Value = sourceRange.Value Set sourceRange = Nothing Set destRange = Nothing 'get the desktop path, save and close new workbook Set WshShell = CreateObject("WScript.Shell") sbFile.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\Copreco Reading.xlsx """ sbFile.Close Set WshShell = Nothing Set sbFile = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime 1004 error | Excel Programming | |||
runtime error 1004 saveas text file | Excel Programming | |||
File Name Causes Runtime Error 1004 | Excel Programming | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
runtime error 1004 when opening excel file via VBA in a browser | Excel Programming |