Excel Save to code

[Monster1979]

Hi everybody.

I have made an invoice in excel that make a new invoice number when opening etc. Everything is working fine but i need to save the invoice in a specified folder, which is also working fine. My problem is when i copy my whole work folder to usb and work on it from my laptop it still saves that file "With the written code" To D:\Drive and not to the given usb I:\ drive. Is there any code that i can write to save it to the current drive you are working from. Here is a sample of my code.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)


ActiveSheet.Copy
NEWFN = "D:\Manie Hansen\Invoices\" & "Invoice " & Range("M11").Value & " " & Range("E19").Value & ".xlsx"
ActiveWorkbook.SaveAs NEWFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close


End Sub

What i want is for the d:\Manie Hansen\..... to change to whatever drive number the USB get on another pc. For example I:\Manie Hansen. without having to change the code from pc to pc.

Thank You

 

[GermanPath]

Hello.

Save to pathdir where your workbook is stored in:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

ActiveSheet.Copy
NEWFN = Application.Path & "\" & "Invoice " & Range("M11").Value & " " & Range("E19").Value & ".xlsx"
ActiveWorkbook.SaveAs NEWFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close

End Sub

 

[Monster1979]

Hi There thanks a lot it, it now save to the c:\ Drive and not the usb but i see what direction i need to go to. Thank you very much.

 

[anvg]

This function returns a collection of USB drive's letters.

Public Function GetUSBDrives() As Collection
    Dim drive As Object, fso As Object
    Dim pUSBCol As New Collection
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each drive In fso.Drives
        If (drive.DriveType = 1) And drive.IsReady Then
            pUSBCol.Add drive.DriveLetter
        End If
    Next
End Function

 

[GermanPath]

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

ActiveSheet.Copy
NEWFN = ThisWorkbook.Path & "\" & "Invoice " & Range("M11").Value & " " & Range("E19").Value & ".xlsx"
ActiveWorkbook.SaveAs NEWFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close

End Sub

ThisWorkbook.Path will take the dir() where your file is stored. When this is on USB stick it will take this.

 

[Monster1979]

Hi GermanPath

My bad, never read the second one. It works like a charm.
Thank you very much.