Copy sheet to new instance of excel

[Dumb Blond]

I have a program that exports data to excel but doesn't load an excel add-in that i need. So, i need to open a new instance of excel which loads the add-in fine, and copy the sheet over to the new instance.

I have scoured the message boards for a solution and found ideas, but can't seem to get anything to work for me. Any help would be greatly appreciated.

 

[Dumb Blond]

It may, but because this is a bank licensed program, I doubt it is readily accessible and have no idea where to find it. All I have to work with is the txt data in an instance of excel that is missing the add-in . I can manually copy it over to a new instance, but it would be more efficient to copy and paste it in the new instance - if this is possible like you said.

 

[Dumb Blond]

Hi Andrew,

Do you have a solution or can you point me in the direction of a solution. I worked on this last week with no progress really, and I am trying to resolve it for Monday.

Thx

 

[Dumb Blond]

Could something like this work?


<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sub CopyValues()
Dim xlApp As Excel.Application
Dim Src As Range
Dim Dst As Range
Dim Vals() as Variant

Set xlApp = GetObject("c:\mypath\book1.xlsm").Application

Set Src = xlApp.ActiveSheet.Range("A1:A9")
Set Dst = Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("A1:A9")

Vals = Src
Dst.Value = Vals
End Sub</code>

 

[Andrew Poulsom]

Did you try it?

What are you doing to install your Add-In when open Excel?

 

[Dumb Blond]

Andrew,

This doesn't work. My other idea was to save the .txt file in a folder called MyData, then relaunch the same file in a new instance of excel - which I am struggling with trying to figure out how to use Shell command to launch excel and then the file - so the Add-In is there. The Add-In is not mine, it is DDE data download Add-In. I don't control it. Have you any ideas on how to do this?

 

[Jaafar Tribak]

I am not sure I fully understood the problem properly, but if you want to load the addin in the remote excel instance launched by the third program you could use something like this :

Sub RemoteLoadAddin()
    Dim xlApp As Excel.Application
    Const ADDIN_PATH_NAME As String = "Enter_Your_Addin_FullName_Here"
    
    Set xlApp = GetObject("c:\mypath\book1.xlsm").Application
    xlApp.Workbooks.Open ADDIN_PATH_NAME
End Sub

 

[Jaafar Tribak]

Also, why don't you open the addin manually in the excel instance launched by the third program ?

 

[Dumb Blond]

I can't locate the xlam file so i have contacted the program provider and waiting to hear back. I agree, this would be easier and i will try your code if they allow it.

 

[Jaafar Tribak]

I can't locate the xlam file so i have contacted the program provider and waiting to hear back. I agree, this would be easier and i will try your code if they allow it.

Based on what you previously described, the Addin is loaded when you manually start your own excel instance .. You can locate the addin file (ie: name and full path) from that excel instance very easily either via the user interface of via code

 

[Dumb Blond]

Name of add-in appears to be ilx.xla

Path is this ("C:\Program Files (x86)\Microsoft Office\Office14\XLSTART") This is what the provider said the name was and this is where I found it.

I don't know/understand what the addin_path_name refers to??

I am not sure I fully understood the problem properly, but if you want to load the addin in the remote excel instance launched by the third program you could use something like this :

Sub RemoteLoadAddin()
    Dim xlApp As Excel.Application
    Const ADDIN_PATH_NAME As String = "Enter_Your_Addin_FullName_Here"
    
    Set xlApp = GetObject("c:\mypath\book1.xlsm").Application
    xlApp.Workbooks.Open ADDIN_PATH_NAME
End Sub