VB script: how to assign metadata file with excel object?

[Former Member]

Hi all,

I have the following problem:

1. I created an extension with a test method.

2. This method:

    => creates a metadata file (embedded, xlsx format). It works fine.

    => creates an excel object and fill it with some information of my model. It works fine as well.

    => the question now is: how can I associate the metadata file created in the first step with an excel object from second step (that is only in memory).

I attached the method in the text file. May be you find the right solution for my problem.

Best regards

Peter

extension_method_create_file.txt.zip

[former_member200945 · Accepted solution]

This is the definition of function LoadFileAsEmbedded:

So it can only load external file.

You need temporarily save the excel file to local driver so it becomes external file. Then use above function to load the file.(note use file name).

At last delete the file.

[Former Member · Accepted solution]

Thank you Phillip,

it works now. The Problem was that this function requires a file as a string and not as an object.

Wonderful. Many thanks!

Peter

[former_member787772]

Good day to you.

I understand it that your question was placed a long ago. Netherless, I'm faced with the task like yours right now.

And I must admit your script helped me.

I've solved the problem which you encountered by doing the following.

1 step you created a File in model.

2 step to create a Excel application as object.

3 step to open, created by you file in model, as you seemed to have done:

dim wbk ' (for workbook)

dim objExcel '(for Excel application)

dim file_obj ( for model.Files.CreateNew())

set objExcel = GetObject (, "Exel.Application") ' or set objExcel = CreateObject("Excel.Application")

objExcel.visible = True

file_obj.OpenFile

set wbk = objExcel.ActiveWorkbook

with wbk

'do whatever you want in Excel

'.worksheets(1).Range ("A1:G1') = Array (''''')

'~~~~~~~~~~~~~~~~~~~~~~~~

'and most usefull way to finish with it

wbk.save

wbk.close

That's all. you have all necessary changes in your model's file saved.

Why not? It works for me and quite easy way to modify your file without even saving on a drive and afterwards using LoasAsEmbeddedFile (which for me did not work....)

You just open it, modify, save and close you model's file as many times as you need.

Good luck!

[Former Member]

Hi Philipp,

it does not work. There is no error if I apply this formel but if I open the file from meta model the Excel file seems to be empty. Definitely there are some simple meta data in.

[former_member200945]

I create a PDM, then  copy your code and run it through Tools->Execute Commands->Edit /Run Script. I can see the code create Files folder and in the folder there is an excel file.

I double click the file and it somehow opens twice. One has one line input and other is empty.

[former_member200945]

Sounds like you want to create Files object  inside the model and  associate

the object with an excel object.

Try this:

Instead of calling

ActiveModel.Files.Add(excel_obj)

You call

file_obj.LoadFileAsEmbedded(excel_obj)