By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,218 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,218 IT Pros & Developers. It's quick & easy.

importing excel cells to access tables

P: 1
Hi,

I'm trying to import specific cells from MS Excel 2000 spreadsheets to 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, 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 tblXls and tblXls2 in and Access 2000 Database and I want to import cells F2, C2 and J2 from excel spreadsheets into the fields Test, Test2 and Test3 in the table tblXls, and then I want to import cell F3, C3 and J3 into the fields Name, Phone, and Address in the tblXls2 table. How do I do this?

Here is the code I have so far for the 1 table:

Expand|Select|Wrap|Line Numbers
  1. Private Sub xlsAdd_Click()
  2.  
  3. Dim rec As DAO.Recordset
  4. Dim xls As Object
  5. Dim xlsSht As Object
  6. Dim xlsSht2 As Object
  7. Dim xlsWrkBk As Object
  8. Dim xlsPath As String
  9. Dim xlsPath2 As String
  10. Dim xlsFile As String
  11. Dim fullXlsFile As String
  12. Dim fullFile As String
  13. Dim fullFile2 As String
  14.  
  15. Dim Msg, Style, title, Response
  16.   Msg = "Importing is Done, Files are imported!"    ' Define message.
  17.   Style = vbOKOnly
  18.   title = "Import Mesage"
  19.  
  20.     xlsPath = "C:\Xls\"    ' Set the xls path for new files.
  21.     xlsPath2 = "C:\Xls\done\"    ' Set the 2nd xls path to store imported files.
  22.     xlsFile = Dir(xlsPath & "*.xls", vbNormal)     ' Retrieve the first entry.
  23.  
  24.     Do While xlsFile <> ""    ' Start the loop.
  25.         ' Ignore the current directory and the encompassing directory.
  26.         fullXlsFile = xlsPath & xlsFile
  27.         fullFile = xlsPath & xlsFile
  28.         fullFile2 = xlsPath2 & xlsFile
  29.         If Right(fullXlsFile, 4) = ".xls" Then 'import it
  30.         DoCmd.SetWarnings False
  31.         Set xls = CreateObject("Excel.Application")
  32.         Set xlsWrkBk = GetObject(fullXlsFile)
  33.         Set xlsSht = xlsWrkBk.Worksheets(1)
  34.         Set xlsSht2 = xlsWrkBk.Worksheets(2)
  35.  
  36.         'Open 1st table
  37.         Set rec = CurrentDb.OpenRecordset("tblXls")
  38.         rec.AddNew
  39.         rec.Fields("Test") = Nz(StrConv(xlsSht.cells(2, "F"), vbProperCase), "bad1")
  40.         rec.Fields("Test2") = Nz(StrConv(xlsSht.cells(2, "C"), vbProperCase), "bad2")
  41.         rec.Fields("Test3") = Nz(StrConv(xlsSht.cells(2, "J"), vbProperCase), "0001110000")
  42.         rec.Fields("Test4") = Left(xlsFile, 10)
  43.         rec.Update
  44.  
  45.         'How do I open the second table here to continue exportind the rest of the data?
  46.  
  47.         DoCmd.SetWarnings True
  48.         End If
  49.  
  50.         'Closing excel
  51.         xlsWrkBk.Application.Quit
  52.  
  53.     'Moving the imported Excel file
  54.     Name fullFile As fullFile2
  55.     xlsFile = Dir()
  56.  
  57.     Loop
  58. Response = MsgBox(Msg, Style, title)
  59. End Sub
  60.  
Please Help. Thanks.
Sep 2 '07 #1
Share this Question
Share on Google+
1 Reply


JConsulting
Expert 100+
P: 603
Hi,

I'm trying to import specific cells from MS Excel 2000 spreadsheets to 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, 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 tblXls and tblXls2 in and Access 2000 Database and I want to import cells F2, C2 and J2 from excel spreadsheets into the fields Test, Test2 and Test3 in the table tblXls, and then I want to import cell F3, C3 and J3 into the fields Name, Phone, and Address in the tblXls2 table. How do I do this?

Here is the code I have so far for the 1 table:

