Hello everyone,
I am trying to automate invoice process by using VBA code but at the time of using this I am facing problem of Run time error 1004 file could found at location.
I am facing problem on below code
Workbooks.Open ("D:\Contactdetails\desktopfiles\invoicesample\invoice.xlsx")
As I have checked all file name given by me and location, everything is okey but still I am facing this problem. Can anyone help me out this problem?
Thanks in advance...
Mady
Code :
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 path 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 = False
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
Bookmarks