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

  • Thread starter Thread starter ArharDal
  • Start date Start date
A

ArharDal

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
 
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
 
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
 
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.
 
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.
 
Back
Top