CODEWITHSUNDEEP
vba
kassprek
kassprek

Reputation: 1007

How to get string value from Sub procedure in VBA?

I need to save some string variable to a file in utf-8 encoded. I've got vba script which can retrive variable from another application.

Sub Main
    ChanNum = DDEInitiate("MacroEngine","MacroGetVar")
    Var$ = DDERequest$(ChanNum,"VariableFromOutside")
    DDETerminate ChanNum
    sContent = Var
    MsgBox sContent         
End Sub

That script can display sContent only, but i need to processing this string for further in next script as you see below

Sub saveUTF_File(FileUrl,sCode)
    ' save text into utf-8 format
    Set objStream = CreateObject("ADODB.Stream")
    With objStream
        .Open
        .Charset = "utf-8"
        .Position = objStream.Size
        .WriteText=sCode
        .SaveToFile FileUrl,2
        .Close
    End With
    Set objStream = Nothing
End Sub

FilePath = "C:\file.html"
saveUTF_File FilePath,sContent

But it is not wroking because sContent variable is not inherited from Sub Main procedure. My question is how I can do it? Thanks for advance for any help.

My code now is

    Function TextOutside() As Variant

    ChanNum = DDEInitiate("MacroEngine","MacroGetVar")
    Var$ = DDERequest$(ChanNum,"VariableFromOutside")
    DDETerminate ChanNum
    TextOutside = Var
    ' MsgBox TextOutside        

    End Function

    Sub saveUTF_File(FileUrl,sCode)

    Set objStream = CreateObject("ADODB.Stream")
    With objStream
    .Open
    .Charset = "utf-8"
    .Position = objStream.Size
    .WriteText=sCode
    .SaveToFile FileUrl,2
    .Close
    End With
    Set objStream = Nothing
    End Sub

    FilePath = "C:\file.html"
    sContent = TextOutside()
    saveUTF_File FilePath, sContent

This script below working without errors but unfortunatly don't want save the result to C:\file.html at all :( why?

    Global sContent

    Sub Main()
    ChanNum = DDEInitiate("MacroEngine","MacroGetVar")
    Var$ = DDERequest$(ChanNum,"VariableFromOutside")
    DDETerminate ChanNum
    sContent = Var
    MsgBox sContent         
    End Sub

    Sub saveUTF_File(FileUrl,sCode)
    ' save text into utf-8 format
    Set objStream = CreateObject("ADODB.Stream")
    With objStream
    .Open
    .Charset = "utf-8"
    .Position = objStream.Size
    .WriteText = sCode
    .SaveToFile FileUrl,2
    .Close
    End With
    Set objStream = Nothing
    End Sub

    Sub DoAll()
    Dim FilePath 
    '
    ' Main is called before here.
    '
    FilePath = "C:\file.html"
    saveUTF_File FilePath, sContent
    End Sub

Upvotes: 1

Views: 650

Answers (1)

jacouh
jacouh

Reputation: 8741

If I understand you, This may be a solution, I would also propose you to declare explicitly all variables wiht Dim keyword, let's use dirty global variable solution:

Global sContent

Sub Main()
  ChanNum = DDEInitiate("MacroEngine","MacroGetVar")
  Var$ = DDERequest$(ChanNum,"VariableFromOutside")
  DDETerminate ChanNum
  sContent = Var
  MsgBox sContent         
End Sub

Sub saveUTF_File(FileUrl,sCode)
  ' save text into utf-8 format
  Set objStream = CreateObject("ADODB.Stream")
  With objStream
    .Open
    .Charset = "utf-8"
    .Position = objStream.Size
    .WriteText=sCode
    .SaveToFile FileUrl,2
    .Close
  End With
  Set objStream = Nothing
End Sub

Sub DoAll()
  Dim FilePath 
'
' Main is called before here.
'
  FilePath = "C:\file.html"
  saveUTF_File FilePath, sContent
End Sub

You call DoAll() by a command button after calling Main.

Upvotes: 3

Related Questions