linking Excel to Access

[ironj32]

hi everyone,
here's what i have...i am going to be receiving a couple thousand seperate workbooks over the next few months. all of the workbooks have one sheet all formated the same. i would like to set something up to put the data into my access database. i do have a table in access with all of the same corresponding fields. the thing is that the workbook/sheet does not have the info all in one row. it is all over a1, b3, a5, etc... i am looking for some suggestions on how to accomplish this.

i'm thinking of maybe having a an Access Form where i can click a button, prompt me to select a file click, click "ok" then it puts all of the data in my table. it would also be nice to have this automatically move the excel to a new folder (Completed Survey).

Thanks for your suggestions!

 

[SkipVought]

Hi,

Here's some code I created to suck workbook sheets into a table in Access

Sub AppendData2Temp()
    Dim oFSO, oFLD, oFIL, sSQL As String, x
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFLD = oFSO.GetFolder("M:\FP_REPORTS\BUILD_PLAN_INFO\Source Workbooks")
    
    For Each oFIL In oFLD.files
        
        
        If Not UCase(oFIL.Name) Like "*CONDENSED*" Then
        
            DoCmd.TransferSpreadsheet _
                acImport, _
                acSpreadsheetTypeExcel9, _
                "temp", _
                oFIL.Path, _
                True
        End If
    Next
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[ironj32]

Thanks Skip! that works, however, that put's every workbook.xls all into the same table still formatted exactly like the excel book what i want to do is to take certain cells from the workbook and make that one entry (row) in the access table.

example:
vendor1.xls cells a1, b3, b6, c7 would be row 1 in the access table

vendor2.xls cells a1, b3, b6, c7 would be row 2 in the access table

vendor3.xls cells a1, b3, b6, c7 would be row 3 in the access table

my vba skills are somewhat limited, but they are improving. thanks in advance for anyones help and guidance.

 

[SkipVought]

Please post the first 7 rows of a typical workbook, so I can see what we're working with.

Skip,
[sub]
[red][/red]
[/sub]

 

[ironj32]

is there a way to attach a file? it'll be much clearer to just attach a sample.

 

[SkipVought]

http://www.sendspace.com/login.html

Skip,
[sub]
[red][/red]
[/sub]

 

[ironj32]

A B C D
1 user name vendor name
2 $12000 12/14/07
3 renewing (123)123-4567
4 city name.name@email.com
5 state

http://www.hello.com

6 zip
7 Yes X
8 No X
9 No X
...
...
50 Additional Comments

 

[SkipVought]

Just for the record, if you can get the source data changed to a TABLE format rather than this DISPLAY format [sub]typical neophite spreadsheet user blunder![/sub], it would make life alot easier.

Sub AppendData2Temp()
    Dim oFSO, oFLD, oFIL, sSQL As String, xl As Excel.Application, wb As Workbook
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFLD = oFSO.GetFolder("M:\FP_REPORTS\BUILD_PLAN_INFO\Source Workbooks")
    
    Set xl = CreateObject("Excel.Application")
    
    For Each oFIL In oFLD.Files
        Set wb = xl.Workbooks.Open(oFIL.Path)
        With wb.ActiveSheet
        'a1, b3, b6, c7
            sName = .[A1]
            'and so on
            
            'then your append query goes here
        End With
        wb.Close
    Next
    Set wb = Nothing
    Set xl = Nothing
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[bobeckb]

ironj32,

I have the same task to accomplish. Could you share your code?

Thanks.