Importing excel cells to more than 1 access tables 2

[thadson]

Hi,

I'm trying to import specific cells from about 500 MS Excel 2000 spreadsheets into MS Access 2000 tables then move the spreadsheets to a different directory.
I'm very new to this and I'm having trouble to implement this.

I have worked out so far the code to import certain cells into 1 table and then move the spreadsheet, but I do not know how to import some other cells into another tables so the data would be connected and remain together.

So lets say that I have 2 tables named Table1 and Table2 in an Access 2000 Database and I want to import cells B1, B3 and D3 from excel spreadsheets into the fields Data1, Data2 and Data3 in the table Table1, and then I want to import cell List2!F1, F3 and H3 into the fields Data11, Data12, and Data13 into the Table2 table. How do I do this?

Here is the code I have so far for only one table:

Private Sub xlsAdd_Click()
 
Dim rec As DAO.Recordset
Dim xls As Object
Dim xlsSht As Object
Dim xlsSht2 As Object
Dim xlsWrkBk As Object
Dim xlsPath As String
Dim xlsPath2 As String
Dim xlsFile As String
Dim fullXlsFile As String
Dim fullFile As String
Dim fullFile2 As String
 
Dim Msg, Style, title, Response
  Msg = "Importing is Done, Files are imported!"    ' Define message.
  Style = vbOKOnly
  title = "Import Mesage"
 
    xlsPath = "C:\Xls\"    ' Set the xls path for new files.
    xlsPath2 = "C:\Xls\done\"    ' Set the 2nd xls path to store imported files.
    xlsFile = Dir(xlsPath & "*.xls", vbNormal)     ' Retrieve the first entry.
    
    Do While xlsFile <> ""    ' Start the loop.
        ' Ignore the current directory and the encompassing directory.
        fullXlsFile = xlsPath & xlsFile
        fullFile = xlsPath & xlsFile
        fullFile2 = xlsPath2 & xlsFile
        If Right(fullXlsFile, 4) = ".xls" Then 'import it
        DoCmd.SetWarnings False
        Set xls = CreateObject("Excel.Application")
        Set xlsWrkBk = GetObject(fullXlsFile)
        Set xlsSht = xlsWrkBk.Worksheets(1) 'worksheet List1 of TblXls1.xls
        Set xlsSht2 = xlsWrkBk.Worksheets(2) 'worksheet List2 of TblXls1.xls
                
        'Open 1st table
        Set rec = CurrentDb.OpenRecordset("Table1")
        rec.AddNew
        rec.Fields("Data1") = Nz(StrConv(xlsSht.cells(1, "B"), vbProperCase), "bad1")
        rec.Fields("Data2") = Nz(StrConv(xlsSht.cells(3, "B"), vbProperCase), "bad2")
        rec.Fields("Data4") = Nz(StrConv(xlsSht2.cells(1, "B"), vbProperCase), "0001110000")
        'rec.Fields("Data25") = Left(xlsFile, 10) 'first 10 charactes of filename
        rec.Update
        
        'How do I open the second and third tables here to continue exporting the rest of the data?
        'example: cell F3 of worksheet List1 of TblXls1.xls should import to Table2
        'example2: cell F2 of worksheet List2 of TblXls1.xls should import to Table3
   
        
        DoCmd.SetWarnings True
        End If
        
        'Closing excel
        xlsWrkBk.Application.Quit
    
    'Moving the imported Excel file
    Name fullFile As fullFile2
    xlsFile = Dir()
        
    Loop
Response = MsgBox(Msg, Style, title)
End Sub

I can email sample database and spreadsheet on request. It is a small 23kb file.

Thanks

 

[SkipVought]

Hi,

        'Open 1st table
        Set rec = CurrentDb.OpenRecordset("Table1")
        rec.AddNew
        rec.Fields("Data1") = Nz(StrConv(xlsSht.Cells(1, "B"), vbProperCase), "bad1")
        rec.Fields("Data2") = Nz(StrConv(xlsSht.Cells(3, "B"), vbProperCase), "bad2")
        rec.Fields("Data4") = Nz(StrConv(xlsSht2.Cells(1, "B"), vbProperCase), "0001110000")
        'rec.Fields("Data25") = Left(xlsFile, 10) 'first 10 charactes of filename
        rec.Update
        
        'How do I open the second and third tables here to continue exporting the rest of the data?
        'example: cell F3 of worksheet List1 of TblXls1.xls should import to Table2
        'example2: cell F2 of worksheet List2 of TblXls1.xls should import to Table3
        
        rec.Close
        
        Set rec = CurrentDb.OpenRecordset("Table2")
        rec.AddNew
        rec.Fields("Data1") = Nz(StrConv(xlsSht.Cells(1, "B"), vbProperCase), "bad1")
        rec.Fields("Data2") = Nz(StrConv(xlsSht.Cells(3, "B"), vbProperCase), "bad2")
        rec.Fields("Data4") = Nz(StrConv(xlsSht2.Cells(1, "B"), vbProperCase), "0001110000")
        'rec.Fields("Data25") = Left(xlsFile, 10) 'first 10 charactes of filename
        rec.Update
        
        rec.Close
        
        Set rec = CurrentDb.OpenRecordset("Table3")
        rec.AddNew
        rec.Fields("Data1") = Nz(StrConv(xlsSht.Cells(1, "B"), vbProperCase), "bad1")
        rec.Fields("Data2") = Nz(StrConv(xlsSht.Cells(3, "B"), vbProperCase), "bad2")
        rec.Fields("Data4") = Nz(StrConv(xlsSht2.Cells(1, "B"), vbProperCase), "0001110000")
        'rec.Fields("Data25") = Left(xlsFile, 10) 'first 10 charactes of filename
        rec.Update
        
        rec.Close

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[thadson]

Thanks for the answer SkipVought. Will this code make sure that the data from the spreadsheet will remain together in the database. What I mean is that for example -from the above example - let say that Table1 would contain first middle last names of a client. Then table 2 is address city state zip for same client and table 3 is work information for same client. With this code you gave me, will the database recognise that this info in the 3 tables belong together?

 

[SkipVought]

Not unless you establish a relationship. Exactly how is table 1 related to table 2 and table 3? There has to be a key to relate them in some way.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[thadson]

Yes, every table has a ClientID field. When the client's data gets imported from the spreadsheet, a new clientid has to be created and then the tables get related by that ClientID.

 

[SkipVought]

Then for a specified ClientID, the data in Table 1, Table 2 and Table 3 is related.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}