CODEWITHSUNDEEP
ms-accessvba
Hilly1
Hilly1

Reputation: 157

Access VBA Code to read a table for a file name to use in opening a spreadsheet

I have a table called FILE LOCATIONS, which has 3 fields: Supplier (the name of a vendor) Price List Location (the filename path, e.g. c:\users\Joe\downloads) Price List FileName (the filename to be imported, e.g. test.xlsx

I know very little about VBA, and am reviewing tutorials online, but have a long way to go, so thank you in advance for any assistance you can provide!

I'm trying to put some vba code behind a button that will do the following:

Look up the supplier "Jones" in the table, and return the file path and name to be used in importing a spreadsheet with the TransferSpreadsheet function

Here's some code I've been trying to develop

Dim filename As String
Dim myfile As Variant

Dlookup("[Price File Location]", "FILE LOCATIONS", "[Supplier] = Jones"           

DoCmd.TransferSpreadsheet acImport, 10, "JonesImport", filename, True, ""

Beep
MsgBox "Finished Importing Merit Price List", vbOKOnly, ""

Can you also recommend a good way to learn VBA, from the fundamentals to more advanced?

Upvotes: 0

Views: 855

Answers (1)

Kostas K.
Kostas K.

Reputation: 8508

Text fields must be enclosed in single ' or double quotes " e.g. "[Supplier] = 'Jones'"

See below:

Sub Import()

    'get concatenated file path + file name from table
    Dim filename As String
        filename = DLookup("[Price File Location] & '\' & [Price List FileName]", "FILE LOCATIONS", "[Supplier] = 'Jones'")

    'validate path
    If Len(Dir(filename)) = 0 Then
        MsgBox "File not found...", vbExclamation
        Exit Sub
    End If

    'import
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "JonesImport", filename, True '.xlsx

    MsgBox "Finished Importing Merit Price List", vbOKOnly, ""
End Sub

There are plenty of other resources on the internet nowdays from begginer to advanced level.

Good luck!

Upvotes: 1

Related Questions