Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel Macro's


  • Please log in to reply
1 reply to this topic

#1 excel_inquiry

excel_inquiry

  • Members
  • 1 posts
  • OFFLINE
  •  
  • Local time:09:36 AM

Posted 14 July 2010 - 07:45 AM

Hello,

I have developed an approx 50 page (work tabs) spreadsheet in Excel 2003 (Windows XP).

The workbook contains three main macro’s and after several manual inputs are performed the salaries for the organization are projected.

When I send out the report to the applicable parties, I hide the other 49+ tabs and then protect the workbook with a password to ensure the other parties cannot see the other areas information.

Due to the size/complexity of the document I am unable to delete the tabs (takes a long time) and that is why I have hid the rest of the tabs.

Each tab runs on the three main macros and the figures that are generated are rolled up in summary pages for the applicable areas (these summary pages do not used the macros)

Question #1: in a complex workbook is there a way to delete the tabs quicker?

Part 2:

A majority of the parties still use Windows 2000 (Excel 2002) due to other applications that are based around this operating system.

They are able to update/save the report and any changes that are made are accurately reflected. However, when the Excel 2002 user saves the document and sends it back to me it takes an extremely long time (45 mins) for their report accurately show the figures. This message occurs in any cell that requires the macro:

#NAME?

Another piece of information that I obtained said:

Microsoft Office Excel re-calculates formulas when opening files last saved by an earlier version of excel.

This leads me to believe that since the parties are saving it on the 2002 version, I cannot easily open the file because the entire hidden report needs to be re-calculated.

Question 2:

Is there any format or way to save or set up the macro’s/spreadsheet to eliminate this issue?

Any suggestions would be much appreciated. Thank you!!!!!!!!

BC AdBot (Login to Remove)

 


#2 Geoffc

Geoffc

  • Members
  • 92 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Warragul, Australia
  • Local time:12:36 AM

Posted 27 July 2010 - 01:33 AM

Your problem reads like there's a lot of unstated constraints in your situation, like resources and procedures for distribution, management, return, collation. Too much for me. I'm looking here just at a more efficient message setup operation.

Sounds like much of your overhead irritation relates to bothway transmission of large quantities of irrelevant (even though hidden) data. Here are three lines of thought:

1. Another macro to generate 50 individual spreadsheets from your workbook.

2. Rework your base dataset in EXCEL to bare-bones tabular form, to enable mailmerge into a user-friendly WORD template. I've done this with 50 data fields into a 20-page 85-client report template, and it's a ho-hum WORD mailmerge task once you get the master doc looking pretty.

3. Take this on to include the transmission stage with the article "Integrate mail merge in Microsoft Word with Excel" at TechRepublic, at http://techrepublic.com.com/
The direct approach http://search.techrepublic.com.com/index.p...l&go=Search might even work.

I'm afraid any of those options is a week's work, but that's why we're paid so well.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users