What is the syntax to write to another workbook?

'to retrieve data
Workbooks("<workbook file name>").Worksheets("<sheet name>").Range("<your range>").Value
Workbooks("<workbook file name>").Worksheets("<sheet name>").Cells(<row number>, <column number>).Value

'to set data
Workbooks("<workbook file name>").Workbooks("Worksheets("<sheet name>").Range("<your range>") = <your value/data>
Workbooks("<workbook file name>").Worksheets("<sheet name>").Cells(<row number>, <column number>).Value = <your value/data>
'examples
Workbooks("example.xlsm").Worksheets("Example").Range("A1:A4") = "hello"
Workbooks("book3.xlsx").Worksheets("Example").Range("D1") = "world"
Workbooks("book3.xlsx").Worksheets("Example").Cells(2, 4).Value = "goodbye"
Advertisements

What is the syntax to write text files using FSO?

Why use FSO over Write Command method? FSO allows coders to work with drives, folders, files.

Note: Please enable Microsoft Scripting Runtime before running this code (click here).

‘example
Dim FilePath, CellData As String
Dim LastCol, LastRow As Long
Dim fso As FileSystemObject

Set fso = New FileSystemObject
Dim stream As TextStream

LastCol = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.UsedRange.Rows.Count

'create a textstream
Set stream = fso.OpenTextFile("<file path of txt file>\<txt file name>.txt”, ForWriting, True)
CellData = ""
For i = 1 To LastRow
    For j = 1 To LastCol
        CellData = ActiveCell(i, j).Value
        stream.WriteLine ("Location: " & i & ", " & j & "-->" & CellData)
    Next j
Next i

stream.Close

 

 

What is the syntax to write text files using Write Command?

Why use Write Command method instead of FSO?
Write command does not require user to add any references. However, it does not work with drives, files, folders.

Note: Please enable Microsoft Scripting Runtime before running this code (click here for article)

Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

LastCol = <last column you want to write>
LastRow = <last row you want to write>

FilePath = "<file path of txt file>\<txt file name>.txt"
Open FilePath For Output As #2
CellData = ""

For i = 1 To LastRow
    For j = 1 To LastCol
        CellData = <whatever you want to write> 'commonly Cells(i, j).Value
        Write #2, CellData
    Next j
Next i

Close #2

MsgBox ("Writing Complete")