Importing an Excel file as a table n VBA

[JungleJme]

SOLVED Importing an Excel file as a table n VBA

Hi,

I'm a VBA novice and am really struggling with this.... :banghead:


Anyway. This is what i am trying to achieve. I've managed the first three steps in Excel, but have just found out Access has no macro recorder so i'm now completely stumped. The whole process should be:


Allow the user to browse to a .txt file.
That .txt file is opened in excel -
Excel runs text to columns to sort the data.
Access then imports the data as a table
As this will be run more than once, the previous table must be deleted or completed overwritten during the process.

Seperately i can then create an update and an append query to update the database.

Please help! Thanks in advance for your time.

In excel the code to open the text file and sort it to columns is:

Sub ImportTextFile()
On Error GoTo ErrTrap
Application.ScreenUpdating = False
 
 
' Below allows user to open a txt file.
 
Dim TxtFileName As String
TxtFileName = GetTextFile("C:\Users\7092\Desktop\")
Workbooks.OpenText Filename:=TxtFileName, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), DecimalSeparator:=".", ThousandsSeparator:=",", TrailingMinusNumbers:=True
If Len(TxtFileName) Then
    If Right$(TxtFileName, 4) <> ".txt" Then
        MsgBox "You must select a text file!"
        Exit Sub
    End If
Else
    Exit Sub
End If
 
'below sorts data from the .txt file into columns
 
Sheets("sqlexec").Select
    ActiveSheet.Columns("A:A").Select
    Selection.Texttocolumns Destination:=Range("A:A"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(7, 1), Array(9, 1), Array(37, 1), Array(65, 1), _
        Array(75, 1), Array(78, 1), Array(81, 1), Array(84, 1), Array(124, 1), Array(164, 1), Array _
        (204, 1), Array(234, 1), Array(244, 1), Array(260, 1), Array(269, 1)), _
        TrailingMinusNumbers:=True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("N:N").Select
    Selection.NumberFormat = "@"
    Columns("P:P").Select
    Selection.Texttocolumns Destination:=Range("P1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(8, 1)), Trailing
 
 
'function to retrieve file
Function GetTextFile(sPath) As String
    ChDir sPath
    GetTextFile = Application.GetOpenFilename( _
        FileFilter:="Text Files (*.txt), *.txt,", _
        FilterIndex:=1, _
        Title:="Select A Text File")
End Function

 

[Flank]

I do this quite a bit. In office 2007, I use the macro command, transfer spreadsheet under macro's.

I also set the upload to a temp table. So the Macro is something like:

SetWarnings: No
OpenQuery: CleanTemp (which runs a delete query to wipe the temp table)
TransferSpreadsheet: (Enter Parameters, Range Example Sheet1!A1:B2)
OpenQuery: Append Temp table to Main table
OpenQuery: Update Temp table to Main table
OpenQuery: CleanTemp
SetWarnings:Yes

I use the setwarnings to stop Access from showing the message that you are about to Append or Update tables.

Hope that helps.

 

[boblarson]

First off,

You don't need to open it in Excel to sort it. You need to use a QUERY with an ORDER BY on it when you need to use the table. The think you need to understand is that Access does not store the data in the table in any meaningful way (when the database has compact and repair done it will order them by the primary key but that doesn't stay that way as records are added/deleted).

So, just because it appears to remain sorted for you is not how it really works and you can find it not in the order you think it should be in one day and then you would have been wondering what happened. So, you just need to use the TransferText command (sort of like what Flank showed) but TransferText will import a text file. Don't even mess with trying to sort it in Excel first. And you can set an import specification (see here for how) to be able to set some of the details before importing.

 

[JungleJme]

Thanks guys - you were both right. I was massively over complicating things! Got the update sorted now so thank you both