Data Link

Andy - Jan 27, 2016 at 05:46 PM
 Blocked Profile - Jan 29, 2016 at 04:59 PM

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,

2 responses

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Jan 29, 2016 at 06:49 AM
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:-

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

Application.ScreenUpdating = True

End Sub
Sub NextInvoice()

Application.ScreenUpdating = False

Dim myshape As Shape

    Range("J6").Value = Range("J6").Value + 1
    For Each myshape In ActiveSheet.Shapes
    myshape.Visible = True

Application.ScreenUpdating = True

End Sub
 Sub SaveInvoiceAsPDF()
 Application.ScreenUpdating = False
     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
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


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

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!

Thank you!

I will try this out

I truly appreciate the feedback
Blocked Profile
Jan 29, 2016 at 04:59 PM
Use a custom report in Access!