automating worksheet copy to new workbooks

[Ashank4vba]

Hello,

Please help.
I have around 800 workbooks in a directory. All of them have a worksheet called 'Inspection Form'. I want to copy just this worksheet to a new workbook and save the workbook. I would like to save each new worksheet to a new workbook separately. I can do this manually but I dont know how to do this by automation. I am not sure if I can use the DoCmd.CopyObject function from Microsoft Access for this. Please advise! Thank you very much.

-Arvind

 

[SkipVought]

Hi,

Do you REALLY want to proliferate all these workbooks?

Are they each going to a separate person?

Anyhow, take a look at the FileSystemObject Object in HELP.

Then you can loop thru the folder to open each workbook and copy the appropriate sheet.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Ashank4vba]

Thanks Skip. I cant think of any other option otherwise. The worksheets contain lots of pictures in them and hence are quite big. I just want to work on their data and thats in the 'Inspection form' worksheet (with no pics in the sheet). So I am thinking of making new workbooks with just the data so that they might be faster to work with and would be easily portable. Thanks for your suggestion, I'll work on it.

 

[SkipVought]

"The worksheets contain lots of pictures in them and hence are quite big."

Did you mean that the workbooks contain lots of pictures in the sheets other than the Inspection Form?

1. Consider assembling the Inspection Form sheets into a single workbook.

or

2. Consider reading the data from each Inspection Form, and adding that data to a TABLE (sheet) of inspection data.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Ashank4vba]

Hey Skip,

Yes, all workbooks contain many pictures in them.

Thats what I am trying to do. I'm trying to read all the data from the 'Inspection Form' Worksheet into an Access table for further processing. However, currently it takes anywhere between 1.5 - 2.5 minutes to process a single excel workbook and there will be more than a 1000. This is why I am trying to make copies of the workbooks without the pictures to see if this speeds up the importing of data into the access table. I am using Excel objects in VBA for importing.

Also, I have run into an additional problem when I try to attempt this. The worksheets contain option buttons with names such as "Option Button 1056" etc...I had hard coded this information for importing. However, when I create a new workbook by copying the 'Inspection form' worksheet from an existing workbook, the option button names seem to change slightly..For example, "option button 1056" now becomes "option button 1055"....Dont know why this happens or if it will happen to all workbooks. Any insight would be greatly appreciated. Thanks.

-Regards
Arvind.

 

[SkipVought]

Please post the code that you are currently using to get the data out of the Excel workbooks into your Access table.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]