Can anyone give me a simple example of how to read text/data from an excel file in VBA? I only need data from a few selected cells.
Also, what is the newline/carriage return command when writing text to Word in VBA? I know is C++ it's \n or something. Thanks in advance.
Here is a macro that contains enough examples that I think you can get what you need. (It also creates a new Word file.)
Sub MakeWordFile()
' Creates Word document of Auction Items using Automation
Dim WordApp As Object
' Start Word and create an object
Set WordApp = CreateObject("Word.Application")
With WordApp
.Documents.Add
End With
' Determine the file name
SaveAsName = ThisWorkbook.Path & "" & "Auction Catalog.doc"
' Sort Worksheet into proper catalog order
Sheets("Items").Select
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, _
Key1:=Range("C2"), Order3:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2").Select
' Information from worksheet
Set Data = Sheets("Items").Range("A1")
' Cycle through all records in Items
Records = Application.CountA(Sheets("Items").Range("Title"))
' Records = 7
For i = 2 To Records
' Update status bar progress message
Application.StatusBar = "Processing Record " & i & " of " & Records
' Assign current data to variables
Item1 = Data.Offset(i - 1, 0).Value
Item2 = Data.Offset(i - 1, 1).Value
Title = Data.Offset(i - 1, 2).Value
Descript = Data.Offset(i - 1, 3).Value
' Send commands to Word
With WordApp
With .Selection
.TypeParagraph
.Font.Size = 12
.Font.Bold = True
.ParagraphFormat.Alignment = 0
.TypeText Text:=Item1 & Item2 & ". "
.Font.Underline = True
.TypeText Text:=Title
.Font.Bold = False
.Font.Underline = False
.TypeParagraph
.TypeText Text:=Descript
.TypeParagraph
End With
End With
Next i
' Save the Word file and close it
With WordApp
.ActiveDocument.SaveAs FileName:=SaveAsName
.ActiveWindow.Close
' Kill the object
.Quit
End With
Set WordApp = Nothing
' Reset status bar
Application.StatusBar = ""
MsgBox "Auction Catalog.doc was created and saved in " & ThisWorkbook.Path
End Sub
thomach, your codes helped me out a bit but I have another questions. If I'm in a MSword macro, how do I open an Excel file and copy a selection over to MSword? Or at least assign it to a variable in MSword macro?
I'm afraid I'v never worked from the MS Word VBA side. I would assume it's similar, but obviously don't know.
But remember that once you have opened the Excel file and are working in it you need to use Excel VBA commands, just as you need to use the WORD-specific commands while working in the Word document.
Hopefully someone else can give you a real answer.
Is VBA HELP in WORD of any use?
Thanks for all your help thomach. Anybody worked with MS Word VBA that can give me some pointers? Any other sites that might provide me with an answer?
Don’t have an account yet? Register yourself now and be a part of our community!
