need macro to remove auto_open when i Save

[dmcgee57]

I have a file that generates a report automatic using the Autpen macro and the scheduler .. at 6 am the file open and imports data from other files and generates report, then, the report is auto save as a file with at date code added. Problem ... when the file is rename it still contain the Autpen macro. I have several people that need to use the data from the report but it runs the macro when opened ... I know to hold the shift key when opening the file but others fail to remember .... can I add a macro when saving to strip the Autpen from the newly created file????

 

[Joe4]

Welcome to the Board!

Instead of having it import data into your Macro workbook, why not have it import data into a NEW workbook (that wouldn't have the macro then)?

Or, likewise, after importing to the macro workbook, open a new workbook and copy and paste all the data into the new workbook and save?

 

[GTO]

Greetings,

To programmatically remove/add code in (I forget which) either 2002 or 2003 and thereafter, requires that the users have the "Trust Access..." in macro security ticked. You may find this to be a hassle, depending on how many users, and how much time you have to explain this to ea of them.

I personally like Joe's suggestion of giving them a clean/no-code-included workbook, but was also thinking that if there is no code other than the auto_open, a simple solution might be to include an IF statement at the top of the procedure.

Not tested, but I would think that 'If ThisWorkbook.FullName = (whatever the fullname of your original is) Then'... might do the trick.

Mark

 

[dmcgee57]

Great suggestions ... but, I can't create the report in another workbook because I am using Name ranges .. this file is so large that I have the macro turn off the calculations until all imported files from the AS400 have been loaded (it takes 20 minutes to calculate when the macro turn calculations back on). I am using 2007 and then taking the data detail sheet and transfering it to a 2003 version file to send to the sales group (most do not have 2007). I can not take the master file and "save as" a 2003 because the pivot table is no longer active (I had the create the file on 2003 so it would allow the drill down for the pivot table, saving as removes that option). I also make a copy of the 2007 file each day for daily snapshot of production. I have tried coping the sheets to a new workbook before saving ... but, I am having problems breaking all the links in 2007. I have to break links - save - close file - reopen (some links are still active) and repeat the breaking link 3 to 4 times before they are all gone ... can you help ?????

I know you are saying this should be in a database like Access instead of Excel ... I agree, but I am doing contract work for this company and Excel is their choice since no one their is familiar with Access.

 

[Joe4]

I know you are saying this should be in a database like Access instead of Excel ... I agree, but I am doing contract work for this company and Excel is their choice since no one their is familiar with Access.

The beautiful thing about Access is that your users don't need to know the first thing about Access to use it. I create Access applications for a group of users who have no more than high school educations and are hardly computer literate. But if you make the Access application all form driven, it can actually be easier to use than Excel!<!-- / message -->

 

[dmcgee57]

I have created several Access databases with Switchboards ... but the company that I have contracted with did not buy the Office package that included Access. Most the people that need the report are in sales (located across the US UK & Asia) they are not a local network. I suggested saving the file as a PDF or Web Page, but they want a working spreadsheet with active Pivot Table for the drill down feature. If you can find a macro that will disable the Autpen feature ... or Break links when I copy sheets into new workbook.
Thanks

 

[Joe4]

If you can find a macro that will disable the Autpen feature ...

They are both VBA code, you can't disable the Autpen while having the other one enabled, especially since the Autpen one is the first one that runs when a workbook is opened!

GTO's idea may help you do what you want. Basically, you leave the Autpen code in there, but have it first run a condition to see if it has already been run today. If it has, it can exit the code without doing anything else (if you are saving the file with the current date, have your condition check to see if a file with today's date already exists).

Or, you could have the macro store the current date somewhere in your workbook when it runs. Then just check that cell to see if the date is the current date.

Regarding removing the macros, perhaps this link will be of use. Note that there is a link in that post that might also be useful.
http://www.mrexcel.com/forum/showthread.php?t=283478

 

[ZVI]

Hi & welcome to the Board!

So you have the master XLSM-workbook and the XLS-reports built from it.

Here is the link to the code for deleting a procedure from a module: Programming The VBA Editor

To avoid running Autpen macro in XLS you can use such code:

Sub Auto_Open()
  ' Exit if not the master XLSM workbook
  If Not UCase(ThisWorkbook.Name) Like "*.XLSM" Then Exit Sub
  ' Your code ...
End Sub

If macros are not required at all in the report XLS then you can save XLSM as XLSX (macro are not saved in it) and then as XLS

Below is the code for breaking all links from the active workbook.

Sub BreakLinks()
  Dim Lnk
  On Error Resume Next
  With ActiveWorkbook
    For Each Lnk In .LinkSources(Type:=xlExcelLinks)
      .BreakLink Name:=Lnk, Type:=xlLinkTypeExcelLinks
    Next
  End With
End Sub

But to avoid storing links you can just copy all sheets to the new workbook and then delete some of them. In this case all names are copied as well.

Regards & Happy Holidays!
Vladimir

 

[dmcgee57]

Thanks for all the support ... the IF Not statement works wonderful

Have a Wonderful New Year,
Debi