Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Saving excel data to load into word
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Saving excel data to load into word
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 "TJA" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving an excel file in word | Excel Discussion (Misc queries) | |||
Saving Excel E Mails in a Word Folder | Setting up and Configuration of Excel | |||
saving work in word/excel onto a usb flashdrive | Excel Discussion (Misc queries) | |||
Excel should undo/redo after saving a file--just like Word. | Excel Discussion (Misc queries) | |||
Is anything needed if I only load Word & Excel from Office XP Sta. | Excel Discussion (Misc queries) |