Run macro to different workbook that is closed

I've been searching and can't find anything on this. I have a workbook that I would like to run a simple macro to another closed workbook. Whenever I open my main workbook 'Inventory.xlsm' i want to update my data file called "S:/Data/stock.xlsx by putting a command button on my Inventory.xlsm file. 
Would it be easier to put the macro into the Inventory.xlsm or can I just call a macro name in stock.xlsx. I would like to avoid changing my 'stock.xlsx' to a stock.xlsm.  

My simple macro that would run on stock.xlsx is 

Sub Format()
    Range("Table2[stockstatus]").Select
    Selection.NumberFormat = "General"
    Selection.TextToColumns Destination:=Range("Table2[stockstatus]"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon _
        :=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, _
        1), TrailingMinusNumbers:=True
    ActiveWorkbook.Save
End Sub
Answer
Answer

You can't run a macro in a workbook that is closed.

You can't run a macro that affects a workbook that is closed.

 

You can put code in Inventory.xlsm that opens stock.xlsx, updates it and closes it, e.g.

 

Sub Format()
    Workbooks.Open "S:/Data/stock.xlsx"
    Range("Table2[stockstatus]").Select
    Selection.NumberFormat = "General"
    Selection.TextToColumns Destination:=Range("Table2[stockstatus]"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon _
        :=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, _
        1), TrailingMinusNumbers:=True
    ActiveWorkbook.Save
    Workbooks("stock.xlsx").Close
End Sub
Microsoft Excel MVP, Oxford, England. www.manville.org.uk

5 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated June 15, 2020 Views 2,336 Applies to: