SaveAs Failing

SaraO · Feb 17th 2016

Hi,

I have an invoice template that we are trying to use on our network. The Invoice Workbook is in a folder on our network server computer as well as the folder where the invoices are being saved to. When I use the invoice on 2 computers that are connected to the server everything works great, but when I go to the server computer to create an invoice from there I keep getting an error message saying the SaveAs failed.

Does anyone have any ideas as to why it would work from two computers and not the other one?

Grimes0332 · Feb 17th 2016

Re: SaveAs Failing

Drive mappings are usually different on network clients. Post a sample of your code.

SaraO · Feb 17th 2016

Re: SaveAs Failing

This is the code I'm using...

Code

Public Sub Save_Invoice()
     Application.ScreenUpdating = False
     
     NextInvoice
    
    PrintInvoice
     
    Dim SaveAsFileName As Variant
    Dim newWorkbook As Workbook
     
    SaveAsFileName = "V:\Invoices\Inv" & " " & Range("I10").Value & " " & Range("I11").Value & ".xlsx"
      
     'Create new workbook with 1 empty sheet
     
    Set newWorkbook = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
     
     'Copy entire Invoice sheet to new workbook - this becomes the first of 2 sheets in new workbook
     
    ThisWorkbook.Worksheets("Invoice").Copy Before:=newWorkbook.Worksheets(1)
     
     'Tidy up copied Invoice sheet
     
    With newWorkbook.Worksheets(1)
                  
          'Convert formulas to their values
         
        .Range("I11").Value = .Range("I11").Value 'Date
        .Range("B11").Value = .Range("B11").Value 'Address
        .Range("B12").Value = .Range("B12").Value 'Phone #
        .Range("B13").Value = .Range("B13").Value 'Email
        .Range("B14").Value = .Range("B14").Value 'Email
        .Range("J16:J46").Value = .Range("J16:J46").Value 'Totals
        .Range("H41:H46").Value = .Range("H41:H46").Value 'Hiden formulas
        .Range("B1").Value = .Range("B1").Value 'Hiden Formula
         
         'Delete data validation dropdowns
         
        .Range("B10").Validation.Delete
        .Range("I12").Validation.Delete
        .Range("F42").Validation.Delete
 
         'Delete unwanted columns, including hidden lookup table
         
        .Columns("K:AZ").Delete
    End With
     
     'Suppress all Excel warnings: deleting sheet; saving as macro-free workbook; file already exists
     
    Application.DisplayAlerts = False
     
     'Delete empty sheet
     
    newWorkbook.Sheets(2).Delete
     
     'Save new workbook in xlsx format
     
    newWorkbook.SaveAs SaveAsFileName, xlOpenXMLWorkbook
    newWorkbook.Close False
     
     'Email PDF Invoice
     
     AttachActiveSheetPDF
     
     'Clear Incoice
     
    Clear_Invoice
     
    'Reenable Excel warnings
     
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True
     
End Sub

Display More

The debugger highlights this line in the code "newWorkbook.SaveAs SaveAsFileName, xlOpenXMLWorkbook"

Grimes0332 · Feb 17th 2016

Re: SaveAs Failing

And drive V exists on the machine that errors?

shknbk2 · Feb 17th 2016

Re: SaveAs Failing

I think Grimes0332 might be right. Your server might not map the V drive to a location on its C drive. What happens on the server if you use the full path location of the Invoices folder in place of "V:"? For example, find the actual Invoices folder location on the server and copy the complete file path and temporarily paste it for "V:"?

SaraO · Feb 17th 2016

Re: SaveAs Failing

The V drive does exist on the error computer.

I found the actual folder location and it is the same as the code. I even tried changing the folder location and put the new folder path into the code to see if that would work and no luck.

Grimes0332 · Feb 17th 2016

Re: SaveAs Failing

What is the actual error number/description and is this a Citrix environment?

SaraO · Feb 17th 2016

Re: SaveAs Failing

The error message says...

Run-time error '1004':
Method 'SaveAs' of '_Workbook' failed

Sorry I'm not sure what a Citrix environment is. Still pretty new at this.

Grimes0332 · Feb 18th 2016

Re: SaveAs Failing

Sorry, I can't really help. It's difficult to suggest a solution as the code is essentially correct.

Just try one thing to see if that works.

Use ChDir to change the default directory

Code

ChDir [COLOR=#333333]"V:\Invoices"

[/COLOR]And then save using just the filename

Code

[COLOR=#333333]SaveAsFileName = "Inv" & " " & Range("I10").Value & " " & Range("I11").Value & ".xlsx"

[/COLOR]Other than that, I'm out of ideas.

SaraO · Feb 18th 2016

Re: SaveAs Failing

I tried your suggestion and no luck...

Could it have anything to do with the operating system?

I am getting other errors with more VBA codes on the problem computer, the same codes work on two other computers.

The Problem computer has Windows 7, the two that work have Windows 8 and 10.

SaveAs Failing

Participate now!

Share