VBA 'Newb' - Output to one FreeFile on multiple events?

[Rich Wallace]

Hi all,

I am placing some code that will need to create and modify a single file
during the 'lifecycle' of a worksheet and I'm curious if I can make a
reference to the one file outside of all of my internal procedure and
functions.

(Is this possible?)I.E.:
When a user opens a workbook, I grab the active sheet and create an XML
document based on data within the sheet:

------------------------Begin code snippet------------------------
Sub Workbook_Open
Call CreateXML("Root")
End Sub

Public Function CreateXML(sRootName as String) As Boolean

Dim oWorkSheet As Worksheet
Dim oWorkBook As Workbook

Dim sXmlFileName As String
Dim sDivisionName As String

Set oWorkBook = ActiveWorkbook
Set oWorkSheet = ThisWorkbook.Worksheets("Sheet1")

sXMLFileName = "C:\Test.XML"
sDivisionName = oWorkBook.Names("Division").Value

iFileNum = FreeFile
Open sXMLFileName For Output As #iFileNum

Print #iFileNum, "<?xml version=""1.0""?>"
Print #iFileNum, "<Sheet1>"
Print #iFileNum, "<Inventory><Division>"
Print #iFileNum, sDivisionName
Print #iFileNum, "</Division>"

CreateXML = True

End Function

Private Sub Worksheet_Change(ByVal Target As Range)
'Trap changed Cells and write them to #iFileNum
End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)

Print #iFileNum, "</Inventory>"
Print #iFileNum, "</Sheet1>"
If iFileNum > 0 Then Close #iFileNum

End Sub
------------------------End code snippet------------------------

Any ideas??

TIA
-Rich

 

[Rich Wallace]

Think I got it...


Private Sub Workbook_Open()

Open "C:\TestFile.txt" For Output As #1
Print #1, "Initial Create"
Close #1

End Sub

Private Sub TestWrite()

Open "C:\TestFile.txt" For Append As #1
Print #1, "Append"
Close #1

End Sub


Look good?

 

[Dick Kusleika]

Rich

Opening text files is pretty quick, so I think your method is fine. Here's
another way just for your information: Open the file in the Open event,
close it in the BeforeClose event, then you don't have to open and close it
in each of your procedures. So, in the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Print #fnum, "WorkbookClose"

Close fnum

End Sub

Private Sub Workbook_Open()

Dim fname As String

fnum = FreeFile
fname = "C:\Dick\Tester\Test.txt"

Open fname For Output As fnum

Print #fnum, "WorkbookOpen"

End Sub

And in a standard module

Public fnum As Long

Sub RunTest()

Print #fnum, "RunTest"

End Sub


Note that fnum is public, so that all of your procedure will have access to
it.

 

[Rich Wallace]

Thank you for the info sir!

Rich

Opening text files is pretty quick, so I think your method is fine. Here's
another way just for your information: Open the file in the Open event,
close it in the BeforeClose event, then you don't have to open and close it
in each of your procedures. So, in the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Print #fnum, "WorkbookClose"

Close fnum

End Sub

Private Sub Workbook_Open()

Dim fname As String

fnum = FreeFile
fname = "C:\Dick\Tester\Test.txt"

Open fname For Output As fnum

Print #fnum, "WorkbookOpen"

End Sub

And in a standard module

Public fnum As Long

Sub RunTest()

Print #fnum, "RunTest"

End Sub


Note that fnum is public, so that all of your procedure will have access to
it.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.