Worksheet / workbook problem

[pwbrown]

Hello,

Basically I want to save a worksheet from one workbook into a different workbook within access. How do i go about doing that?

I was guessing I could open two excel application objects and open the workbooks or worksheets there but i wouldn't know how to save one onto the other.
The worksheets would have the same name if that helps and they both already exist so it's like an overwrite.

Kind regards,

Peter

 

[JohnLee]

Hi, Why do you need to do that in Access, If the data never gets imported into Access then why use Access to do that type of work, just use Excel's own features to do that.

Regards

John

 

[pwbrown]

It will actually get imported right after. I made a template excel file to calculate values from a worksheet so I want to overwrite one worksheet then import another after it uses the imported one to get values. I can't do this manually as there are hundreds of excel files!

 

[JohnLee]

Hi again,

To copy a worksheet from workbook to another retaining the same tab name do the following:

Open up both workbooks, select the worksheet tab from the workbook you want to copy from and Right click the tab and select the option "Copy or Move...". The Move or Copy dialog box will pop up and will display all the worksheets in you workbook, assuming you only have the one then, only that one will be displayed in the "before sheet" section, making sure that that worksheet is selected, click in the "creat copy" check box, then in the "To book" option select the workbook you want to copy or move the spreadsheet to and then click on the "Ok" button you spreadsheet will either be copied or moved according to which option you chose.

Close your workbooks, Job Done

Regards

John

 

[JohnLee]

Hi,

Create a process within Excel to do what you want, have a master spreadsheet with the code in it and action it from there.

Excel has some powerful features of it's own, if you explore them you will be supprised at what you can achive.

Regards

John

 

[pwbrown]

Hi again,

To copy a worksheet from workbook to another retaining the same tab name do the following:

Open up both workbooks, select the worksheet tab from the workbook you want to copy from and Right click the tab and select the option "Copy or Move...". The Move or Copy dialog box will pop up and will display all the worksheets in you workbook, assuming you only have the one then, only that one will be displayed in the "before sheet" section, making sure that that worksheet is selected, click in the "creat copy" check box, then in the "To book" option select the workbook you want to copy or move the spreadsheet to and then click on the "Ok" button you spreadsheet will either be copied or moved according to which option you chose.

Close your workbooks, Job Done

Regards

John

I'm not going to do that hundreds of times for each workbook!

Hi,

Create a process within Excel to do what you want, have a master spreadsheet with the code in it and action it from there.

Excel has some powerful features of it's own, if you explore them you will be supprised at what you can achive.

Regards

John

I've had a master spreadsheet from the start. I wanted to find an easy way to put the spreadsheet into hundreds of workbooks all with the same format but different values. Then I'll import all the spreadsheets into access and combine all the data then export it into one hug spreadsheet.

 

[Rx_]

Recommend using object code. Give me an idea about your current skill level and I will try and help you tomorrow.
Do you know how to Set a reference to an existing Workbook?
Are you creating an Excel Workbook via Code now?

Are the hundreds of workbooks you want to update (evidently each with some custom data) in a folder where they can be identified some way?

I create a lot of advanced Excel Automation from MS Access if you can describe your skill level and provide a little more detail of what you wish to accomplish. Will try to help.

 

[pwbrown]

Recommend using object code. Give me an idea about your current skill level and I will try and help you tomorrow.
Do you know how to Set a reference to an existing Workbook?
Are you creating an Excel Workbook via Code now?

Are the hundreds of workbooks you want to update (evidently each with some custom data) in a folder where they can be identified some way?

I create a lot of advanced Excel Automation from MS Access if you can describe your skill level and provide a little more detail of what you wish to accomplish. Will try to help.

I'm certainly not advanced nor am I a beginner, somwhere inbetween.
I have done a lot of work using object code but not in saving worksheets to a different workbook it originated from.

Setting a reference is no problem.
No I'm not creating the excel workbook from code
The workbooks will all be located in the same folder.

So let me try to explain it better:

• I have these hundred odd workbooks each with 5 or 6 worksheets.
• I need to gather information from all of them and put it into one spreadsheet. The formating of these spreadsheets are awful (not done by me)
• So I created a master spreadsheet which has all the formulas' to take the data from each spreadsheet in each workbook.
• I need to find an easy way to add this spreadsheet to each workbook without doing it manually.
• Once it's added, I'll import that new spreadsheet from each workbook and combine them all into an access table then export it all into one spreadsheet.
• The last point I have already developed in access.
• All the workbooks have the same layout.

Can't think of anything else right now.

Kind regards,

Peter

 

[Rx_]

Uploaded an Access 2010 DB
ExcelHarvest 4nd Generation - Public

This single purpose - 1 time application did the following:
1. Given a directory path
Goes through all sub-folders to find all Excel Workbooks.
Simple Qualification that the Excel Workbook meets the criteria
(e.g. Mine were all based off of a template - so check a specific cell for a specific string to pass qualification - your qualification will differ)

2. Qualified Excel Workbook's Path and name is recorded into a local Table field. It also records the Workbook's Worksheet Count.

This is the part of the code I suggest you first concentrate on. Inventory the path, names, and worksheet counts of all your workbooks.


After that is done, a table of the worksheets that will be modified exist to manage the update.

This example does not modify the existing workbooks. instead,
3. it uses the Path / Worksheetcount to harvest the data from each workbook, worksheet's Tab name (a year), worksheet Header data, then the 12 months of sample data.
In this example, the template was the same for all of them.
This data is harvested then put into another table (Flare).

# 3 must (code and table) must be highly customized for your exact case.

Your 4th requirement will come later. After you harvest the desired information, use the master Excel Path, name, worksheetcount information to insert another worksheet with the template information as you described.

There is not a lot of comments for a beginner here. Suggest that the Immediate Window be visible - set some break points and step through the code.

 

[pwbrown]

I should probably have mentioned I'm using access 2007 (not my choice).

 

[pwbrown]

I think I'm going to scrap this idea, I don't need to do the process this way anymore. Although I will still look to find out how to do it, in my own time, just incase I ever need to know how to, in the future.
Thanks for all the replies guys!