Hello,
I hope I can find help here.
I found another user that asked a similar questions 120 days ago:
I have followed a video series on youtube to create an invoice. That invoice has buttons to do macros/vba. it works very well and I am almost happy with it.
One of the buttons is to save the invoice as a PDF the other button saves as a .xlsx file. Each of these work and paste the hyperlink onto the order records sheet. However, clicking the two seperate buttons saves it on two different lines when it should be on the same line.
The series I followed and enjoyed was https://www.youtube.com/playlist?list=PLA3JEasWtYad0OeX78k0gFhxm5qnnaD57.
I am in the same boat as this person. At the moment, I cannot have the saved files with the extension (Fileformat:=51). It saves it without extension. I need to specify that I am on MAC OS.
I don't know if this makes a difference, but on my invoice sheet, I have a QR code that is generated as the information gets filled in. I did not set up a button for it, I added the image function and the information used for this function are on other cells that I hide. This QR code generator uses an API (for bank payment via QR code)
When I run the SaveInvAsExcel (), it does what it is supposed to do, but the saved file is missing the extension (.xlsx)
Anyone can guide me towards a solution?
This is the Macro I have issues with:
Sub SaveInvAsExcel()
Dim invno As Long
Dim custname As String
Dim amt As Currency
Dim dt_issue As Date
Dim term As Long
Dim path As String
Dim fname As String
Dim nextrec As Range
invno = Range("G1")
custname = Range("E13")
amt = Range("H37")
dt_issue = Range("B19")
term = Range("B20")
path = "/Users/rodsmacbook14m1pro/Library/CloudStorage/OneDrive-Madhouses.r.o/Desktop/NUPEAKS/ACCOUNTING/2024/ACCOUNTANT DOCS/INCOMES INVOICES/EXCEL INVOICES/"
fname = invno & " _ " & custname
'copy the invoice sheet to a new workbook
Sheet1.Copy
'then delete all the buttons on the worksheet
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next shp
'save the new workbook to a specified folder
With ActiveWorkbook
.Sheets(1).Name = "Invoice"
.SaveAs FileName:=path & fname, FileFormat:=51
.Close
End With
'need to put the details of the invoice in the record of invoices sheet
Set nextrec = Sheet3.Range("A104876").End(xlUp).Offset(1, 0)
nextrec = invno
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = amt
nextrec.Offset(0, 3) = dt_issue
nextrec.Offset(0, 4) = term
Sheet3.Hyperlinks.Add anchor:=nextrec.Offset(0, 7), Address:=path & fname & ".xlsx"
End Sub
Quote Reply
I hope I can find help here.
I found another user that asked a similar questions 120 days ago:
I have followed a video series on youtube to create an invoice. That invoice has buttons to do macros/vba. it works very well and I am almost happy with it.
One of the buttons is to save the invoice as a PDF the other button saves as a .xlsx file. Each of these work and paste the hyperlink onto the order records sheet. However, clicking the two seperate buttons saves it on two different lines when it should be on the same line.
The series I followed and enjoyed was https://www.youtube.com/playlist?list=PLA3JEasWtYad0OeX78k0gFhxm5qnnaD57.
I am in the same boat as this person. At the moment, I cannot have the saved files with the extension (Fileformat:=51). It saves it without extension. I need to specify that I am on MAC OS.
I don't know if this makes a difference, but on my invoice sheet, I have a QR code that is generated as the information gets filled in. I did not set up a button for it, I added the image function and the information used for this function are on other cells that I hide. This QR code generator uses an API (for bank payment via QR code)
When I run the SaveInvAsExcel (), it does what it is supposed to do, but the saved file is missing the extension (.xlsx)
Anyone can guide me towards a solution?
This is the Macro I have issues with:
Sub SaveInvAsExcel()
Dim invno As Long
Dim custname As String
Dim amt As Currency
Dim dt_issue As Date
Dim term As Long
Dim path As String
Dim fname As String
Dim nextrec As Range
invno = Range("G1")
custname = Range("E13")
amt = Range("H37")
dt_issue = Range("B19")
term = Range("B20")
path = "/Users/rodsmacbook14m1pro/Library/CloudStorage/OneDrive-Madhouses.r.o/Desktop/NUPEAKS/ACCOUNTING/2024/ACCOUNTANT DOCS/INCOMES INVOICES/EXCEL INVOICES/"
fname = invno & " _ " & custname
'copy the invoice sheet to a new workbook
Sheet1.Copy
'then delete all the buttons on the worksheet
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next shp
'save the new workbook to a specified folder
With ActiveWorkbook
.Sheets(1).Name = "Invoice"
.SaveAs FileName:=path & fname, FileFormat:=51
.Close
End With
'need to put the details of the invoice in the record of invoices sheet
Set nextrec = Sheet3.Range("A104876").End(xlUp).Offset(1, 0)
nextrec = invno
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = amt
nextrec.Offset(0, 3) = dt_issue
nextrec.Offset(0, 4) = term
Sheet3.Hyperlinks.Add anchor:=nextrec.Offset(0, 7), Address:=path & fname & ".xlsx"
End Sub
Quote Reply
