Replies

Thanks

I’ll pursue this line with the network administrators and post a message here if it solves the problem.

Thanks for your reply Legare

Can you tell me more? Have you had experience with this sort of problem before?

TIA, Leigh

Thanks Jefferson for your reply – but now I am more perplexed as to what is causing the problem. Not because of your reply but I spoke to a contact that is also running Office XP under XP Pro and opening networked MS Word files takes an inordinate amount of time on his very small Microsoft network (5 PCs, no server). If he copies the file to his workstation, there is no delay in opening the document. (These files have no links, they just take longer than expected to open across the network.)

The original MS Excel problem is running across an NetWare network and I suspect that that may have some bearing on the problem. Anyone able to say what NetWare settings may sloooow down an update in MS products? Or am I just clutching at straws and of course I am hoping that the problem is not some change to the way MS Excel checks its links.

All suggestions gratefully acknowledged. I am at a loss to find a solution.

Whilst I do not use that model HP printer, I have a workaround that I put up with whilst quietly cursing the software/hardware.

I simply select the text by clicking and dragging across it with the mouse, copy it and then paste into a wordprocessor (I use MS Word). If the pictures don’t come with the text, and that happens a lot, I copy the pictures individually and then paste them where required.

Additionally, this gives me some editorial control over the document unless there are copyright issues to consider.

HTH, Leigh

Hi

You could relpace the line in your code that says

Worksheets("Template").Protect Password:="MyPassword"

with the following:

With Worksheets("Template")
    .Protect Password:="MyPassword"
    .Visible = xlVeryHidden
End With

In this way the “Template” worksheet is hidden at all times.

Leigh

Solved with the following:

    Application.DisplayAlerts = False

Leigh

I now have this project at the stage where the data required for the label is temporarily stored in the last worksheet and then deleted when the user closes the workbook.

I used the following code to start MS Word after copying the required cells.

    Range("A100:A104").Copy
	Application.ActivateMicrosoftApp xlMicrosoftWord
	' code to paste in MS Word

Any suggestions as I have not determined a way of having control over MS Word to paste in the copied text from within the MS Excel macro.

I will be taking info from the particular label that the user selects in the userform so that MS Word’s Page Setup can be modified to suit the relevant label position.

Help! Leigh

Thank you Hans

I had heard of that link finder XLA but had not explored it. Now that you have reminded me, I’ll look into it.

BTW, other VBA afficionados will appreciate the site that Hans mentioned. It has some very useful links (‘scuse the pun) and information for VBA and MS Excel (most versions). Now if there was a VBA command to break any links in workbook and replace them with the value I would be the happiest person this side of the Pacific. (I really must get a life, I think!)

Thanks Jefferson

I have about thirty workbooks that can be used to create these reports. About 12 sheets in each have the links on them with between 20 and 400 cells actually linked.

Those worksheets are protected so that users can only add information where allowed, therefore it would be not impossible to come up with some code that can find each link. The contents in each cell could be copied and then pasted as values only but I fear there are too many individual links and the users will get click happy whilst waiting for the sands to sift thru’ the hourglass (thought; does shaking it make it run faster?)

All suggestions most gladly considered.

Leigh

Thanks Bob
That was the problem but I had never seen it before.

Leigh

Basically what happens is that a user can access data that has been created in CSV format by the IT department from various sources. This original data file is either filtered to show a range of items of interest to the user for QA evaluation (medical tests) or a specific test together with categories of times (eg Cat 1, Cat 2, etc.). The user creates a file that they name and save with the date range (typically, for the previous week) and type of test and category (eg 24-30032002-ALL-B1.xls). This data file is saved ready for reuse when the user needs to check the QA for another test type and the category that it matched. It allows the users to re-evaluate data again and again until they are satisfied that all relevant QA matters are considered.

The code that I developed is now working very well thanks to Woody’s Lounge and its contributors.

So don’t feel too badly about not understanding what the application was trying to achieve. It needed a lot of explaining obviously and I had resolved the other issues but needed specific help with a couple of parts that had me stumped. Like the problems that VBA expects the US date format to be used, and my difficulty in handling an unnamed file programmatically.

Those issues aside, all seems OK now. BTW, I sometimes think I would rather be dabbling in MS Word’s VBA.

Thanks for the options you offered. However, I was not able to use a reference to ‘ActiveWorkbook‘ as it would have referenced the hidden workbook with the VBA code and I would also be unable to use the ‘Workbooks(Workbooks.Count).Name’ option because the user had not named the file at this stage of the code.

I have been able to incorporate the reference to the active workbook later in the code when switching between them to enable temporary filename storage in the hidden file.

Thanks, Leigh

Brooke, the users are fairly traditional and I would have had a lot of resistance to changing the date format to anything other than ‘ddmmyyyy’ style. However, I appreciate your suggestion regarding the date order when viewed in Windows Explorer but I will have to save it for another time.

In the same vein, I am encouraging users to consider the most recently used (date) order when the File | Open command is used. This way they will have their most recently used files sitting either at or near the top of the file list.

Thanks for your suggestion, Leigh

Hi Jefferson

No, it was not a matter of timing, my problem was how to capture the user’s filename. A search of the Net came up with the following solution. (This same site also had an answer to a corrupted XL workbook problem I experienced last month.)

http://www.erlandsendata.no/english/vba/fo…efoldername.htm%5B/url%5D

With suitable modifications to the code I was able to get it to do exactly what I needed including break it up into two components (date and category). One of the modifications was to the reference to ‘ThisWorkbook’. This had to become ‘Workbooks(2)’ so that the filename was not of the hidden file that contains the VBA code.

Thanks, Leigh

I have only noticed that this occurs with CSV files. They are created by a server application and then opened in MS Excel 97 with the problem of ‘conversion’ to an unwanted format that cannot be converted back (?)

Is this true? Leigh