Jump to content
  • Advertisement

Archived

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

Sand_Hawk

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.

If you intended to correct an error in the post then please contact us.

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 this post


Link to post
Share on other sites
Advertisement
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 this post


Link to post
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 this post


Link to post
Share on other sites
welcome to the business world.

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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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)



I suggest that your dad locks the work sheet or saves it read only.


Share this post


Link to post
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 this post


Link to post
Share on other sites

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!