Auto import & AutoRepublish

[no dice]

I have two spreadsheets. One that contains data input by a few authorized users. The second imports data from the first to create pivot table reports. The sole purpose of spreadsheet two is to 'SaveAs' html and publish the reports to the web.

Currently, I manually open spreadsheet 2 and 'enable' the refresh of the data import. The PivotTables are set to refresh upon open. Then I manually 'SaveAs' html (non-interactive).

My question is:

Is there a way that every time spreadsheet 1 is saved, spreadsheet 2 automatically imports the new data, refreshes the pivot tables, and saves an html copy - all without ever opening spreadsheet 2?

[Greg M]

Hi there,

The attached might do what you need. There are two workbooks, a data one & a pivot table one (I haven't included the pivot table itself)

The data workbook contains the necessary VBA code in the "ThisWorkbook" module.

Open the data workbook, enter/change data & save the data workbook. The action of saving the data workbook opens the pivot table workbook (it must be in the same subdirectory as the data workbook), the data is imported automatically, the pivot table workbook is saved automatically in HTML format, & the pivot table workbook then closes automatically.

Is this what you want? Please let me know how you get on & whether or not you need further information.

Best regards,

Greg M

[no dice]

Thanks Greg... I haven't got a clue about VBA so I apologize if I'm not getting it. Your example does auto-save the .xls to .html, but I need it to work spreadsheet 1 to spreadsheet 2 to spreadsheet 2 html. I don't want an html version of spreadsheet 1.

[Greg M]

Hi again,

Ok on not understanding VBA - no problem there.

Maybe we're not understanding each other - My Workbook 1 (.xls) is the data workbook. My Workbook 2 (.htm) is a workbook which is linked to Workbook 1. There is no .xls version of Workbook 2 - do you need one? Similarly, there is no .htm version of Workbook 1.

Would it help if you posted sample copies of the workbooks so I could see exactly what's involved?

regards,

Greg M

[no dice]

Thanks for sticking with me...

Well, I think I do need the 2nd .xls workbook, but perhaps that is my problem. I've attached what I am working with. The "...Tracker.xls" is where the data is stored. The "index.xls" is where I have created pivot tables that generate by linking to the data in "...Tracker.xls" I 'SaveAs' "index.xls" as noninteractive .htm so I can publish the pivot table reports to a web server. If you try this yourself you will see you get an "index.htm" file and an "index_files" folder that contains the supporting files.

Actually... I cannot attach my samples because they exceed the 100kb size limit. If you PM me your email, I'll email them to you.

[no dice]

Got your IM... sent you email. Thanks again.