Expand|Select|Wrap|Line Numbers
  1. Private Sub xlsAdd_Click()
  2.  
  3. Dim rec As DAO.Recordset
  4. Dim xls As Object
  5. Dim xlsSht As Object
  6. Dim xlsSht2 As Object
  7. Dim xlsWrkBk As Object
  8. Dim xlsPath As String
  9. Dim xlsPath2 As String
  10. Dim xlsFile As String
  11. Dim fullXlsFile As String
  12. Dim fullFile As String
  13. Dim fullFile2 As String
  14.  
  15. Dim Msg, Style, title, Response
  16.   Msg = "Importing is Done, Files are imported!"    ' Define message.
  17.   Style = vbOKOnly
  18.   title = "Import Mesage"
  19.  
  20.     xlsPath = "C:\Xls\"    ' Set the xls path for new files.
  21.     xlsPath2 = "C:\Xls\done\"    ' Set the 2nd xls path to store imported files.
  22.     xlsFile = Dir(xlsPath & "*.xls", vbNormal)     ' Retrieve the first entry.
  23.  
  24.     Do While xlsFile <> ""    ' Start the loop.
  25.         ' Ignore the current directory and the encompassing directory.
  26.         fullXlsFile = xlsPath & xlsFile
  27.         fullFile = xlsPath & xlsFile
  28.         fullFile2 = xlsPath2 & xlsFile
  29.         If Right(fullXlsFile, 4) = ".xls" Then 'import it
  30.         DoCmd.SetWarnings False
  31.         Set xls = CreateObject("Excel.Application")
  32.         Set xlsWrkBk = GetObject(fullXlsFile)
  33.         Set xlsSht = xlsWrkBk.Worksheets(1)
  34.         Set xlsSht2 = xlsWrkBk.Worksheets(2)
  35.  
  36.         'Open 1st table
  37.         Set rec = CurrentDb.OpenRecordset("tblXls")
  38.         rec.AddNew
  39.         rec.Fields("Test") = Nz(StrConv(xlsSht.cells(2, "F"), vbProperCase), "bad1")
  40.         rec.Fields("Test2") = Nz(StrConv(xlsSht.cells(2, "C"), vbProperCase), "bad2")
  41.         rec.Fields("Test3") = Nz(StrConv(xlsSht.cells(2, "J"), vbProperCase), "0001110000")
  42.         rec.Fields("Test4") = Left(xlsFile, 10)
  43.         rec.Update
  44.  
  45.         'How do I open the second table here to continue exportind the rest of the data?
  46.  
  47.         DoCmd.SetWarnings True
  48.         End If
  49.  
  50.         'Closing excel
  51.         xlsWrkBk.Application.Quit
  52.  
  53.     'Moving the imported Excel file
  54.     Name fullFile As fullFile2
  55.     xlsFile = Dir()
  56.  
  57.     Loop
  58. Response = MsgBox(Msg, Style, title)
  59. End Sub
  60.  
Please Help. Thanks.

Here's an option...insert this inside your directory loop.

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "Insert into tblXLs1Temp (Test1, Test2, Test3, Test4, Test5, Test6, Test7, Test8, Test3) * FROM [EXCEL 8.0;HDR=No;IMEX=2;DATABASE= '" & fullXlsFile & "'].[Sheet1$C2:J2];"
  2. CurrentDb.Execute "Insert into tblXLs (Test1, Test2, Test3) Select (Test1, Test4, Test8) From tblXls1Temp;"
  3. CurrentDb.Execute "Delete * from tblXLs1Temp;"
  4. CurrentDb.Execute "Insert into tblXLs2Temp (Test1, Test2, Test3, Test4, Test5, Test6, Test7, Test8, Test3) * FROM [EXCEL 8.0;HDR=No;IMEX=2;DATABASE= '" & fullXlsFile & "'].[Sheet1$C3:J3];"
  5. CurrentDb.Execute "Insert into tblXLs2 (Test1, Test2, Test3) Select (Test1, Test4, Test8) From tblXls2Temp;"
  6. CurrentDb.Execute "Delete * from tblXLs2Temp;"
  7.  
You'll need a temp table for each of your final tables.

NOTE: I'm looking into a way to provide the exact cells vs a range, but I've not had much luck.
J
Sep 3 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.