[Solved] VBA: write a line from Excel to a text file

  • I open a file using "job" as my file handler, but no other 'sub' can see the
    file handler.
    The debugger stops with the error "object required"

    N.B. I have missed out the code that reads in the data file.

    Public Sub make_job_file()
    ' create GEO_FILE
    Dim fso, f1, job
    Const ForWriting = 2
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CreateTextFile ("geo_job.job")
    Set f1 = fso.GetFile("geo_job.job")
    Set job = f1.OpenAsTextStream(ForWriting, True)
    End Sub

    Sub jobname()
    job_name = InputBox("input Job Name", "Job Name", job_name, 100, 1)
    job.WriteLine "51=" + job_name
    Job_date = InputBox("input correct date", "Job Date", Job_date, 100, 1)
    job.WriteLine "51=" + Job_date
    End Sub

    Sub station()
    job.WriteLine "2=" + Cells(row, B) ' AT STATION
    job.WriteLine "3=" + Cells(row, H) ' INSTRUMENT HEIGHT
    job.WriteLine "21=0.0000" ' DUMMY RO ANGLE NOT USED
    End Sub

    Can you spot where I have gone wrong.
    Please don't tell me one sub routine can't see another subroutine's variables.

    Julian B

  • Hi Julian,

    I did not completely review you code yet, but as to the last line in your post - about one sub seeing another subs variables....

    The variables that you want to share between subs must be declared "Public" at the beginning of the module in the general section before the actual code of the subs starts - - if not declared Public, variables are only visible within the sub that creates them.

    Look up the Public statement in the VBA help files for more info.

    Hope this helps


  • I have not tested your code either but you could try to publicly declare the variables you need to us publicly declare your variables as Rem has sudjested eg

    Public fso, f1, job

    then you can call from any sub ex

    Sub CallFso()
    End sub

    Sub Calc()
    End sub



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!