numms

I have an Excel Macro Enabled workbook that I've assembled with various links to other Excel files. It is a fairly large file used for compiling metrics from various data sources so there are a great number of formulas involved in the cells. I have placed some Grouping levels in columns to hide components of the calculated formula. For example, ScheduleAdherence%, which consists of ComplianceTime / ScheduledTime. For ease of maintaining this document, these three fields are populated in columns V, W, and X, and since the only metric we're going to be looking at daily is ScheduleAdherence%, I have columns W and X grouped on V. I feel this is a basic enough approach and seems to work for the purposes we need it for, HOWEVER... When using the number references in the upper left corner of Excel to expand and collapse the hidden columns, Excel consistently shuts down giving an error message. I have tried a number of different things to keep this from happening, but even directly after a successful save and reopening the document just saved, I still get this error message. Should anyone have any ideas which may help me out, I'd appreciate it very much. While doing them one at a time is fine, it's not what the program is designed to do. Thanks in advance, Andrew

I'm having an issue with two files in Excel which are each linked to many other documents. The first document (ManagerStats), has a listing of all the Supervisors under that Manager. I've created a link using the Supervisor's name to create a shortcut to the SupervisorStats file. The hyperlink works fine, finds the file and opens it perfectely. The problem comes into play when the SupervisorStats file is updated with information from the linked documents ONLY WHEN THE MANAGERSTATS FILE IS OPEN. NOTE: Some of the files that the Manager and Supervisor stat cards are linked to are the same document. I'm inclined to think that this may be the root of the problem. The SupervisorStats file works fine on it's own when the ManagerStats file is not open, but if the Manager file is open, the Supervisor file will lock. A few points from my testing of this... 1. Neither of these files are linked to each other. There is no data passed back or forth between these two files, all data is populated from other reports. 2. If I open the SupervisorStats, then the ManagerStats, they both update fine. 3. If I open the SupervisorStats, then the ManagerStats, then a different SupervisorStats, all three update fine. 4. The only combination which causes an error is when the ManagerStats is open, then a SupervisorStats is opened. Any thoughts on what could be causing this? I know this is a rather complex issue, and from everything I've seen defies all logic. I'll be more than happy to answer any questions which could help me reach some type of resolution on this issue. Thanks again,

For anyone looking for the answer to this issue, here it is... There is no fix for this. It has to do with a bug in the program with exported Excel documents from a SQL Report Server. The file NEEDS to be opened and saved from within Excel for the values to be able to be looked up. I won't get into my personal feelings on MS for not fixing this, but I wound up having to build in a macro to Open and Save all the files I'm working with. Bit of a pain as there are over 60 reports for this project, but since MS can't seem to fix their software, it's up to us to come up with workarounds.

I am putting together something for my company in which sensitive information is being presented to end users. We have reports which are exported out of SQL server into Excel files on a central drive on the network (permissions are not an issue). Each end user will have a file linked to these exported reports which does a vlookup to pull values from these files. The problem that I am running into is that when the user's file is opened, it is prompting to either Update or Do Not Update values. When I click Update, the message "Unable to read file" is displayed. If the individual files are opened first and then the end user's file is opened, all the values are populated with no problem, but as I mentioned, with the sensitive information contained in these files, I cannot allow the end users to be opening these feeder reports. I have run across KB824172 which I think covers the admission of Microsoft that this is a problem with their product, but I've yet to find any information about how/when/if it's getting fixed. Basically, here is our layout... End User > Given a file with their name in it and vlookups to feeder files (to pull that agent's stats) Background > Feeder files are run through the scheduler in SQL Server Reporting Services and exported to a public drive in a hidden folder. > Formulas on the End User's workbooks have been hidden to prevent them from finding the path (yes, I know it's still available through the Edit-->Links menu - If anyone knows of a way to hide that, it would be greatly appreciated too). > End users open their file, and their stats (Sales, Productivity, Commissions, etc) are displayed on the Excel file on their desktop. ** Special Notes about this... When looking at the links menu, I do have the ability to click Open Source and all linked files will open with no problem at all. This is not an issue with calculations as everything works fine when the files are opened. This is not an issue with permissions to the feeder files as there is no problem opening these files from within the template. This really appears to come down to a function in the program that just doesn't work correctly. Any thoughts on this would be GREATLY appreicated...

I'm having what seems like a pretty basic issue in Access, but I'm not sure what I haven't looked into yet... I am attempting to create a lookup form which will allow the user to select multiple records, then perform a query based on the selections. The List Box is being populated with the results of a query of all the record numbers in a table. Another query has been created specifying [Forms]![AdjustmentFormSeach]![Combo0] as the only criteria of the lookup. The problem seems to come in when specifying the Multi Select value in the properties of Combo0. When the value for that is set to "None" everything works just fine, but the user can only select one record at a time. When the value is set to "Simple", no records are returned at all, but the query does open. When the value is set to "Extended", no records are returned. I've tried changing the criteria on the query to be In([Forms]![AdjustmentFormSeach]![Combo0]) but that returns nothing either. I've even tried adding a text box to display the values that are returned by Combo0 and I get a Null value even though there's no Nulls values in the list box. Sorry if this is pretty basic, I'm still learning various ways to set things up in Access. Thanks for any help that can be provided on this issue.

I'm sorry for asking what I'm sure is a very basic question... Here's the situation: We have an excel file that's exported monthly and saved in a directory \\reporting\ScoreCard\Scorecards-2008-09-Sep-Fiscal.xls I need to copy and rename it to \\reporting\Prepfiles\Scorecards.xls The problem I'm having is in writing a script to copy this from one directory to another with the name changing. I can handle this copy in either DOS through a command batch, or in VBA in the background of an Access project that has a table linked to the \\reporting\Prepfiles\Scorecards.xls file. I've run across several examples where the files are simply named 200809, but how can I translate that into the three character month name? Any help on this would be greatly appreciated, and again, I'm sorry for what should be a simple answer.