Archived

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

Programming stuff for Excel - UPDATED, need still help

This topic is 5578 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)

• 23
• 10
• 19
• 15
• 14