Data LinkClosed

Question

Hello, 

I'm needing help please,

I'm trying to figure out how to either:

Link an Access database to an Excel Spreadsheet where it has a template for a Commercial Invoice form. 

Or, if an excel workbook had the database on one page and my commercial invoice template is on a separate page that's linked to it. 

We are have to constantly create commercial invoices manually for every order placed going outside the US. We're manually filling in the following data for each item: 
Part#, Description, WeightKG, COO, Type, HTS Code

What I'm ultimately wanting it to do is have the excel spreadsheet be a template for a commercial invoice, and if we can just type the specified "keyed" item which in this case we want to be the part#  (which is an 8 digit #) for said item, and have all the info mentioned above populate the remaining fields such as: description, WeightKG, COO, Type, HTS Code.

this would save us a lot of time!

any info and help would be greatly appreciated.

thank you,

vcoolio · Answer

Hello Andy,

You can have one Excel work book with the invoice template in one sheet and the parts data base in another sheet.

Have a look at the following link to my test work book. As an example, I've rattled up a template more or less based on what you have explained, not knowing what your work book actually looks like:-

https://www.dropbox.com/s/jdmzhliokxpnr2k/Andy%27s%20Invoice%20Template.xlsm?dl=0

The following codes should do the task for you and are assigned to the buttons in the test work book:-



Sub HideShapes()

Application.ScreenUpdating = False

Dim myshape As Shape

For Each myshape In ActiveSheet.Shapes
    myshape.Visible = False
Next

Application.ScreenUpdating = True

End Sub
Sub NextInvoice()

Application.ScreenUpdating = False

Dim myshape As Shape

Range("J6").Value = Range("J6").Value + 1
    Range("B6:H9").ClearContents
    Range("A12:H48").ClearContents
    Range("I11:J48").ClearContents
    
    For Each myshape In ActiveSheet.Shapes
    myshape.Visible = True
Next

Application.ScreenUpdating = True

End Sub
 Sub SaveInvoiceAsPDF()
 
 Application.ScreenUpdating = False
 
     HideShapes
     
     NewFN = "C:\Your FilePath Here\" & Range("B6").Value & ".pdf"   '---->Range("B6") is the Client name.
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
     Quality:=xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=False
     NextInvoice
     
Application.ScreenUpdating = True

End Sub
 
Sub CopyData()

Application.ScreenUpdating = False

Dim PartNum As String
        Dim lr As Long
        
PartNum = Sheet1.Range("J8").Value
lr = Range("A" & Rows.Count).End(xlUp).Row

Sheet2.Select

For Each cell In Range("A1:A" & lr)
        If cell.Value = PartNum Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "H")).Copy
        Sheet1.Range("A48").End(3)(2).PasteSpecial xlPasteValues
    End If
Next

Sheet1.Select
Sheet1.Range("J8") = ""
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The "Fill Invoice" button is self explanatory. The code assigned to this button copies the part details from the data base in sheet2 via a drop down validation list in sheet1, cell J8 which lists the part numbers. Select a part number and click on the "Fill Invoice" button to extract the part number data.

The "Save and Clear" button saves the invoice to your selected file as a PDF. You will need to enter your required file path in the code (line 38 in the above code). It then clears the invoice and increments the invoice number ready for your next invoice. There are some explanatory notes in the test work book.

I hope that this at least gets you underway and hopefully in the right direction!

Cheerio,
vcoolio.

ac3mark · Answer

Use a custom report in Access!

Data Link

2 responses

Similar discussions

Subscribe To Our Newsletter!