How to save a file using "if then or else" in case the 1 drive is not found

Henriette

New Member
Joined
Sep 11, 2019
Messages
2
<ytd-expander id="expander" class="expander-exp style-scope ytd-comment-renderer" style="display: block; --ytd-expander-button-margin:4px 0 0 0; --ytd-expander-collapsed-height:80px;"><yt-formatted-string id="content-text" slot="content" split-lines="" class="style-scope ytd-comment-renderer" style="white-space: pre-wrap; --yt-endpoint-color:var(--yt-spec-call-to-action); --yt-endpoint-hover-color:var(--yt-spec-call-to-action); --yt-endpoint-visited-color:var(--yt-spec-call-to-action); color: var(--ytd-comment-text-color); font-size: 1.4rem; line-height: 2rem;">Hello and thank you for my acceptance on your site, one question....
I made an invoice-manager system using your site for help it works perfect but.....
My office has 4 pc's and I work from shared folders (Z://). The main drive is C... . Ofcourse when I work my invoice-manager from the C-drive my vba code for saving the file doesn't work.
How to use the IF,THEN,ELSE vba code so that if work from the C drive and it doesn't recognize this command it goes to a second command of saving the file at the C:// drive?
THIS IS MY VBA MODULE:Sub InvoiceReport()Dim myFile As String, lastRow As LongmyFile = "Z:\HLEKTRONIKH TIMOLOGISH\INVOICE MANAGER" & Sheets("Invoice").Range("J13") & "_" & Sheets("Invoice").Range("C13") & "_" & Sheets("Invoice").Range("J40") & "€" & "_" & Format(Now(), "dd-mm-yyyy") & "_" & ".xlsm"lastRow = Sheets("Data-Invoices").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1'create invoice in XLSX formatActiveWorkbook.SaveAs "Z:\HLEKTRONIKH TIMOLOGISH\INVOICE MANAGER" & Sheets("Invoice").Range("J13") & "_" & Sheets("Invoice").Range("C13") & "_" & Sheets("Invoice").Range("J40") & "€" & "_" & Format(Now(), "dd-mm-yyyy") & "_" & ".xlsm", FileFormat:=52'ActiveWorkbook.Close</yt-formatted-string>

</ytd-expander><ytd-comment-action-buttons-renderer id="action-buttons" class="style-scope ytd-comment-renderer" action-buttons-style="desktop-toolbar" style="display: block; color: var(--yt-spec-text-secondary); min-height: 16px; margin-top: 4px;">
</ytd-comment-action-buttons-renderer>

<ytd-menu-renderer class="style-scope ytd-comment-renderer" style="display: var(--layout-horizontal_-_display); flex-direction: var(--layout-horizontal_-_flex-direction);"></ytd-menu-renderer>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
Hi

welcome to forum

See if this change to your code does want you want

Code:
Sub InvoiceReport()
    Dim myFile As String
    Dim SharedFolder As Boolean
    
    With Sheets("Invoice")
    myFile = "Z:\HLEKTRONIKH TIMOLOGISH\INVOICE MANAGER" & _
                .Range("J13") & "_" & _
                .Range("C13") & "_" & _
                .Range("J40") & "€" & "_" & _
                Format(Now(), "dd-mm-yyyy") & "_" & ".xlsm"
    End With
                
'check if shared folder available
    SharedFolder = CBool(Dir(myFile, vbDirectory) <> vbNullString)
'if not change to local drive
    If Not SharedFolder Then myFile = Replace(myFile, "Z", "C", 1, 1)
'save workbook
    ActiveWorkbook.SaveAs myFile, FileFormat:=52
'inform user
    MsgBox "Invoice Saved To " & IIf(SharedFolder, "Shared Folder", "Local Folder"), 64, "Save"
'close
    ActiveWorkbook.Close False
End Sub

If not, please explain further, plenty here to help you

Dave
 
Last edited:

Henriette

New Member
Joined
Sep 11, 2019
Messages
2
Thank you Dave it helped a lot but I have one question for now because it say that the document is saved but I can not find it.
the ecxact directory (there were the shared folder comes from on the main pc is: C:\Users\Public\Documents\HLEKTRONIKH TIMOLOGISH\INVOICE MANAGER
This is the shared folder were my boss has to save the document who is working from the main pc.
The other 3 pc are working with this shared folder and at all 3 pc the path name is Z:\HLEKTRONIKH TIMOLOGISH\INVOICE MANAGER\
HOW TO CHANGE THE VBA CODE TO SAVE THE DOCUMENT TO C:\Users\Public\Documents\HLEKTRONIKH TIMOLOGISH\INVOICE MANAGER\
IF IT DOESNT FIND THE Z:\.... PATH IN THE FIRST PLACE

Thanks
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try this update

Code:
Sub InvoiceReport()
    Dim myFolder As String, myFileName As String
    Dim SharedFolder As Boolean
    
    myFolder = "Z:\HLEKTRONIKH TIMOLOGISH\INVOICE MANAGER"
'check if shared folder available
    SharedFolder = CBool(Dir(myFolder, vbDirectory) <> vbNullString)
    If Not SharedFolder Then
'use local folder
        myFolder = "C:\Users\Public\Documents\HLEKTRONIKH TIMOLOGISH\INVOICE MANAGER"
    End If
    
'get filename from ranges
    With Worksheets("Invoice")
        myFileName = .Range("J13") & "_" & _
        .Range("C13") & "_" & _
        .Range("J40") & "€" & "_" & _
        Format(Now(), "dd-mm-yyyy") & "_" & ".xlsm"
    End With
    
    On Error GoTo myerror
'save workbook
    ActiveWorkbook.SaveAs myFolder & "\" & myFileName, FileFormat:=52
'inform user
    MsgBox "Invoice Saved To " & IIf(SharedFolder, "Shared Folder", "Local Folder"), 64, "Save"
'close
    ActiveWorkbook.Close False
    
myerror:
'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,129,675
Messages
5,637,733
Members
416,981
Latest member
PLonchar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top