Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Excel 2003 read only Results

I have several Excel 2003 application that reference a VB6 COM .dll that
contains a bunch of business logic and sp calls to SQL server. This all
works fine in our development environment. Things got a bit dicier when
trying to deploy the application at a client site. The Excel applications
work fine and are utilizing the .DLL to read and write data from SQL. The
problem comes up in the Workbook_BeforeClose event where the code looks
somewhat like this:

Private Sub Workbook_BeforeClose()

Dim tio as new CustomClientDLL.XClass
if (not tio.Cleanup()) then ' Error is thrown here
MsgBox tio.ErrorMessage
Endif

End Sub

And in the clients environment the tio.Cleanup call throws error 438
(doesn't support method or property). I would normally chalk that up as an
unregistered .DLL or earlier version of the .DLL that did not yet have that
method, but this was installed on a 'clean' machine, and the Excel
application was able to retrieve data using the .dll -- so the application is
using the .DLL earlier on.

A little playing around in the development environment shook my confidence a
bit as one of the applications should a similar behavior, instead throwing a
Error 430. Oddly enough, if a breakpoint was set at this line -- executing
the command in the immediate window works just fine. Comparing this
application to the others th work in the dev environment -- the only
difference was in the references section had the Office 11.0 object library
set higher in priority than the Forms 2.0 Object library. Swapping those two
immediately cured the problem in the development environment -- this has not
yet been tested in the client environment. Since I could not even begin to
explain why this ordering would matter at all, I decided to post here since I
have alot of experience with VB6 and C#, but relatively little with Office
applications. So the question really is -- is there anything obviously
wrong, and how should I approach debugging this remotely at the client site?
I have told that we are using the same version and service packs for Windows
XP and Office. It is particularly perplexing since the problem is
apparently isolated to the exit event.

Thanks for any suggestions up front!

Bob

Hi All

I have written a little excel program with macros in Excel 97. I would like to know if it is possible to allow multiple users to read the document and allow only one person write access to the progam? Is it also possible to monitor what information each user can see? Say I create three users, user1 and user2 can only see data I want them to see and user3 can see all and is able to modify information in the program.

The net only provides info for Excel 2003 (IRM). Is this possible in Excel 97 or am I dreaming?

Thanks for all your help

Kind Regards

Ok, I'm helping someone out with a mailmerge and there's something I can't figure out.

I've actually done a lot of mailmerges (on office 2000) but this one is odd.

Package: Word/Excel 2003

Problem:

This is the data:

http://img352.imageshack.us/img352/9162/exceloy0.jpg

This is what the document looks like, with the document merged to the data highlighted in blue above.

http://img155.imageshack.us/img155/921/mergednc7.jpg

As you see from the first picture, the overtime rate from the data is "n/a", but when it's merged to the document it comes through wrong.
Now I thought at first it might be Word not reading the text correctly (when it's expecting numbers), but if you look at the Shift Premium that has come through correctly - as well as the 2 boxes below it.

For completeness, here is the field codes used:

http://img530.imageshack.us/img530/947/unmergedbe4.jpg

Now as you can see, I have put field codes in that was copied directly from an MS website, to change the data - putting a pound sign in, only displaying a certain number of figures and putting 2 decimal points in.
I have tried removing those codes to see if that makes a difference - if I do that, the field reads "0" instead.

And just to confirm, if I merge the document with a line further up that actually has a number as the Overtime rate, it comes through correctly.

All of the cells are formatted as text, but I have tried to format it as currency, general, number and none of that makes a difference.

Hi,

We're using Win2003 server with terminal services with office 2003 suite.

When someone is working in an Excel file and another need to open file he
receives a message that the file is in read-only state ( until that this is
perfect ) but he do not receive the real username of the one that is
modifying the file.

we always receive the same username even if that user is not connected on
the server.

Is someone already saw that kind of thing ? And how to solve it ?

thanks

I would like to be able to format the cells within Excel so that if I typed
in my athlete's time from a race as 153268 or 3245, it would show up in the
cell as 15:32.68 or :32.45 respectively. I would also like to be able to
then add or subtract time in that same format. Right now I can only format
the cell to display those times as 15:32.6 or 00:00.0 (nothing at all). I do
race time analysis with excel but I have to convert the time into all seconds
if I want to add/subtract/divide or whatever. Can you add a format that
would recognize time not in the sense of how a clock on the wall is read but
how a stop watch is recognized?

I'm currently using the MIcrosoft Office Excel 2003 version.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I have a large worksheet (6,000+ rows) that I am continuously updating
I need to quickly and easily see all "recent" changes made as I scroll through
At present, the data in each worksheet cell are all in black (+ Times Roman)
fonts - I would like all changes and additions to be very apparent - in say
red - and this should ideally include changing data within a cell - say
changing "Note" to "Notes" with just the "s" showing up red
Then when I am happy with certain changes, I want to Format Paint them to
black as I scroll through - leaving only the queries and any new-new
additions, etc. continuing to show up in red - or even make newer changes in
a different colour again?
I've tried Track Change markups, but they are difficult to see and a bit
fiddly to read what correction I made in those little "PostIt" boxes....

Any ideas? Thanks

I am using Excel 2003, and I have set up the workbook as shared and is stored
on the network. Someone changes the file and removes the shared function
causing it to be read only when I open it off the network. I would like to
make the shared function secured if possible w/o using a password.

I read this thread, and I used the code from the vbaexpress.com hyperlink and I copied and pasted the exact code into my worksheet (Excel 2003). The only thing I changed were the conditions in the CASE statement (from "Tom", "Joe", "Paul") to "SL", "AL", "FL").

All other code remained the same but as I made changes to the data, no color changes took place.

I have a 6 month calendar with the dates (1-1-08 to 6-30-08) as the column headers and people names as the row headers. Each cell in the calendar is the result of a VLOOKUP function for the assignment each person has on a given date.

I am trying to have the fill color change depending on what the value of each cell is.

Not sure what I am doing wrong. Thanks for any help.

**MORE INFO**

I entered all the VLOOKUP funtion code in each cell, prior to using this VBA code to do the conditional formatting. It seems that the code works if I re-edit the formula in each cell then hit enter to save the formula in the cell. Is there an easier way to do this other than double clicking on each cell then hitting enter? I have a calendar that has 183 days across and 150 people down. That is 27,450 individual cells I would have to click on then hit enter.

Thanks for any help.

Hello everyone,

Our company has created a program in MS Excel 2003. I know how to password protect sheets and make the file Read-Only. I also know that a Macro could be formed, but we dislike that option because if the Macro is disabled we are back to square one. My Question is there anyway to stop a person from Copying or Emailing or distributing the program in any way to an unathorized person or Computer.

Thanks!

Hello!
I would enjoy input into how people effectively distribute an Excel
file to clients, that they can use without too much trouble.

Here's my situation:

I have an Excel file I've coded some macros for. I will be delivering
it to my client tomorrow, for their review. They run Excel 2003, and I
would like to set something up that they can use without too much
hassle. Mainly, I would like to avoid having to reduce their security
settings to use the file.

Problem is, the file is a timesheet. Each user makes a copy of it, and
then enters time information into it. My understanding of digital
signatures has it that as soon as a user saves the file (with their
entered time data), the digital signature will be removed, since I am
no longer the only person to have worked on the file. And (I believe)
that afterwards, they will be getting the security prompt once again,
'till the end of time.

How is this type of situation dealt with? Obviously, a time sheet is
not useful as a read-only file; users *have* to enter data and save it.

How do the gurus deal with this type of situation?

Regards,
Remi.

Hi Guys,

I have just installed Excel 2010 on a new machine and some of my Excel files created on Excel 2003 will not open. I have read elsewhere that this problem "may" be solved if I change the ending of the files from .xls to one that is more readily recognised by Excel 2010 - I am told .xlsx

In my directories I only see the file name and not the extension. How do I go about being able to see the extension in the directory and then changing it to the desired new one?

If you can help I'd be VERY grateful,

John

Hi all,

I am currently trying to save a spreadsheet that was created with 2007 excel. I can open the file but if i make any changes and try to save it it wont work. It says that the file is "read only"

Is there a way i can make changes and save this file or is it not possible using 2003 version?

Thanks

Hey guys, I have a Excel spreadsheet that has tons of my personal acoounts, email address passwords, and other personal data saved in an Excel spreadsheet. It is protected by two passwords(Open & Write/Read Only) and I carry it on my Memory Stick at all times with a good solid password. My question is, Just how secure is the document? Could the fairly experienced computer nerd crack it? Do i have anything to worry about?

Hi

I am using excel 2003, and when I have a document open, and open another one that contains a pivot table, certain cells on previous documents change color without my permission or consent. It causes problems because the background color can change to something similar to the text and make cells impossible to read. That plus I don't know what the information looks like when I save the document as its source is different to what I am seeing. I've attached an example of what is happening.
There is conditional formats in the cells that are changing color in this example, but it doesn't have to be the case. It can occur in cells that only have shading and no conditional formats at all. Its completely random, and I can't figure out what's causing it

Any ideas?

In the past 2 weeks, a co-worker and I have each run into a problem when working with Pivot Tables in excel 2003. We both use pivot tables every week, and we both never ran into this problem before.

The problem:
When running pivot tables from a data tab, the pivot table doesn't recognize certain numbers if the format is different. We've tried copying & pasting the data (paste values, paste formatting), and we've tried formatting all of the data so it's the same, but there are only two fixes that we've found that actually work: 1) retyping the data (not a good solution when the data tab has thousands of rows) and 2) copying the entire column, pasting as unformatted text into word, and then pasting back into excel. While the second solution works, it is obviously not ideal. The bigger problem is that you don't even know that your pivot table isn't returning correct data unless you are keen enough to double check it.

Has anyone else run into this problem recently? Is there a patch that can fix it?

ok here's the deal...

There is an excel document on a server running microsoft server 2003.
There is a windows machine
There is a mac.

If I am writing to the excel sheet on the windows machine then I can open the excel sheet as read-only on the Mac... this is fine and to be expected of course.

If I am writing to the excel sheet on the Mac and I try to open the excel sheet on the windows machine i cannot even open it as read only... instead I get the error- "Cannot Access: filename.xls"

I only want to be able to read the excel sheet on a windows machine while it is open with write access on the mac.

The docs in question open fine on either machine when they are not in use by the other.

...

This issue reproduces with all flavors of Win Xp Pro and Vista and with OS X 10.4 and 10.5... it reproduces on many different machines, with every excel and/or word doc I've tried... but only Microsoft Office docs.

On Windows we're using Microsoft Office 2007... on Mac we've used Microsoft Office X and Microsoft Office for Mac 2008.

The Microsoft "personal support" service was unable to resolve this issue and they want to elevate to the "pro support" level,... but I'm trying to do some research elsewhere before dropping so much cash on this small inconvenience.

big thanks if you can help!

It takes over 60 seconds for Excel to load. I cannot get to Excel by clinking
an Exce work sheet, (it will stall and then provide an error message). I can
only access Excel through the start>programmes function.

all other applications load without delay.

I am using Excel 2003.

I checked the directory and the file states "Excel11.xlb" - it will not let
me change this file (states is Read Only)...

any assistance greatly appreciated

Hello,

I am having some trouble opening XML in Excel. I have a web program that
will perform a query and export the results in XML format. I have some users
on Office 2003 PRO and SBE, whenever a user opens the file in PRO, they get a
dialog box that prompts them with three options, open as XML list, Open as
Read only file, and one more. Once I see this I know the file will be
formated properly with the headers on each column.

Now when a person using SBE opens the same file they do not get the same
dialog box, the file just opens and all the headers are messed up.

If you need an example I can supply screen shot and send them to you.

Thanks for your help

Andrew Wong

I'm trying to correct some odd formatting errors which occurred when converting an Excel 2003 document opened in Excel 2007. The workbook in question is a series of paragraphs word wrapped in one column with a set width of 45. Each row is (or rather was) AutoFit so the row height matched the paragraph length perfectly.

The issue I'm trying to correct are random spaces at the end of certain paragraphs of text. Each space appears to be a perfect carriage reuturn and appears in about 30 of the 300 rows. AutoFit does not work with code, double clicking or via Excels interface.

I wrote some code which could be the issue, but it fine with all the other paragraphs and some test ones I've created. I can manually grab and move the width but there are enough legacy documents to justify the creation of this add-in. I'm wondering if the issue is indeed my code or there is something else at play.

I've checked the issue cells for blank space before and after as well. I did notice the Category is set to general instead of text. When I try and convert, it changes everything to ########. If I deleted the contents, change the category and paste in just the values of the text desired, everything appears as ####### as well.

I've played with the vertical alignment, word wrap and other formatting variable. If I create a new workbook, set a column width manually to 45, and paste values only into the cell and select word wrap. It eliminates the space.

Does this mean I have to write a conversion sub to copy over the values and manually read formatting settings from old document and then implement in a new document? I mean... isn't that kind of what Microsoft File Format Converter was supposed to do!?!

Another idea I was exploring was a better way to whitewash the formatting of the cell, making it default in all respects like the cell in a new excel workbook. I'm not certain if range.clear will suffice and how to paste values with code.

Below are my subs.

Thanks in advance for all hep and suggestions.

Sub vAlignTop(control As IRibbonControl)
    Dim myRange As Range
    Set myRange = ActiveWindow.RangeSelection
    With myRange
        myRange.Cells.VerticalAlignment = xlVAlignTop
    End With
    myRange.Activate
End Sub

Sub vAlignBot(control As IRibbonControl)
    Dim myRange As Range
    Set myRange = ActiveWindow.RangeSelection
    With myRange
        myRange.Cells.VerticalAlignment = xlVAlignBottom
    End With
    myRange.Activate
End Sub

Sub vAlignCen(control As IRibbonControl)
    Dim myRange As Range
    Set myRange = ActiveWindow.RangeSelection
    With myRange
        myRange.Cells.VerticalAlignment = xlVAlignCenter
    End With
    myRange.Activate
End Sub

Sub hAlignRig(control As IRibbonControl)
    Dim myRange As Range
    Set myRange = ActiveWindow.RangeSelection
    With myRange
        myRange.Cells.HorizontalAlignment = xlHAlignRight
    End With
    myRange.Activate
End Sub

Sub hAlignLef(control As IRibbonControl)
    Dim myRange As Range
    Set myRange = ActiveWindow.RangeSelection
    With myRange
        myRange.Cells.HorizontalAlignment = xlHAlignLeft
    End With
    myRange.Activate
End Sub

Sub hAlignCen(control As IRibbonControl)
    Dim myRange As Range
    Set myRange = ActiveWindow.RangeSelection
    With myRange
        myRange.Cells.HorizontalAlignment = xlHAlignCenter
    End With
    myRange.Activate
End Sub

Sub wwTrue(control As IRibbonControl)
    Dim myRange As Range
    Set myRange = ActiveWindow.RangeSelection
    With myRange
        myRange.Cells.WrapText = True
    End With
    myRange.Activate
End Sub

Sub wwFalse(control As IRibbonControl)
    Dim myRange As Range
    Set myRange = ActiveWindow.RangeSelection
    With myRange
        myRange.Cells.WrapText = False
    End With
    myRange.Activate
End Sub

Sub afRow(control As IRibbonControl)
    Dim myRange As Range
    Set myRange = ActiveWindow.RangeSelection
    For Each Item In myRange
        If Item <> Null Then
            myRange.RowHeight = 1
            myRange.RowHeight.AutoFit = True
        End If
    Next
    myRange.Activate
End Sub

Sub afCol(control As IRibbonControl)
    Dim myRange As Range
    Set myRange = ActiveWindow.RangeSelection
    For Each cell In myRange
        If cell <> Null Then
            myRange.ColumnWidth = 1
            myRange.ColumnWidth.AutoFit = True
        End If
    Next
    myRange.Activate
End Sub


I have a fairly large form which I want to have users fill out. While it is possible to have it Read Only so that they always SaveAs I would rather put in a macro to clear the user input fields. I have done this in Excel 2003 and when I try using the same techniques in 2007 I get an Error 400.

I have all the cells (some are merged cells) in a named range and have the following code for clearing them.

Public Sub ClearUserInfo()
Range("Sheet1User").ClearContents
End Sub
I have tried to select the named range manually and right click and choose Clear Contents and it works so I am not sure what I am not doing right.

Help is appreciated.


No luck finding an answer? You could always try Google.