Humm, well, I don't think I'd do it like that. I'd keep the Tables pretty
simple, and do any complex relationships in the Queries. Maybe I missed
something, but I don't know why you have to align data. You have the PKs,
and that's great. Do the aligning on a Query, and set up those ComboBoxes in
a Form.
I'd just set up a basic Table, apply the appropriate Data Type, then build
your Queries off of the Table and build your Form off of the Query. Well,
that's just my two cents.
Here is one more import macro. This will import all of the sheets from each
Excel file, in one folder, into separate Tables.
Function ImportExcelFiles()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim lngCounter As Long
blnHasFieldNames = True
'Name the generic table
strTable = "Table"
lngCounter = 0
strPath = "C:\Import\"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
lngCounter = lngCounter + 1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable & lngCounter, strPathFile, blnHasFieldNames
strFile = Dir()
Loop
MsgBox "Done with Import"
End Function
This macro will import one Excel file, with several sheets, into separate
Access Tables (it came form Ken's site):
Sub ImportAllSheetsIntoMultipleTables()
'Import Data from All Worksheets in a single EXCEL File into Separate Tables
via TransferSpreadsheet
'Generic code to import the data from all worksheets in a single EXCEL file.
Each worksheet's data will be imported into a separate table whose name is
'tbl' plus the worksheet name (e.g., "tblSheet1").
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPathFile As String
Dim strPassword As String
' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
blnHasFieldNames = False
' Replace C:\Filename.xls with the actual path and filename
strPathFile = "C:\Import\MyWorkbook.xls"
' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = "passwordtext"
blnReadOnly = True
' Open the EXCEL file and read the worksheet names into a collection
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount
' Close the EXCEL file without saving the file, and clean up the EXCEL objects
objWorkbook.Close False
Set objWorkbook = Nothing
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing
' Import the data from each worksheet into a separate table
For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl" & colWorksheets(lngCount), strPathFile, blnHasFieldNames, _
colWorksheets(lngCount) & "$"
Next lngCount
' Delete the collection
Set colWorksheets = Nothing
End Sub
Good luck,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
TL Mike DH said:
Thanks for the reply.
But I still have the problem with aligning the data I.E.
In the excel file I have:
Product name = Bumper
Model = Exploere
Manufacturer = Ford
Type = Limited Edition
From this data it needs to go into a catalog table that is setup as
CatalogPK
ProductFK
ModelFK
Using the Model information in the excel file to get the ModelPK
Model Table
ModelPK = AutoNumber = 1
Model = Explorer
Manufacturer = Ford
Type = Limted Edition
ryguy7272 said:
This is what I use:
Private Sub Command1_Click()
Call Import
End Sub
Function Import()
On Error GoTo Import_Err
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM [NameOfYourTableThatYouAreImportingInto];")
DoCmd.TransferSpreadsheet acImport, 8, "NameOFYourTable", "C:\Documents
and Settings\ryan\Desktop\Access Models\NameOfYourWorkbook.xls", True,
"NameOfYourWorksheet!A1:AC150"
DoCmd.SetWarnings True
'DoCmd.CloseForm "frmImport"
ImportRVP_Exit:
Exit Function
Import_Err:
MsgBox Error$
Resume Import_Exit
End Function
Notice, this will delete all data in the Table and then do the import. If
you don't delete the data first, the data is appended to the bottom of the
dataset.
Also, take a look at this when you have a chance:
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm
I think Ken pretty much covers almost Import/Excel topic you could possibly
want or need.
HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
:
I have a access database that I am trying to upload data from an excel spread
sheet.
In the excel spreadsheet it has for say:
Catalog Spreadsheet
- Product name
- Model
- Manufacturer
- Type
I want to upload it into a linked table which is linked to the Products
table and the Models table.
Catalog Table
- CatalogPK
- ProductFK
- ModelFK
this is linked to:
Models Table
- ModelPK
- Model
- ManufacturerFK
- TypeFK
and:
Products Table
- ProductPK
- Product
The data is arrange like
- Product name = Steel Front Bumper
- Model = Explorer
- Manufacturer = Ford
- Type = Limited Edition
- Product name = Steel Front Bumper
- Model = Explorer
- Manufacturer = Ford
- Type = Standard Edition
ETC.
I would like to be able to run either a query or have a datasheet form with
combo boxes that would allow me to paste all the information front the spread
sheet.
