SaveAs 'Cannot access file' problem
SaveAs 'Cannot access file' problem
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: SaveAs 'Cannot access file' problem

  1. #1
    Board Regular
    Join Date
    May 2009
    Location
    Wythenshawe Manchester
    Posts
    703
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SaveAs 'Cannot access file' problem

     
    The code below stops with the error "Runtime error 1004: Cannot access THURSDAY 10.5.12.xls" at the red line. The workbook of that name already exists in that folder, and should prompt me whether I want to save over it or not (as a similar macro I have used for years did) but stops with this error. Anyone know why, please? Thanks for your time.
    Nigel

    Code:
    Sub GetSOInfo()
        Dim so As Worksheet, f As Worksheet, ri As Range, cel As Range, col As Range, day As Integer, DateInc As Integer
        Dim c As Integer, p As Integer, a, OldDate As String, NewDate As String
        Dim dte As Date, dc As String, dp As String
        dte = Worksheets("Workpad").[b10]
        If WorksheetFunction.Weekday(dte) = 7 Then
            DateInc = 2
        Else
            DateInc = 1
        End If
        OldDate = UCase(WorksheetFunction.Text(dte, "DDDD")) & " " & WorksheetFunction.Text(dte, "D.M.Y")
        NewDate = UCase(WorksheetFunction.Text(dte + DateInc, "DDDD")) & " " & WorksheetFunction.Text(dte + DateInc, "D.M.Y")
        If MsgBox("Do you want to set up the standing order figures for " & NewDate & "?" & vbCr & vbCr & _
            "THIS WILL CLEAR ALL EXISTING FIGURES FROM THE ORDER SHEET", vbYesNo, "ARE YOU SURE?") = vbNo Then Exit Sub
        Application.ScreenUpdating = False
        ActiveWorkbook.SaveAs Filename:="\\Think\c\NICE BUNS\NICE BUNS BAKERY\ORDERS\DAILY ORDERS\" & OldDate & ".xls"
        MsgBox OldDate & " saved sucessfully"
    
    .....etc
    Last edited by nigelandrewfoster; May 10th, 2012 at 07:09 AM.
    Excel 2010 for Windows, 2011 for Mac (alas) - God is good. ALL THE TIME!

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,702
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SaveAs 'Cannot access file' problem

    The only think I can think of is that the file is in use (?). I generally take a "paranoid" approach and delete the file explicitly/check that it was deleted:
    Code:
    DIM FSO as Object
    Dim sFile As String
    sFile = "\\Think\c\NICE BUNS\NICE BUNS BAKERY\ORDERS\DAILY ORDERS\" & OldDate & ".xls"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(sFile) Then
        FSO.DeleteFile sFile
    End If
    If FSO.FileExists(sFile) Then
        MsgBox "Error: file " & sFile " cannot be deleted."
        '//Appropriately abort the code here
    End If
    Causes of undeletable files are sometimes obscure though. I have no problem 99.5% of the time and once in a blue moon for no apparent reason I can't delete a file.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    May 2009
    Location
    Wythenshawe Manchester
    Posts
    703
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SaveAs 'Cannot access file' problem

    Thanks very much. I will try your approach. I have never used CreateObject before. Will investigate!
    Excel 2010 for Windows, 2011 for Mac (alas) - God is good. ALL THE TIME!

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,702
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SaveAs 'Cannot access file' problem

    You can explicitly reference the FileSystemObject too:

    1. In the visual basic editor go to Tools | References
    2. Check the box for "Microsoft Scripting Runtime"


    Now in your code you can declare the object:
    Code:
    Dim FSO As FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    This has the advantage of giving you intellisense when you use the object. The FSO object is a little more sophisticated than inbuilt VBA file commands, and I have a suspicion it understands UNC filepaths a little better, will read/write unicode, and so on, so it's slightly more flexible in practice.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Sep 2010
    Location
    Sunnyvale, CA. USA
    Posts
    541
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SaveAs 'Cannot access file' problem

    If you are using a Ctrl-Shift key combo to run your macro and you get this error 1004, try running it instead from the the VBA editor and pushing F5. If the error goes away, try removing the "Shift" key from your macro key combination.

    I've had this 1004 run-time error intermittantly with macros launched with a "shift" key combo when opening workbooks from the macro. It is a bug in Microsoft that affects workbooks opening, and perhaps with your code saving a workbook.

    It was a baffling intermittant error I could never figure out until I ran across this article:

    http://www.jkp-ads.com/Articles/WorkbookOpenBug.asp
    Patience and perseverance are qualities of enduring. And with help from God and through others, are where answers can be found.

    ==== . -..- -.-. . .-.. =====
    I use Excel 2007, Windows 7
    == ..--- ----- ----- --... ==

  6. #6
    Board Regular
    Join Date
    May 2009
    Location
    Wythenshawe Manchester
    Posts
    703
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SaveAs 'Cannot access file' problem

      
    Thanks very much.
    Excel 2010 for Windows, 2011 for Mac (alas) - God is good. ALL THE TIME!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com