Saving excel data to load into word

[TJA]

Please help....

I wrote this macro in excel exporting excel data so it can be loaded into
word document. It is not working... I can't get it to debub.

Thank you


Sub File_Export()
'
' File_Export Macro
' Export Style Worksheet to Word
'
' Keyboard Shortcut: Ctrl+Shift+F
'
'Export file for MS Word
'

Dim Filename As String
Dim NumRows As Long, NumCols As Integer
Dim r As Long, c As Integer
Dim Data
Dim ExpRng As Range

ThisWorkbook.Worksheets("person").Activate
Set ExpRng = Range("A5:N30")
NumCols = ExpRng.Columns.Count
NumRows = ExpRng.Rows.Count
Filename = "f:\foundations\excel_program\styles.txt"
Open Filename For Output As #1
For r = 1 To NumRows
For c = 1 To NumCols
Data = ExpRng.Cells(r, c).Value
If IsNumeric(Data) Then Data = Val(Data)
If IsEmpty(ExpRng.Cells(r, c)) Then Data = ""
If c <> NumCols Then
Write #1, Data;
Else
Write #1, Data
End If
Next c
Next r
Close #1
ThisWorkbook.Worksheets("blank").Activate
End Sub

 

[JLatham]

Your code works just fine on my system (Excel 2003). What error are you
getting when you try to run it?

You say you can't get it to Debug? If you're using Debug | Compile VBA
Project and nothing seems to be happening, that just means that the process
has not detected any obvious errors in syntax.

I might have written it slightly differently, as show below, but that's just
me. First I'd declare a new integer variable, newFileNum, to hold an
available file number obtained from the system (see code), and I would have
written the tests for cell contents to test for the IsEmpty() state first,
but other than that, it looks like code that should give you the results you
want. As I said, your code works for me as written.

My revised version of your code:
Dim Filename As String
Dim NumRows As Long, NumCols As Integer
Dim r As Long, c As Integer
Dim Data
Dim ExpRng As Range
Dim newFileNum As Integer ' added variable

ThisWorkbook.Worksheets("person").Activate
Set ExpRng = Range("A5:N30")
NumCols = ExpRng.Columns.Count
NumRows = ExpRng.Rows.Count
newFileNum = FreeFile() ' get available buffer #
Filename = "C:\Documents and Settings\All Users\Documents\styles.txt"
Open Filename For Output As #newFileNum ' changed
For r = 1 To NumRows
For c = 1 To NumCols
'changed testing for a cell's contents
If IsEmpty(ExpRng.Cells(r, c)) Then
Data = ""
ElseIf IsNumeric(ExpRng.Cells(r, c)) Then
Data = Val(ExpRng.Cells(r, c))
Else
Data = ExpRng.Cells(r, c)
End If
If c <> NumCols Then
Write #newFileNum, Data; ' changed
Else
Write #newFileNum, Data ' changed
End If
Next c
Next r
Close #newFileNum ' changed
ThisWorkbook.Worksheets("blank").Activate