Macro to Load new txt files to tables

[kt91jz]

Hi all

I am pretty new to access. I know most of the basics like creating tables writing query's etc....

I need help with a macro that I am working on.

I have a table which I need to automatically update every month with new data.

I need to overwrite the current data with my new data every month.

The new data I am putting into this table each month is in a txt file format.

So basically I want the macro to go into a folder and find the latest txt file in that folder based on the last updated date. I then want it to load this data (and overwrite) in my table.

I have done this successfully in Excel with a macro and my code is below for this.

Sub Auto_Open()
'Declare the variables
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    Dim SaveColNdx As Integer
    Dim RowNdx As Long
    Dim ColNdx As Integer
    Dim TempVal As Variant
    Dim WholeLine As String
    Dim Pos As Integer
    Dim NextPos As Integer
    Dim Sep As String
     
    'Delete last months data
    Sheets("Sheet1").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
     
    
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
Sep = vbTab
    
    'Delete last months data
    Sheets("Sheet1").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    
    'Specify the path to the folder
    MyPath = "S:\Claims Analytics Reporting\Project_Opal\UK Extracts\2.1.1\"
        
    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    'Get the first txt file from the folder
    MyFile = Dir(MyPath & "*.Txt", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    'Loop through each txt file in the folder
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)
        
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        'Get the next Txt file from the folder
        MyFile = Dir
        
    Loop
    
    'Open the latest file
    Open MyPath & LatestFile For Input Access Read As #1
While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend
    'Refresh pivots
    Sheets("Pivot Check").Select
    Range("C9").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Range("I8").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub

Any help with this would be great

Thanks

Andrew

 

[namliam]

And what is your specific question?

Docmd.transfertext will load get your text file into a table.
Then before doing so, you can delete the table or delete the table's contents without much problems.....

 

[kt91jz]

Hi nailman

My question is what is the code I need for the macro.

I know I could just update the table manually and it only takes a second but I have 15 tables to update.

Also something to note is that the txt file name that I wish to add in every month will be different as it refers to months.

So this months txt file will be Jan-15.txt and next months will be Feb-15.txt

I want the macro to be smart enough to be able to identify which is the most recently updated txt file in the folder and then load this into my table overwriting the current data in the process.

 

[namliam]

Well you have your starting point of the excel code, I gave you Docmd.transfertext to read the text files into a table... Should be a good starting point ?

 

[kt91jz]

For some reason it is not letting me see your post... it is saying because im a new user and don't have ten posts that I cannot see your link or post.

 

[namliam]

Yet you seem to be able to reply to my post?

 

[kt91jz]

Yes I can see the first line of your replies and then I get this error...


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam A