Sleeper: Run time error 1004 file could not open

[mpatl]

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

[Dave]

This is wrong...

ActiveWorkbook.SaveAs Filename:=path & Invoice & "-" & Date & "-" & bankname & ".Xlsx

Guessing...

ActiveWorkbook.SaveAs Filename:=Application.path & "\" & Invoice &  "\"  & myDate & "\" & bankname & ".Xlsx"

HTH. Dave

[SamT]

In order to use this workbook (attachment) you must first copy the Cells of the Invoice Sheet of D:\Contactdetails\desktopfiles\invoicesample\invoice.xlsx to the InvoiceTemplate sheet of this workbook. then copy any other sheets needed into this workbook.

You must also fix the Range Assignments as indicated in the code page of the InvoiceTemplate sheet.

All the code in this book compiles, but obviously I cannot actually test it.

[mpatl]

Hello Sam,

Thanks for your reply, As I am doing simple automation invoice and path mentioned in file is for invoice file.

I have two workbook one is for basic data with Invoice information.(my file name is "invoice info" attaching for your reference). and another workbook is invoice sample (this is invoice where i want to paste respective data from "invoice info" in respective cells mentioned in VB code). But at the time to open Invoice sample file by VB code designed by me I have received error ("Run time error 1004 File could not found on specific location please check location or file name")

invoice infor.xlsminvoice sample to show.xlsx

Hope this time you will clear about my work and problem

[SamT]

Did you look at the code in my attachment?

Please upload for me a sample of Sheets("invoiceinfo")

[mpatl]

Dear Sam,

Here I am attaching the file of invoice info

invoice info.xlsm

Yes i have saw your code but as i want to maintain database with date in excel to tally the report with invoices. So its manadatory to maintain both files.

Waiting for you reply....

[westconn1]

Workbooks.Open ("D:\Contactdetails\desktopfiles\invoicesample\invoice.xlsx")

as no one can test the correctness of your file path, show an image of windows explorer open to your path

[SamT]

Mpatl,

Following your code in post # 1, I also wrote my code to use one Invoice for each invoiceinfo Row. That will not work as you have multiple Rows for each Invoice number and rows for multiple Items on the Basic Invoice.

There is no reason to use a separate workbook for the basic Invoice template and some reasons not to use the separate workbook; for example, difficulties in opening the separate workbook..

You must also consider that the completed Invoice may have multiple sheets because there are too many items to fit on one printed sheet of paper.

Per your post # 6, yes, it is obvious that all documents must be saved in a data base or folder.

The code you wrote has many errors in spelling and in logic. I feel that the entire projects needs to be re thought and re-designed. The error in opening the workbook is merely the first of many code errors that you found when you clicked the button.

I suggest that you put the words "Option Explicit" at the very top most of your code page, then on the VBA Menu "Debug," click on "Compile VBAProject." This will find many logic and spelling errors for you.

My purpose on this forum is to help you learn to automate your office. Other people here like to help you automate your office. They might join this thread. Still other people here will automate your office for hire. See: This Link. I will save all the work I have done and all your attachments for a short time.

All Offices need the same things for their Automated Invoices and we have programmed many of them. All you need to provide is a complete two page sample invoice with headers and page breaks. Print out the sample before you give it to any programmer to insure that it is in fact correct for your use. They also need to know the paths to your data base workbook and a sample Invoiceinfo workbook and all the details about how to access them.