If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need an example of importing from Excel to Access
A user has asked that some of his spreadsheets be combined into an Access
database. Each workbook has multiple sheets, with most of them being charts using data from the first worksheet in each workbook. These graphs are then copy/pasted into PowerPoint. Can that be done from Access also? Also, one spreadsheet comes from an outside source and a new one comes in every two weeks, replacing the old one. There are five header rows, some of which are merged vertically. The columns are rearranged with each new version of the spreadsheet based on a delivery date for the item in the column, and data in the column changes also, as does the number of columns and rows. The first six columns are static. I think that each two weeks I will have to delete a table in Access and replace it with data imported from the newest spreadsheet, but have never worked with Excel data before, and also don't know how merged cells will effect anything - these contain the item ID number which will be critical. Each column merges a different number of cells from 2 to 5 cells. Does any of the Access guru's have an example database showing how to import from Excel? Currently it will be Office 2003, but soon will be 2007. The code will have to work with either. I have done imports with text delimited data but this is entrely new to me. Mich |
#2
|
|||
|
|||
Need an example of importing from Excel to Access
Mich,
Take a look a Ken's site for some code ideas. http://www.accessmvp.com/KDSnell/EXCEL_Import.htm You may have to build your database around the .Xls your importing. If you can match the column heads to your table field names it might work. I import 3-4 spreadsheets a week from different sources, I have to tweak the column heads and delete unnecessary data but it can be done. hth Good luck Richard |
#3
|
|||
|
|||
Need an example of importing from Excel to Access
The spreadsheet is set up exactly opposite of what importing is expecting.
Instead of a column of VehicleIDs, these are in a row, with info in the rows beneath, so I need to be able to look at the spreasheet cell by cell and analyze the data to put it into an Access table. I'm probably going to have to start at the top of each column and work down to get one record for Access. All of the import examples I have seen so far assume each Excel row is a Access row, while for this application each spreadsheet column is an Access row. The spreadsheet is created by another entity which we have no control over. Any more ideas on how I can import this Excell spreadsheetsheet bi-weekly into Access? Mich "Richard" wrote in message ... Mich, Take a look a Ken's site for some code ideas. http://www.accessmvp.com/KDSnell/EXCEL_Import.htm You may have to build your database around the .Xls your importing. If you can match the column heads to your table field names it might work. I import 3-4 spreadsheets a week from different sources, I have to tweak the column heads and delete unnecessary data but it can be done. hth Good luck Richard |
#4
|
|||
|
|||
Need an example of importing from Excel to Access
My approach would be to attempt a series of steps to see if they get
you where you want to go, then begin to develop VBA code to repeat the process. Step 1 would be to discover if the Excel Transpose function will successfully transpose the data into the row / column format that Access expects to find. Step 2: can that data be imported, or is some editing necessary? Step 3: Can that editing be automated, or will it require human decision making? Step 4: Develop Macros in Excel to prepare the data for importing. Step 5: Develop COM Automation code in Access to do the same thing as Step 4 but now in code behind a form in Access. Good luck! and come back here with specific questions as you need assistance. -- Clif "M Skabialka" wrote in message ... The spreadsheet is set up exactly opposite of what importing is expecting. Instead of a column of VehicleIDs, these are in a row, with info in the rows beneath, so I need to be able to look at the spreasheet cell by cell and analyze the data to put it into an Access table. I'm probably going to have to start at the top of each column and work down to get one record for Access. All of the import examples I have seen so far assume each Excel row is a Access row, while for this application each spreadsheet column is an Access row. The spreadsheet is created by another entity which we have no control over. Any more ideas on how I can import this Excell spreadsheetsheet bi-weekly into Access? Mich "Richard" wrote in message ... Mich, Take a look a Ken's site for some code ideas. http://www.accessmvp.com/KDSnell/EXCEL_Import.htm You may have to build your database around the .Xls your importing. If you can match the column heads to your table field names it might work. I import 3-4 spreadsheets a week from different sources, I have to tweak the column heads and delete unnecessary data but it can be done. hth Good luck Richard -- Clif |
Thread Tools | |
Display Modes | |
|
|