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 4912
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
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
11 posts
views
Thread by Grim Reaper |
last post: by
|
7 posts
views
Thread by Darren |
last post: by
|
reply
views
Thread by ImraneA |
last post: by
|
8 posts
views
Thread by harry |
last post: by
|
2 posts
views
Thread by Ralph |
last post: by
|
9 posts
views
Thread by jillandgordon |
last post: by
|
reply
views
Thread by acharyaks |
last post: by
|
12 posts
views
Thread by Ed |
last post: by
|
3 posts
views
Thread by D.Stone |
last post: by
| | | | | | | | | | |