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: -
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)
-
Set xlsSht2 = xlsWrkBk.Worksheets(2)
-
-
'Open 1st table
-
Set rec = CurrentDb.OpenRecordset("tblXls")
-
rec.AddNew
-
rec.Fields("Test") = Nz(StrConv(xlsSht.cells(2, "F"), vbProperCase), "bad1")
-
rec.Fields("Test2") = Nz(StrConv(xlsSht.cells(2, "C"), vbProperCase), "bad2")
-
rec.Fields("Test3") = Nz(StrConv(xlsSht.cells(2, "J"), vbProperCase), "0001110000")
-
rec.Fields("Test4") = Left(xlsFile, 10)
-
rec.Update
-
-
'How do I open the second table here to continue exportind the rest of the data?
-
-
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
-
Please Help. Thanks.
1 5129
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: -
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)
-
Set xlsSht2 = xlsWrkBk.Worksheets(2)
-
-
'Open 1st table
-
Set rec = CurrentDb.OpenRecordset("tblXls")
-
rec.AddNew
-
rec.Fields("Test") = Nz(StrConv(xlsSht.cells(2, "F"), vbProperCase), "bad1")
-
rec.Fields("Test2") = Nz(StrConv(xlsSht.cells(2, "C"), vbProperCase), "bad2")
-
rec.Fields("Test3") = Nz(StrConv(xlsSht.cells(2, "J"), vbProperCase), "0001110000")
-
rec.Fields("Test4") = Left(xlsFile, 10)
-
rec.Update
-
-
'How do I open the second table here to continue exportind the rest of the data?
-
-
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
-
Please Help. Thanks.
Here's an option...insert this inside your directory loop. -
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];"
-
CurrentDb.Execute "Insert into tblXLs (Test1, Test2, Test3) Select (Test1, Test4, Test8) From tblXls1Temp;"
-
CurrentDb.Execute "Delete * from tblXLs1Temp;"
-
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];"
-
CurrentDb.Execute "Insert into tblXLs2 (Test1, Test2, Test3) Select (Test1, Test4, Test8) From tblXls2Temp;"
-
CurrentDb.Execute "Delete * from tblXLs2Temp;"
-
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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. ...
|
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...
|
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...
|
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...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |