Hewson, Jim
JHewson at nciinc.com
Thu Jul 10 15:02:24 CDT 2008
Do you wish to pull all the data from the multiple spreadsheets into one workbook or do you plan on pulling all the data into a table? I have code for either. In mid June I asked the question of how to hide a database view of a table as I was copying and pasting from Excel into Access. My problem was that I had several Workbooks with multiple spreadsheets each that I needed to pull into Access into a single table. I would need to pull in the data, manipulate it, them export it to a different Excel Workbook. Then the temp tables I used to import the data would be cleared and the process would start over again. Manually, the process was taking one or two people three to four days to complete. The recommendations were to use 1. DoCmd TransferSpreadsheet, 2. Use an array 3. Manipulate the data in Excel to another workbook 4. Copy and paste the data from Excel to Access. The original problem included an Excel Add-in that combined all the spreadsheets of one workbook into another workbook in one spreadsheet. The one spreadsheet was then imported (manually) into Access and the data manipulated. My thought was to use Access to combine the spreadsheets into a temp table then work with the table to extract the data I needed. I first used Copy and Pasted; that is I copied the data from Excel and then pasted it into Access. This worked, but the Excel spreadsheet would not close - hence my exasperated inquiry to this list. It was suggested by Heenan to use the DoCmd.TransferSpreadsheet. I was successful in getting this to work and it worked well. Two problems cropped up though. One, an Excel instance was always present in the Task Manager and when I attempted to start over I would get an error message. I used several suggested solutions such as to quit Excel, to close everything (worksheets, workbooks, etc.), make sure to cut off user control (UserControl = False) or to ensure Excel was called explicitly every time (that meant I couldn't use With appExcel). I spent a couple of days trying to find a solution. In one blog I read it mentioned that cut off UserControl only worked in A97. Nothing worked. I finally ended up using a Shell command to kill the Excel Instance. The other problem I encountered was when a workbook had more than 100 spreadsheet it would take f-o-r-e-v-e-r to complete. One workbook had over 700 spreadsheets and it would take several minutes to import the data. Drew recommended and gave me code to create an array. Sorry, Drew, I never did work with it much. I spent most of my time trying to the TransferSpreadsheet option to work. So I can't really say if it would work. My current process is to use the Excel Add-in to create an additional workbook with all the data in one worksheet. The Add-in takes seconds to do the work - one Workbook has about 2400 spreadsheets (a whopping 42MB) and it takes less than a minute to combine the spreadsheets (BTW - this file actually creates two spreadsheets because of Excel's row limitation). After the spreadsheets are combined then the data is imported into Excel. I would like to thank everyone who helped me this. I'm sorry it took so long. HTH Jim jhewson at nciinc.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Porter Sent: Thursday, July 10, 2008 2:16 PM To: Access Developers discussion and problem solving Subject: [AccessD] Pulling all sheets from a spreadsheet Does anyone have some code that pulls data from all tabs in a spreadsheet, assuming all data is in the same format? I'm getting spreadsheets with 50+ tabs of budget data for loading into a database, and it's very cumbersome. Thanks, Mark Porter Sr. Technologist Nana Development Corp. Desk: 907-265-4156 Fax: 907-343-5656 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com ################################################################################ This email transmission contains information from NCI Information Systems, Inc. that may be considered privileged or confidential and is intended solely for the named recipient. If you have received this message in error, please contact the sender immediately and be aware that the use, copying or dissemination of this information is prohibited. ################################################################################