How to transfer info from Excel to Access, Need Help. 1

[PorscheGT2]

Hello,

I'm trying to copy data from Excel to Access using the Template Wizard. This process works although it involves using the template and saving it. What I would like to do is to transfer directly from one of the Excel tool spreadsheets we're using to Access without needing the template. Is that possible with a VBA macro? The tool we're using is saved in .xls, and I don't know if it's possible to copy/incorporate the template properties for data tracking into a regular Excel spreadsheet. Any help would be appreciated.

 

[SkipVought]

PorscheGT2,

This is VBA code in Access. You could adapt this code to run from Excel.

Sub AppendToTable()
    Dim fs, f, fl, fc, xl
    Set xl = CreateObject("Excel.application")
    DoCmd.SetWarnings False
    fn = xl.GetOpenFilename
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(ParsePath(fn))
    Set fc = f.Files
    DoCmd.RunSQL "DROP TABLE AggregatedOrders"
    DoCmd.RunSQL "Create Table AggregatedOrders (" & _
        "Col Integer, Row Integer, Product Text (5), Model Text (9), " & _
        "Contract Text (9), SerialNbr Text (5), Material Text (18), " & _
        "EM Text (2), Description Text (32), ReqmtQty Single,  ReqmtDate Date, " & _
        "OrdQty Single, OrdType Text (10), OrdNbr Text (13), MatlType Text (5), " & _
        "MatlGrp Text (4), SchStartRel Date, SchFinDel Date, " & _
        "ActStartRel Date, Plant Text (3), Level Integer, PctComp Single, " & _
        "SeqNbr Integer, OrdType1 Text (10), OrdQty1 Single) "
    For Each fl In fc
         DoCmd.TransferSpreadsheet _
            acImport, acSpreadsheetTypeExcel9, "AggregatedOrders", fl, True
    Next
    DoCmd.SetWarnings True
    Set xl = Nothing
End Sub
Function ParsePath(fn)
    Dim i, s
    For i = Len(fn) To 1 Step -1
        s = Mid(fn, i, 1)
        Select Case s
        Case "\"
            ParsePath = Left(fn, i - 1)
            Exit Function
        End Select
    Next
End Function

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884