#### Archived

This topic is now archived and is closed to further replies.

# Programming stuff for Excel - UPDATED, need still help

This topic is 5490 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

UPDATE: Ok, I just wrote up a VBA script to handle it. It works fine, as long as I use fixed ranges. And that's bad. Is there a way with VBA to find out the number of rows/columns for the current sheet? I can already change worksheet etc. This is my code:

Sub FixPaths()
For Each WS In Worksheets
Worksheets(WS.Name).Activate

ActiveSheet.Unprotect
Range("A1").Select

Call FixSheet(strSearchText, 10, 10) ' Hardcoded ranges
Next
End Sub

********************** Original message **************** OMG! I am at my dads work doing system administration. After the server problems I encountered I encountered a second problem. They're using the financial software package "Exact for Windows". The app is sold in over 57 countries blah blah... It comes with an excel add-in. If the add-in isn't loaded when you load a sheet, all the links to the add-in are being molested by adding the text 'c:\windows\application data\somemore\shithere\EMAIN97.XLA'! in it. If I want to fix the sheets, it would take me at least 2 days removing the text. Can I write a program(VB/C++/VBA/Whatever language) that removes these links so I can use my better for other things? Sand Hawk ---------------- (Inspired by Pouya) [edited by - sand_hawk on February 12, 2003 6:02:35 AM]

##### Share on other sites
Excel(and the other Office apps) can be automated from any language that supports COM. The easiest route is probably to use VBA, since a VBA IDE comes bundled with Excel. But then again, that requires you to deal with BASIC...

"I know very well who Satan is: He is freedom. He is the uncontrolled, the incalculable, the antithesis of order and discipline, the antithesis of the legalism of outer space.... We know where a planet will be in twelve years, four months and nine days. But we don''t know where a butterfly will have flown one minute hence. Therefore the butterfly is of Satan."
-- Jens Bjørneboe

##### Share on other sites
When you open such a workbook it say "This workbook has links to other source". Click Update. If it says it can''t find the add-in, click "Browse", select the link, and then find the add-in for it.

If the add-in is loaded, but has changed directories, you can just do a global search & destroy for ''c:\windows\application data\somemore\shithere\EMAIN97.XLA''! and replace it with an empty string.

##### Share on other sites

I imagine alot of working people on these servers have to program around or using these apps.

use find and replace.

or create a new one.

##### Share on other sites
The problem is, if my dad gets the workbook back from someone else that has changed it, all the links are fubarred. Meaning, he has to fix them all. Some workbooks took my dad a week to build(Including all the functions used from the add-in) and then fixing all the links takes up alot of (precious) time. I remember that VB has the option of interfacing with Excel(Excel needs to be open for that though). Does anyone know how I can do that?

Sand Hawk

----------------

(Inspired by Pouya)

##### Share on other sites
open VBA in excel, browse the objects (from the view menu i think... could be wrong), choosing excel objects.

click on an object - press f1- read the documentation

i use python with the win32com package to interface with com objects in excel

##### Share on other sites
quote:
Original post by Sand_Hawk
...Excel(Excel needs to be open for that though)...

You don''t need to have excel open to use VB with an excel workbook.

You just have to do something like this (Trying to do this from memory so it may not be 100% accurate):
1st make a reference to Excel in the VB Project. (or you can do Set oXL = CreateObject("Application.Excel") )

Dim oXL as Excel.Application
Dim oBook as Excel.Workbook
Dim oSheet as Excel.Worksheet

If Excel is open you can do this:
oXL.GetObject(,"Excel.Application") ''** if Excel is open

if Excel is not open, you should do this:
oXL.CreateObject("Excel.Application") ''** If Excel is not open

Ideally, you should have a subroutine that determines if excel is open and if it is, use that instance of excel, otherwise open a new instance. You should be able to figure out the rest of it from here. The objects are pretty easy to figure out.

~"What''''s this red button do?"

##### Share on other sites
YAY!! That''s what I needed! Thanks for the tip psyberia.

Sand Hawk

----------------

(Inspired by Pouya)

##### Share on other sites
quote:
Original post by Sand_Hawk
The problem is, if my dad gets the workbook back from someone else that has changed it, all the links are fubarred. Meaning, he has to fix them all. Some workbooks took my dad a week to build(Including all the functions used from the add-in)

##### Share on other sites
He can''t, because the people that need these workbooks, sometimes make minor changes in it. So, saving it read only isn''t going to work out. I am currently looking into the excel stuff I was given.

Sand Hawk

----------------

(Inspired by Pouya)

##### Share on other sites
I''d be honest with you maybe excel is not the way to go for this.

maybe someone needs to lay down the law on what changes can and cannot be made.

if people are changing paths maybe there should be a fileshare drive where all these paths to point to.

sounds to me as if people have different drives mapped on there system.

if all users had the same drives map to the same letter this would''nt be a problem for you.

you could always code in a way for people to add links using

\\server\servershare\directory type thing.

P.S are people sending around a file or a shortcut?

##### Share on other sites
The file is being sent by mail to the main office.

sand Hawk

----------------

(Inspired by Pouya)

##### Share on other sites
quote:
Original post by Themonkster
I''d be honest with you maybe excel is not the way to go for this.

maybe someone needs to lay down the law on what changes can and cannot be made.

if people are changing paths maybe there should be a fileshare drive where all these paths to point to.

sounds to me as if people have different drives mapped on there system.

if all users had the same drives map to the same letter this would''nt be a problem for you.

you could always code in a way for people to add links using

\\server\servershare\directory type thing.

P.S are people sending around a file or a shortcut?

This would not solve the problem. The problem is, Excel remembers the exact path to an add-in and insist on using it. e.g. Make a workbook and use some add-in functions in it. Try using it on a PC with office installed on C:, and another with it installed on D:. An Office2k upgrade from Office97 will have different paths from an Office2k fresh-install.

This is a massive headache for me at work all the time. If anyone knows "the way" to beat Excel into submission, please let me know.