>>> Excel import Macro <<<

[Raedor]

Hi,

I know I'm in the wrong forum and posting a wrong question, but really I cannot find an Excel equivalent forum online and I can see that this is where Access queries are resolved and I can also see other Access/Excel related posts.

So can some one please help a newbie out with this as I really do think its quite an easy solution for most programmers.

I receive about 20 txt files daily and need a simple import macro to be written that will allow this data to be appended into my spreadsheet as and when I click a button on the sheet.
The text file will always have a header of the field names and then just have 1 new record to be appended.
Ideally I will format my spreadsheet to reflect more meaningfull names than the field headers and not to have these overwritten everytime data is appended.

I do appreciate that what I'm asking really I should learn myself, but without that 1st time help its, too me, like looking up Mt Everest

Thanks in anticipation
HELP

 

[PaulJR]

Excel forum can be found under: Access World Forums > Apps and Windows.

However, have you tried recording a macro in Excel to import the file and append it to your data? You can do a real lot by with macros without being a programmer.

I'd suggest you make a copy of your file, and experiment with recording macros until it does roughly what you want it to do, and then post more specific questions here should you run into difficulties.

 

[mikebaldam]

Here is the code i use to import an Excel file directly to a table

Dim msgret As String
Dim strDir As String
msgret = MsgBox("Are you sure you want to import a new list?", vbYesNo)

If msgret = "6" Then
    strDir = CurrentProject.Path & "\Data\" 'or amend to your excel filelocation ie strDir= "c:\myfile.xls"
    DoCmd.SetWarnings False

     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "TABLENAMEHERE", strDir & "filename.xls", -1

    DoCmd.SetWarnings True
    MsgBox "File Imported, Please check the details", vbInformation
    Me.Refresh

Else
    Exit Sub
End If

Exit Sub

I hope this makes sense. (Im being lazy)