Appending multiple tables

[Guinaba]

Hi guys,
I have the following situation:
1) Need to extract all the .xlsx files from a folder
2) Append all of them in one table
3) After having all the data in this table, need to start appending only the most recent file

I do have the vba code to get the most recent file and the other vba module to get all files, however not sure what the best approach to combine all of them in one table in access. If I use append query do I have to append one by one? Hopefully not

VBA code to extract the most recent .xlsx file

Option Explicit

Option Compare Database

'==========================================

' Import Data From Excel With DoCmd

'==========================================



Sub importDataFromExcelDoCmd()

' Set variables

Dim strTableName As String

Dim strFileName As String

Dim VarFileName As String

Dim blnHasHeadings As Boolean

Dim MyPath As String

Dim MyFile As String

Dim LatestFile As String

Dim LatestDate As Date

Dim LMD As Date



'********************Getting the latest file from the folder**************************************



MyPath = "Z:\Supply Chain\Demand Planning\Coles\SY35 Total Manassen\"

If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

MyFile = Dir(MyPath & "*.xlsx", vbNormal)

If Len(MyFile) = 0 Then

MsgBox "No files were found...", vbExclamation

Exit Sub

End If



Do While Len(MyFile) > 0

LMD = FileDateTime(MyPath & MyFile)

If LMD > LatestDate Then

LatestFile = MyFile

LatestDate = LMD

End If

MyFile = Dir

Loop

VarFileName = (MyPath & LatestFile) 'Latest file



'*****************Setting the data in Access*************************



' Set data

strTableName = "tbl0ColesPromoData"

strFileName = VarFileName

blnHasHeadings = True

' Import data

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, strFileName, blnHasHeadings



End Sub

VBA code to extract all the .xlsx files

[/B]
Sub ImportfromPath(path As String, intoTable As String, hasHeader As Boolean)



Dim fileName As String



'Loop through the folder & import each file

fileName = Dir(path & "\*.xlsx")

While fileName <> ""

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, intoTable, path & fileName, hasHeader

'check whether there are any more files to import

fileName = Dir()

Wend



 End Sub
[B]

 

[alansidman]

Here is an alternative means using Power Query

How to Combine Tables with Power Query - Excel Campus

Use Power Query to combine similar tables together and append new ones. Easily change or delete the query as tables change

www.excelcampus.com

 

[Guinaba]

Here is an alternative means using Power Query

How to Combine Tables with Power Query - Excel Campus

Use Power Query to combine similar tables together and append new ones. Easily change or delete the query as tables change

www.excelcampus.com

Thanks @alansidman, but this is the way I do it now, however excel is not a database, depending how much data you have it is a nightmare to keep the data in sheets.

 

[Micron]

Not sure I follow this one. Haven't used transferspreadsheet for a looonngg time, but IIRC it just appends to a table, so why would you need any append query? Please advise if the transfer method doesn't append but over-writes instead.

 

[Guinaba]

Not sure I follow this one. Haven't used transferspreadsheet for a looonngg time, but IIRC it just appends to a table, so why would you need any append query? Please advise if the transfer method doesn't append but over-writes instead.

Hi @Micron,

I'd like to give a go with IIRC appending to a table, do you have any examples, to ilustrate that?

 

[Micron]

You realize that IIRC is short for "If I Recall Correctly"? If so, then I'm not sure what you're asking for.
It's like
- LOL, which I probably don't have to explain
- AFAIC as far as I'm concerned
- AFAIK as far as I know

 

[Guinaba]

Hehe...My bad ...Thanks @Micron