hi everyone,
Hope all doing well, i am trying to do Invoice automation but I am facing problem of Run time error 1004 file Could not found on specific location. I have checked file name & location and also tried by changing file location but still problem is persist. can any one help me out
I have debug on below code :
Workbooks.Open ("D:\Contactdetails\desktopfiles\invoicesample\invoice.xlsx")
My full code is as per below
Private Sub CommandButton1_Click()
Dim qty As String
Dim unitprice As String
Dim bankname As String
Dim address As String
Dim city As String
Dim r As Long
Dim state As String
Dim zip As String
Dim mobileno As Long
Dim phonenumber As String
Dim bankid As String
Dim strfilepath As String
Dim strfilename As String
Dim myfilename As String
Dim mydate As String
lastrow = Sheets("invoiceinfo").Range("A" & Rows.Count).End(xlUp).Row
r = 6
For r = 6 To lastrow
If Cells(r, 21).Value = "done" Then GoTo nextrow
outward = Sheets("invoiceinfo").Cells(r, 3).Value
Invoice = Sheets("invoiceinfo").Cells(r, 4).Value
Resources = Sheets("invoiceinfo").Cells(r, 6).Value
qty = Sheets("invoiceinfo").Cells(r, 7).Value
unitprice = Sheets("invoiceinfo").Cells(r, 8).Value
Vat = Sheets("invoiceinfo").Cells(r, 9).Value
Servicetax = Sheets("invoiceinfo").Cells(r, 10).Value
Total = Sheets("invoiceinfo").Cells(r, 11).Value
kindattn = Sheets("invoiceinfo").Cells(r, 12).Value
bankname = Sheets("invoiceinfo").Cells(r, 13).Value
address = Sheets("invoiceinfo").Cells(r, 14).Value
city = Sheets("invoiceinfo").Cells(r, 15).Value
state = Sheets("invoiceinfo").Cells(r, 16).Value
zip = Sheets("invoiceinfo").Cells(r, 17).Value
mobileno = Sheets("invoiceinfo").Cells(r, 18).Value
phonenumber = Sheets("invoiceinfo").Cells(r, 19).Value
bankid = Sheets("invoiceinfo").Cells(r, 20).Value
Cells(r, 21).Value = "done"
Application.DisplayAlerts = True
Workbooks.Open ("D:\Contactdetails\desktopfiles\invoicesample\invoice.xlsx")
ActiveWorkbook.Sheets("invoice").Activate
ActiveWorkbook.Sheets("invoice").Range("D9").Value = bankid
ActiveWorkbook.Sheets("invoice").Range("D11").Value = kindattn
ActiveWorkbook.Sheets("invoice").Range("D13:H14").Value = Address1
ActiveWorkbook.Sheets("invoice").Range("D15").Value = city
ActiveWorkbook.Sheets("invoice").Range("F15").Value = state
ActiveWorkbook.Sheets("invoice").Range("H15").Value = zip
ActiveWorkbook.Sheets("invoice").Range("H15").Value = mobileno
ActiveWorkbook.Sheets("Invoice").Range("D17").Value = Phoneno
ActiveWorkbook.Sheets("invoice").Range("M14").Value = Outwardno
ActiveWorkbook.Sheets("invoice").Range("M15").Value = Invoiceno
ActiveWorkbook.Sheets("invoice").Range("E20:H20").Value = resource
ActiveWorkbook.Sheets("invoice").Range("J20").Value = qty
ActiveWorkbook.Sheets("invoice").Range("L20").Value = unitprice
ActiveWorkbook.Sheets("invoice").Range("M20").Value = Total
ActiveWorkbook.Sheets("invoice").Range("M38").Value = Vat
ActiveWorkbook.Sheets("invoice").Range("M39").Value = Servicetax
path = "D:\contact details\"
mydate = Date
mydate = Format(mydate, "DD_MM_YYYY")
ActiveWorkbook.SaveAs Filename:=path & Invoice & "-" & Date & "-" & bankname & ".Xlsx"
myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True
'ActiveWorkbook.PrintOut Copies:=1
Activewrokbook.Close savechanges:=False
nextrow:
Next r
End Sub