Problem reading text from range using offset and printing to text file

[simeon]

Hi,

I am problems writing a VB script in Excel 2003 to read through the individual cells in an range, extract the text using offset and write the same text to another textfile (this is a precursor to wrting a macro to reformat the text for another application).

What I have is spreadsheet that contained a named range called "TestMethods" and is 14 rows by 8 columns. I can successufully count the number of rows to create a For ... Next loop to go through the 14 rows, but currently it's just writing blanks.

this is what the code looks like:

Sub OpenTxtfile()
Dim oFile As Integer, NoofMethods As Integer, Rw As Integer
Dim ObjDefnTable

Set ObjDefnTable = Worksheets("Mapping").Range("TestMethods")

oFile = FreeFile()
Open "C:\Backup\ShopITv60.sdt" For Output As oFile

Windows("SDT_Gen_Example.xlsm").Activate

' Determine no of rows to read through (incl hdr row for now)
NoofMethods = ObjDefnTable.Rows.Count

For Rw = 2 To NoofMethods Step 1

Print #oFile, "Test Method Row " & Rw & ObjDefnTable.Offset(Rw, 2).Text
​

Next Rw

Close #oFile

End Sub
​

Any advice will be much appreciated.

Thanks,

Simeon

 

[simeon]

For those of you who are interested I realised that I was using the wrong object type to extract the cell value

I needed to use the "Cells" object. Therefore the print statement should look like:

Print #oFile, "Test Method Row " & Rw & Worksheets("Mapping").Range("TestMethods").Cells(Rw, 1).Text

 

[offsetprinting]

Using the Super Bowl as a rallying point to focus on hunger, screen printing
Chinese Printing, Magazine Offset Printing, Sheetfed Printing, Excellent Customer Service Printing In China