Solved: Run Macro on Specified File and Worksheet

[flea333]

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]

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]

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]

Why not trigger your updating macro after you have finished formatting

[flea333]

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]

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]

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]

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]

Calculations are off...this doesn't prevent excel from updating links

[flea333]

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]

I used this code and referenced wBook.

[VBA]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
[/VBA]