472,951 Members | 1,940 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,951 software developers and data experts.

importing excel cells to access tables

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
1 5077
JConsulting
603 Expert 512MB
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

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

Similar topics

11
by: Grim Reaper | last post by:
I am importing a .csv file into Access that has 37 fields. My problem is that sometimes the last field only has data at the end of the column (it looks like when you import a file into Access, for...
7
by: Darren | last post by:
I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the...
0
by: ImraneA | last post by:
Hi there Have a database, where front-end interface allows user to select a ms access database. From there, standard tables are linked. Routine, that creates a spreadsheet, for each table a...
8
by: harry | last post by:
Hi Folks: I'm trying to do something that looks simple, but I can't make it work right. It's Access 2000 on a Win2000 computer. I create a database with 5 columbs. The data I need to import...
2
by: Ralph | last post by:
I'm trying to import a range of cells from an Excel spreadsheet into a table in access. The first row contains column labels, but I cannot use those as my field names, both because of their format...
9
by: jillandgordon | last post by:
I am trying to import an excel file into Access 97. It looks perfectly all right but, every time I try to import it, I get to the lst step and am told that it was not imported due to an error. ...
0
by: acharyaks | last post by:
Hi life saver, I am using excel component for the development. The purpose is to connect to excel through the odbc connection string. Then through the connection extract data into a dataset and...
12
by: Ed | last post by:
Hi All, I'm looking for an efficient way of dumping a table in a Dataset into an Excel sheet. I'm currently copying data cell by cell and this is rather inefficient for large Datasets. My...
3
by: D.Stone | last post by:
I'm trying to import an Excel spreadsheet into an existing Access table using Office 2003. Ultimately, the plan is to do it programmatically using TransferSpreadsheet, but to check that the file...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.