File number not available on entering in a module in an excel add-in
 

I have an excel file that contains that Main(). I have WriteLog(fnum,
message) function in a different excel addin file .xla. When I use
WriteLog to write to a already opened file I get "Bad file or number".
Apparently, the file number is not available in that module before
entering into the module in the addin.

Sub Main()
REPORT_FILE_NUMBER = FreeFile
Open ThisWorkbook.Sheets(1).Range("outputfile").value For Output
Access Write As #REPORT_FILE_NUMBER
Print #REPORT_FILE_NUMBER, "My message" '<== This works fine

WriteLog( REPORT_FILE_NUMBER, "My message" )

Print #REPORT_FILE_NUMBER, "My message" '<== This works fine also!!
Close #REPORT_FILE_NUMBER
End Sub

'This sub is in an excel addin. It works fine if not an excel addin but
a different module in the same file.
Sub WriteLog( Optional dest, msg As String )
Print #dest, msg '<=== I get error number 52, bad file name or
number here
End Sub

Please help. Thanks

File number not available on entering in a module in an excel add-in
 

If you have your looging routine in the .xla, you do not need the file
number. Just pass the text and let the .WriteLog take care of the rest.
In case I misunderstand the problem, values returned by FreeFile as not
machine unique. They are certainly Process independent but possibly Thread
independent. As such, a FreeFile value from one thread/process cannot be
used by another thread/process as it will either be invalid or point to a
different file.

NickHK

"ArharDal"
groups.com...
I have an excel file that contains that Main(). I have WriteLog(fnum,
message) function in a different excel addin file .xla. When I use
WriteLog to write to a already opened file I get "Bad file or number".
Apparently, the file number is not available in that module before
entering into the module in the addin.

Sub Main()
REPORT_FILE_NUMBER = FreeFile
Open ThisWorkbook.Sheets(1).Range("outputfile").value For Output
Access Write As #REPORT_FILE_NUMBER
Print #REPORT_FILE_NUMBER, "My message" '<== This works fine

WriteLog( REPORT_FILE_NUMBER, "My message" )

Print #REPORT_FILE_NUMBER, "My message" '<== This works fine also!!
Close #REPORT_FILE_NUMBER
End Sub

'This sub is in an excel addin. It works fine if not an excel addin but
a different module in the same file.
Sub WriteLog( Optional dest, msg As String )
Print #dest, msg '<=== I get error number 52, bad file name or
number here
End Sub

Please help. Thanks

File number not available on entering in a module in an excel add-in
 

Thanks much Nick. But I dont' see how the module in .xla addin would
run as a different thread or process. Also, I could have multiple files
open and I need to tell WriteLog() which file to write to and that's
why I need to pass in the file number to WriteLog(). -d

NickHK wrote:
If you have your looging routine in the .xla, you do not need the file
number. Just pass the text and let the .WriteLog take care of the rest.
In case I misunderstand the problem, values returned by FreeFile as not
machine unique. They are certainly Process independent but possibly Thread
independent. As such, a FreeFile value from one thread/process cannot be
used by another thread/process as it will either be invalid or point to a
different file.

NickHK

"ArharDal"
groups.com...
I have an excel file that contains that Main(). I have WriteLog(fnum,
message) function in a different excel addin file .xla. When I use
WriteLog to write to a already opened file I get "Bad file or number".
Apparently, the file number is not available in that module before
entering into the module in the addin.

Sub Main()
REPORT_FILE_NUMBER = FreeFile
Open ThisWorkbook.Sheets(1).Range("outputfile").value For Output
Access Write As #REPORT_FILE_NUMBER
Print #REPORT_FILE_NUMBER, "My message" '<== This works fine

WriteLog( REPORT_FILE_NUMBER, "My message" )

Print #REPORT_FILE_NUMBER, "My message" '<== This works fine also!!
Close #REPORT_FILE_NUMBER
End Sub

'This sub is in an excel addin. It works fine if not an excel addin but
a different module in the same file.
Sub WriteLog( Optional dest, msg As String )
Print #dest, msg '<=== I get error number 52, bad file name or
number here
End Sub

Please help. Thanks

File number not available on entering in a module in an excel add-in
 

Yes right. But I just thought it would be inefficient. I ended up
creating a FileStream object globally and pass it around for writing
operations. That actually works pretty nice and I guess its more
*trendy*. Thanks for your help.

Tim Williams wrote:
Wouldn't it be easier to let the logging code take care of opening/closing the files?
In that case you could just pass the file path instead of the FreeFile number.

--
Tim Williams
Palo Alto, CA


"ArharDal" wrote in message oups.com...
Thanks much Nick. But I dont' see how the module in .xla addin would
run as a different thread or process. Also, I could have multiple files
open and I need to tell WriteLog() which file to write to and that's
why I need to pass in the file number to WriteLog(). -d

NickHK wrote:
If you have your looging routine in the .xla, you do not need the file
number. Just pass the text and let the .WriteLog take care of the rest.
In case I misunderstand the problem, values returned by FreeFile as not
machine unique. They are certainly Process independent but possibly Thread
independent. As such, a FreeFile value from one thread/process cannot be
used by another thread/process as it will either be invalid or point to a
different file.

NickHK

"ArharDal"
groups.com...
I have an excel file that contains that Main(). I have WriteLog(fnum,
message) function in a different excel addin file .xla. When I use
WriteLog to write to a already opened file I get "Bad file or number".
Apparently, the file number is not available in that module before
entering into the module in the addin.

Sub Main()
REPORT_FILE_NUMBER = FreeFile
Open ThisWorkbook.Sheets(1).Range("outputfile").value For Output
Access Write As #REPORT_FILE_NUMBER
Print #REPORT_FILE_NUMBER, "My message" '<== This works fine

WriteLog( REPORT_FILE_NUMBER, "My message" )

Print #REPORT_FILE_NUMBER, "My message" '<== This works fine also!!
Close #REPORT_FILE_NUMBER
End Sub

'This sub is in an excel addin. It works fine if not an excel addin but
a different module in the same file.
Sub WriteLog( Optional dest, msg As String )
Print #dest, msg '<=== I get error number 52, bad file name or
number here
End Sub

Please help. Thanks