PDA

View Full Version : Copy worksheet information to Word using VBA in Microsoft Excel



thedark123
06-11-2006, 11:13 PM
I need to do a macro to copy some worksheet information to a new word document:

This is what I will do for my script:

1)When I click on a button in excel, a popup-box will appear and I can specify the directory or location of where my files is (In this case it is a .xls file inside a folder, lets call it 'test'
C:\Documents and Settings\Administrator\Desktop\test)

http://i6.photobucket.com/albums/y226/thedark123/directory.gif

2 )After specifying the directory, immediately a new folder will be created inside 'test' folder depending on that .xls file, If the .xls file is call "CU Maintain Addressee Test Data V0.01.000.xls" then the folder will have the same name as it "CU Maintain Addressee Test Data V0.01.000"

3)Now the script will continue into opening that .xls file (suppose to have a loop to read more .xls files if there are more then 1 of such kind of file in 'test' folder) and read the information in the selected cells and copy it into a new word document.

The new word document will then appear in "C:\Documents and Settings\Administrator\Desktop\test\CU Maintain Addressee Test Data V0.01.000". This process will carry on if there are more of such kind of .xls file. In the new word document there will be a format inside which all subsequent page will follow. The values copied from excel are supposed to fall into the correct table cell in word.

http://i6.photobucket.com/albums/y226/thedark123/exceltable.gif

copied over to

http://i6.photobucket.com/albums/y226/thedark123/wordformat.gif

So in this word document, this format/template already exist, how to corresponse the information from excel cells to word document cells?


4)After everything have been done properly a pop-out box will appear, "Process have been completed successfully"

5)End

thedark123
06-11-2006, 11:17 PM
Here is my coding up till now...

Private Sub CommandButton1_Click()
Const wdCollapseEnd = 0
Const wdPageBreak = 7
Const wdNormalView = 1
Dim wb As Workbook
' Dim ws As Worksheet
Dim x

' Prompt the user for the folder to list.


Dim newfol As String

'newfol = (Range("A1").Text)

newfol = testingonly

ChDir "C:\Documents and Settings\Administrator\Desktop\"

On Error Resume Next

MkDir (newfol)



' Prompt the user for the folder to list.
x = InputBox("What folder do you want to list?" & Chr$(13) & Chr$(13) _
& "For example: C:\My Documents")

If x = "" Or x = " " Then
Response = MsgBox("Please Enter a Directory Location" _
& Chr$(13) & Chr$(13) & _
"To enter directory location, click No." & Chr$(13) & _
"To Exit, click Yes.", vbYesNo)
If Response = "6" Then
End If
Else

' Search Drive
ChDrive "C"
ChDir x

On Error Resume Next

' Place .xls files into worksheet and tabulate data
outrow = 2
filess = Dir("*.xls")

While Not filess = ""
Workbooks.Open Filename:=filess, UpdateLinks:=False


' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library

' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
Dim wdApp As Object, wdDoc As Object, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."
Set wdApp = CreateObject("Word.Application")

Set wdDoc = wdApp.Documents.Add
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
Application.CutCopyMode = False
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
' insert page break after all worksheets except the last one
If Not ws.Name = Worksheets(Worksheets.Count).Name Then
With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
.InsertParagraphBefore
.Collapse Direction:=wdCollapseEnd
.InsertBreak Type:=wdPageBreak
End With
End If
Next ws

Set ws = Nothing

Application.StatusBar = "Cleaning up..."
' apply normal view
With wdApp.ActiveWindow
If .View.SplitSpecial = wdPaneNone Then
.ActivePane.View.Type = wdNormalView
Else
.View.Type = wdNormalView
End If
End With
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False

filess = Dir()



Wend

End If



End Sub

thedark123
06-12-2006, 01:45 AM
hmm anyone happen to know how to cont my script? greatly appreciated..
urgent and rushing!

thedark123
06-13-2006, 04:06 AM
should i use bookmark instead?