Run macro from newly created workbook

Hi Guys,

i am writing SQLs using ODBC connection.
And to avoid leak memorys the best is to connect to another workbook and use it as source.

1. I have a current workbook and i am runing macro from this.
2. Macro is creating new workbook and catching differences between variables.
3. Macro will be also catching differences between tables using SQL and odbc.

Problem is that when i am creating new workbook i would like to close current one (with macro) and run macro with odbc from newly created (new one).
From this newly created i could connect to my current workbook using odbc connection.

It is possible to move the code from current workbook and run it from newly created?

Best,
Jacek

Hi,
May be you can try to save your file with macro as .xlam (Add-on), then apply this Add-on. This way you could run your code from every excel file you open.

Hi kasan.

I can't do this because workbook is read by java later.

What is this .xlam file?

I think that here creating simple temporary copy can be better solution...

Best,
Jacek

Open your file with macro code, Save As - choose type "Add-on (.xlam)", Open new excel file, then Developer tab - Add-Ins. Choose just saved file (check it).
Now this macro code will be avaliable in all excel files.
This can solve our issue when you create new file and close original. Your code still will be avaliable in this newly created file.

This is how I usually do.
Use SaveCopyAs to make a copy of the workbook that you are working from and read the data from there.
e.g

    Dim fn As String
    With CreateObject("Scripting.FileSystemObject")
        fn = ThisWorkbook.Path & "\" & Replace(.GetTempName, ".tmp", "." & .GetExtensionName(ThisWorkbook.Name))
    End With
    ThisWorkbook.SaveCopyAs fn
    'Read from fn
    '
    Kill fn  '<- don't forget to delete the file at the end

thank you Guys,

i will use savecopy as method.

Best,
Jacek