How can I change the content of embeded file in model via VB

[Former Member]

Hi,

may be you could help me. I have an embedded text file in my model. This file consist of some settings. I would like to read this settings later in my extension via VB script. There is a method file_obj.Content but it seems to deliver hexadecimal content of the file. Do you know how can I read via VB script the content of the embedded file? I would appreciate your answer!

Best regards

Peter

[former_member956200]

Hopefully this saves someone else some time...

You can do this all within vbScript within power designer as long as you convert back and forth between the binary and text formats.

I have included two very basic functions below.(They only trap for the special characters for new line and tab) but it should provide a decent starting point.

Here is an example which will modify an embedded file object called "testing"

dim f,myNewContent

myNewContent = "Make the" +vbnewline +"file look like" +vbTab +vbTab +vbTab + "this."

for each f in ActiveModel.files
   if f.name = "testing" Then
   
      output f.Content
      f.Content = StringToHex(myNewContent)
      output f.Content
  
   end if  
next


Function StringToHex(inputTextString)
   dim i,currentCharacter
   for i = 0 to len(inputTextString)-1 step 1 'Single character
      currentCharacter = left(inputTextString,1) 'get the character
      inputTextString = right(inputTextString,(len(inputTextString)-1)) 'take it off the front of the string
      'Test for special character cases      
      if Asc(currentCharacter) = 13 OR Asc(currentCharacter) = 10 THEN 
         StringToHex = StringToHex + "0d0a" 'Od 0a is a new line character in hex, char(13) is carriage return char(10) is line feed
      elseif Asc(currentCharacter) = 9 THEN
         StringToHex = StringToHex + "09" '09 is a hex tab, char(9) is tab
      else
         StringToHex = StringToHex + Hex(Asc(currentCharacter)) 'add the converted tuple to the output
      end if
   next 'i  
End Function


Function HexToString(inputHexString)
   dim i, currentElement


   for i = 0 to len(inputHexString)-2 step 2 'Hex tuples
      currentElement = left(inputHexString,2) 'get the tuple
      inputHexString = right(inputHexString,(len(inputHexString)-2)) 'take it off the front of the string
      output currentElement & "#" & asc(currentElement)
      
      'Ignore when the returned hex string from file.Content has a line break in it 
      if Asc(currentElement) = 13 OR Asc(currentElement) = 10 THEN 
         'do nothing
      else
         'process the element
         HexToString = HexToString + Chr("&H" & currentElement) 'add the converted tuple to the output
      end if      
   next 'i  
End Function

[former_member200945]

You need save the file as external type. Then use Excel open the file to modify its content.

Then save the file. Then create new file object and use LoadFileAsEmbedded

   '1) Find  the file in the model.  Say you have File_1

    Set model=ActiveModel

    Set file=model.findChildByName("File_1", cls_FileObject)

    output file.name

    '2) Save the file as external. Note SaveFile create a folder called File_1. The excel file is in it

    file.SaveFile("D:\temp\File_1")

    file.delete     'remove the file

    '3) Open the excel file  and modify the content.

    Set excel_obj = Createobject("Excel.Application")

    Set workbook = excel_obj.Workbooks.Open("D:\temp\File_1\File_1.xlsx")

    'write update code...

    '...

    '...

    ' Save the file

    excel_obj.ActiveWorkbook.SaveAs  "File_1.xlsx"

    '4) Create a new file object

    file_obj=model.Files.createNew()

    file._obj.name="File_1"

    file_obj.extension="xlsx"

    file.obj.LoadFileAsEmbedded("D:\temp\File_1\File_1.xlsx")