Results 1 to 11 of 11

Invoice Automation Results in 1004 Runtime Error

Threaded View

eknight74 Invoice Automation Results in... 06-26-2018, 09:55 AM
than_gold Re: VBA Code Not Working... 06-26-2018, 10:05 AM
eknight74 Re: VBA Code Not Working... 06-26-2018, 10:15 AM
than_gold Re: VBA Code Not Working... 06-26-2018, 10:36 AM
eknight74 Re: VBA Code Not Working... 06-26-2018, 11:18 AM
eknight74 Re: VBA Code Not Working... 06-26-2018, 11:30 AM
than_gold Re: VBA Code Not Working... 06-26-2018, 11:41 AM
eknight74 Re: VBA Code Not Working... 06-26-2018, 11:54 AM
Arkadi Re: VBA Code Not Working... 06-26-2018, 11:45 AM
eknight74 Re: VBA Code Not Working... 06-26-2018, 11:52 AM
Arkadi Re: Invoice Automation... 06-26-2018, 12:04 PM
  1. #1
    Registered User
    Join Date
    06-26-2018
    Location
    Greenville
    MS-Off Ver
    2016
    Posts
    6

    Invoice Automation Results in 1004 Runtime Error

    I have the code below as part of a spreadsheet that automatically generates and invoice. It has worked fine until today. It place the word "done" in the completed column when executed. This allows for only one invoice at a time to be generated.

    When I used it today, the code is not recognizing the word "done" and continues to try to generate and invoice for a line in the spreadsheet that has no data entered.

    Code is below. I am using Windows 10 Microsoft Office 2016.
    Excel workbook containing the worksheet and the invoice template file are all contained in
    H:\Documents\General Files\Drill\Lime Spreader Invoices\2018

    Any assistance appreciated. I cannot determine what has caused the code to start working improperly.

    Option Explicit
    
    Function LastRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
        With ws
            LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
        End With
    End Function
    
    Private Sub CommandButton1_Click()
    
        Const path$ = "H:\Documents\General Files\Drill\Lime Spreader Invoices\2018\"
    
        Dim name As String, address As String, invoicenumber As Long
        Dim r As Long, mydate As String, myfilename As String, wbInv As Workbook
        Dim endmeterread, beginmeterread, zip, city, pickupdate, returndate, numberofdays, firstdayrate
        Dim tempWB As Workbook
        Dim wsCD As Worksheet
    
        Set wsCD = ThisWorkbook.Worksheets("RentalDetails")
    
        For r = 5 To LastRow(wsCD)
    
            If Not Cells(r, 18).Value = "done" Then
    
                With wsCD
    
                    name = .Cells(r, 4).Value
                    address = .Cells(r, 15).Value
                    invoicenumber = .Cells(r, 1).Value
                    endmeterread = .Cells(r, 6).Value
     beginmeterread = .Cells(r, 5).Value
                    zip = .Cells(r, 17).Value
                    city = .Cells(r, 16).Value
         pickupdate = .Cells(r, 2).Value
         returndate = .Cells(r, 3).Value
         numberofdays = .Cells(r, 14).Value
         firstdayrate = .Cells(r, 8).Value
                    .Cells(r, 18).Value = "done"
                End With
    
                Application.DisplayAlerts = False
                Set wbInv = Workbooks.Open("H:\Documents\General Files\Drill\Lime Spreader Invoices\2018\Invoice.xlsx")
    
                With wbInv.Worksheets("Invoice")
                    .Range("G11").Value = invoicenumber
                    .Range("A14").Value = name
                    .Range("A15").Value = address
                    .Range("A16").Value = city
                    .Range("D16").Value = zip
                    .Range("E19").Value = endmeterread
                    .Range("E20").Value = beginmeterread
                    .Range("G8").Value = pickupdate
                    .Range("I8").Value = returndate
                    .Range("E25").Value = numberofdays
                    .Range("E27").Value = firstdayrate
                
                End With
    
               mydate = Format(Date, "mm_dd_yyyy")
                wbInv.SaveAs Filename:=path & invoicenumber & " - " & name & _
                        " - " & mydate & ".xlsx"
                myfilename = ActiveWorkbook.FullName
                SetAttr myfilename, vbReadOnly
                Application.DisplayAlerts = True
                'ActiveWorkbook.PrintOut copies:=1
                wbInv.Close SaveChanges:=False
                Set wbInv = Nothing
                Set tempWB = Nothing
    
            End If
    
        Next r
    
    End Sub
    Last edited by eknight74; 06-26-2018 at 11:51 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 08-07-2015, 04:43 AM
  2. Workbook.Application.Run from OLE Automation not working for XLSB file
    By bitpusher10101010 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-20-2014, 08:46 PM
  3. Replies: 4
    Last Post: 11-04-2012, 12:43 PM
  4. Need help in Invoice automation
    By Logu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2012, 05:12 AM
  5. Excel to Web Automation (Submit button Not Working)
    By plasma33 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2012, 09:39 AM
  6. Degresive depreciation automation code not working!
    By Alex Cociorva in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2011, 08:53 AM
  7. DisplayAlerts Not Working with Automation
    By msherb in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2005, 09:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1