Import Excel Sheet into Access and adding a field

[Tom72Bus]

Hi,

I've reached a little bit of a brick wall with this!

In short, I've got a load of invoice's sitting in a directory which I need to import into Access. The Invoice contains 4 columns - 'Store', 'Location', 'Claimed' and 'Receieved'. The invoice number is in the title of the spreadsheet - which is always in the same format.

I've pieced together the following code, which simply searches the directory for an excel file, imports it, moves the file, and then loops until all files are processed.

Option Compare Database
Option Explicit
Sub Impo_allExcel()
Dim myfile
Dim mypath
mypath = "J:\Finance\Invoices\"
Do
myfile = Dir(mypath & "*.xls")
DoCmd.TransferSpreadsheet acImport, 8, "Tbl_Invoices", mypath & myfile, True
myfile = Dir
Application.Run "MoveFiles"

Loop Until myfile = ""
MsgBox "All Invoices are now Imported"
End Sub

Sub MoveFiles()
Dim fso
Dim myfile
Dim sfol As String, dfol As String
sfol = "J:\Finance\Invoices\" ' source folder path
dfol = "J:\Finance\Invoices\Done" ' destination folder path
myfile = Dir(sfol & "*.xls")
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
If Not fso.FolderExists(sfol) Then
MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"
ElseIf Not fso.FolderExists(dfol) Then
MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"

This does the job! I'm sure there are more reliable ways of doing it - but it seems to work.

My problem is I would like to add in an extra field - with the Invoice number for each line of invoice data, so when I'm running future queries and reports, I've got a basis for comparison.

How should I approach this? Do I need to put the invoice number into a seperate table and then run a joining query betweent the two?

The file name format is ''com bank inv ####.xls'', or 'myfile' in the code above.

Many Thanks

Tom

 

[DCrake]

Import each xls into a temp file that contains an extra field [InvoiceNo] After the import has been completed perform an update query on the temp table to copy the invoice number to the InvoiceNo field. When completed append the contents of the temp table to the main invoices table, finally delete the contents of the temp table. Loop accordingly.

By using a temp table you can validate the incoming data before it is actually committed to the live table.

David

 

[Tom72Bus]

Import each xls into a temp file that contains an extra field [InvoiceNo] After the import has been completed perform an update query on the temp table to copy the invoice number to the InvoiceNo field. When completed append the contents of the temp table to the main invoices table, finally delete the contents of the temp table. Loop accordingly.

By using a temp table you can validate the incoming data before it is actually committed to the live table.

David

Cheers David,

I've carried on looking at this and started approaching it by adding a column in the excel file prior to the import. Is this bad practice? My vba knowledge also came a little unstuck as the excel macro commands, which i thought i could just copy and paste into the access VBA code, doesn't translate into access.

Your suggestion makes sense, but I can't work out how to physically get the file name into the update query or field via code. Apologies - my overall VBA knowledge is pretty poor!

Cheers

Tom

 

[Rabbie]

Cheers David,

I've carried on looking at this and started approaching it by adding a column in the excel file prior to the import. Is this bad practice? My vba knowledge also came a little unstuck as the excel macro commands, which i thought i could just copy and paste into the access VBA code, doesn't translate into access.
Cheers

Tom

You may find you need to add the Excel library to your references if you want to use the VBA code in Access.

In the Tools menu of the VBA editor screen you will find references. You'll see the current references indicated by a tick and then a list of potential libraries you can add. If necessary scroll down to Microsoft Excel and select that to get the Excel library.

 

[Tom72Bus]

Cheers Rabie,

That's a great help, I now have the following extra code running in Access which runs through and put's cell A1 in the 6th column in access.

Here's the code;

Sub InvoiceNumber()
    Range("F2:F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-5]>"""",R1C1,IF(RC[-4]>"""",R1C1,IF(RC[-3]>"""",R1C1,IF(RC[-2]>"""",R1C1,IF(RC[-1]>"""",R1C1,"""")))))"
    Selection.AutoFill Destination:=Range("F2:F65536"), Type:=xlFillDefault
 
End Sub
 
Sub AddInvoiceNumber()
    Dim myfile As String
    Dim mypath As String
    Dim xl
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    mypath = "J:\Finance Interfaces\HSV\"
    myfile = Dir(mypath & "*.xls")
 
    xl.workbooks.Open mypath & myfile
 
    Application.Run "InvoiceNumber"
    xl.workbooks(1).Close SaveChanges:=True
    xl.Quit
    Set xl = Nothing
End Sub

I've just selected cell A1 just as a starter so I could work out how to run it through Access, how would I now go about changing it so it shows 'myfile' (the file name), or even better just the last four numbers of 'myfile'?

Cheers

Tom