I have the following codes which I run directly in Excel to delete links, delete sheets and save a new email version of the workbook. I would like to run this code from Access as part of my regular refresh batch, instead of Excel, so I can keep all my Excel files macro-free. How do I need to modify the code to do this? Muchos Gracias!

You'll need to set a reference to the Excel Object library I believe. Then you can create an Excel object in Access VBA. Here is some "air code":

Dim xlsObj As Object
Dim xlsRange As Excel.Range
Dim xlsSheet As Excel.Sheet

Set xlsObj = CreateObject("Excel.Application")
xlsObj.Workbooks.Open "[path to your workbook]"
With xlsObj
Set xlsSheet = .Activesheet

etc...

This post has been edited by thegeek: Apr 9 2012, 01:22 PM

here is an example of how to do your deletesheet.

Thank you. I love examples!

Any "example" for how to do the deletelinks() sub? (IMG:style_emoticons/default/iconfused.gif) I was able to get some working code to copy/paste in the first worksheet. But I can't get it to loop through all the worksheets in the workbook.

you may not need to loop thru all the worksheets. In your case, you are working on all the cells in all the worksheets. You should be able to select them all at once and perform what you need.

try this extra piece of code ****untested****

Thank you Bob. I can see how that would work for something like a hyperlink or a connection where the code can identify, select and delete. How would this look if I wanted to go through each sheet and replace formulas with values?

off the top of my head and only for conversation purposes.

is what you are describing basically selecting the entire worksheet, the selecting copy, then paste special ?

Basically, yes.

then it would work the same basic way. Selecting all the worksheets and then all the cells and copy and paste formulas.

you can record a macro that does the steps and see what the excel vba would look like

Can I ask what the overall process is (As in what/why you are exporting from Access. What is in the Excel file etc) as I think there are other ways of tackling this that would remove the need to delete sheets and links etc and could all be done Access side.

@jon,

The way I read this was that the OP wanted to move the "work" from Excel to Access.

Bob

Hi Bob, I think I am reading it the other way, Access work to Excel. But the reason I am after a little bit more info is because I think this is the same thing that NineIron is asking about in another topic right now and I think some of the solutions proposed there (Pushing the data to a pre-defined spreadsheet avoiding links and MS-Query so as to allow emailing) might be better options then what is happening here. Need a little more detail to be sure though, I could be barking up the wrong tree.

will have to wait to see which way it really is.

Firstly, thanks for all your replies. I have something that is 98% there now, but let me try and better explain what I am doing.

1) I have a lot of databases and queries built in Access than run every day through a master batch file. These are pulling in fresh data from a variety of sources.
2) I have linked data tables (from Access tables) in Excel files to create Dashboards with graphs, statistics and other relevant information that is emailed to a distribution. These Excel files contain a lot of pivot tables, vlookups, array formulas, etc., to provide the "graphs, statistics and other relevant information" I need.
3) Rather than emailing out a 50mb Excel file, I wanted convert all formulas to values, delete the sheets with linked tables that I don't need anymore, and create a slimmed down email version of the Excel file.
4) I already had the Excel macros to do this, but I wanted to run it from my Access master batch file directly--hence, the crux of my biscuit (problem, as it were)

This was my original Excel code which worked great:



This is where I am at with the Access version. The only problem now, is that it is not fully releasing the Excel file as I cannot open it until I quit Access. What am I still doing wrong?

this should be what you need.


xlApp.Close
set xlApp = Nothing

Thanks Bob!

While the code is working now, Jon's comment above got me thinking that there is probably a more efficient way to do what I am doing. Instead of creating a linked data table in Excel which pulls from my Access table and refreshing each time, have Access push the data into Excel eliminating the links. If I have an Excel file called "C:\Dashboard.xlsx" with a worksheet called "Data", what is the best way to transfer all records from my Access table "FinalDataTable" into my Excel worksheet "Data"? I would need the code to overwrite existing records in "Data" without any prompts. Can you point me in the right direction? Thanks.

i think if you create a new thread for this new scenario you will get more people looking to give a good answer. You can also search UA

Hi Classicxman,

See attached my example of pushing data to an existing Excel file. I choose to use an Excel template file when doing this as it opens a new instance of the file and forces the user to save a new copy somewhere keeping the original template always intact.

You need to set a Reference to the 'Microsoft Office Excel Object Library' in the Access file otherwise it won't run. I used a dynamic named range in Excel as the source data for my Pivot Tables and Charts, this means that now matter how much data you add in it will always include all of that in them and I have written some quick code on each one so they refresh each time you select them.
Apart from that it should be self-explanatory from the files.

Good luck if you choose to go this way and don't hesitate to ask for any more help.

JonSmith