PDA

View Full Version : Solved: Run Macro on Specified File and Worksheet



flea333
06-28-2010, 05:18 AM
I am importing data from another excel file into my file then I have a macro that adds/deletes rows/columns where there are references to those columns and data. However my macro puts the data in the standard format that the worksheet refers to. When you change these columns the reference formulas all update to track it but I do not want them to.

I need to either:

Prevent the reference formulas from updating until I'm done formatting the data I imported.

OR

Run a macro in the existing file on a different file (which I already have a reference to the workbook as wBook and sRef as the worksheet).

GTO
06-28-2010, 11:43 AM
Hi Flea,

Maybe just me, but your question seems unclear. Could you zip a before and after wb and explain what's wanted?

Mark

PS - .xls format

flea333
06-28-2010, 03:23 PM
Unfortunately its work data that is proprietary, so it would be difficult.

Let me explain:

The source file contains a worksheet with data I reference on different worksheets in that excel file. I have a macro that allows me to "import" updated data from another excel file that overwrites the data in the source spreadsheet. I need to run a macro on this updated data to put it in the format needed for my source spreadsheet. This requires adding columns.
Since I reference the columns in the source spreadsheet, if I run a macro on it to move columns around, the linked formulas in the other worksheets will move with it which causes problems.

The way I used to manually do this was to run the macro from the source spreadsheets macro file on the spreadsheet with the new data, then copy/paste that into the source spreadsheet and it would all update.
I was this to be automatic.

Aussiebear
06-28-2010, 10:21 PM
Why not trigger your updating macro after you have finished formatting

flea333
06-30-2010, 02:39 PM
The macro is the thing that formats the data.
I want to import data from a spreadsheet and modify it, adding columns etc.
So the question is, do I do it before I copy it into my spreadsheet or after?
I currently do the modifying after copying into the spreadsheet which then causes all my reference links to the cells to be changed.

mfegyver
07-01-2010, 08:44 AM
Hi Flea, not sure if I understood, but normally I work with a databas and forms that update my DB, so to run a macro in the DB to execute task in another file I use:

ActiveWindow.ActivateNext

important: it must be only these 2 excel windows openned each time.

RonMcK3
07-01-2010, 09:34 AM
How about turning of calculations for the target worksheet when your program begins, leave it off while it does all of whatever you're doing, and -- then, as it's wrapping up and finishing, re-enable calculations?

Cheers!

jwilder1
07-02-2010, 02:06 AM
My suggestion would be to copy your source data to a temporary worksheet as values, update the temp sheet from your import macro, and then copy back to your source sheet and delete the temporary sheet. That would prevent links from moving.

flea333
07-06-2010, 07:59 PM
Calculations are off...this doesn't prevent excel from updating links

flea333
07-07-2010, 04:18 PM
Isn't there a way to run a macro on another workbook? The only way I know how is to have the workbook activated then run the macro.

flea333
08-25-2010, 01:24 PM
I used this code and referenced wBook.

Fname = Application.GetOpenFilename("Excel Files (*.xls; *xlsx), *.xls; *.xlsx")

CalcSet False 'my own function to turn off calculations

Filename = CStr(Fname)
Filename = Split(Filename, "\")(UBound(Split(Filename, "\"))) 'Extract file name from path
If Fname <> False Then
On Error Resume Next
Set wBook = Workbooks(Filename)
If wBook Is Nothing Then
Workbooks.Open Filename:=Fname, ReadOnly:=True
Set wBook = Workbooks(Filename)
'Instead of opening just grab data out of file
Else
If Not wBook.Saved Then
If MsgBox("This file has been modified since open, wanna continue with the macro anyway?", vbYesNo, "File Open and Modified") = vbNo Then
GoTo CloseUp
Exit Sub
End If
End If
End If