coeurdange57
12-12-2017, 03:15 AM
Hi,
I create a macro in Excel for exporting data in Word:
Sub export_workbook_to_word()
Dim sheetName As String
Set obj = CreateObject("Word.Application")
obj.Visible = True
Set newobj = obj.Documents.Add
For Each ws In ActiveWorkbook.Sheets
sheetName = ws.Name
'Retrieve name of the Worksheet
newobj.ActiveWindow.Selection.TypeText sheetName
newobj.ActiveWindow.Selection.Style = ActiveDocument.Styles(-2)
newobj.ActiveWindow.Selection.TypeParagraph
ws.UsedRange.Copy
newobj.ActiveWindow.Selection.PasteExcelTable False, False, False
newobj.ActiveWindow.Selection.InsertBreak Type:=7
Next
newobj.ActiveWindow.Selection.TypeBackspace
newobj.ActiveWindow.Selection.TypeBackspace
obj.Activate
newobj.SaveAs Filename:=Application.ActiveWorkbook.Path & "\" & Split(ActiveWorkbook.Name, ".")(0)
End Sub It's working correctly. I would like to create a prompt for selecting the origin folder (with Excel files) and the destination folder (Word files created with the script).
Could you please help me to do that?
Regards
I create a macro in Excel for exporting data in Word:
Sub export_workbook_to_word()
Dim sheetName As String
Set obj = CreateObject("Word.Application")
obj.Visible = True
Set newobj = obj.Documents.Add
For Each ws In ActiveWorkbook.Sheets
sheetName = ws.Name
'Retrieve name of the Worksheet
newobj.ActiveWindow.Selection.TypeText sheetName
newobj.ActiveWindow.Selection.Style = ActiveDocument.Styles(-2)
newobj.ActiveWindow.Selection.TypeParagraph
ws.UsedRange.Copy
newobj.ActiveWindow.Selection.PasteExcelTable False, False, False
newobj.ActiveWindow.Selection.InsertBreak Type:=7
Next
newobj.ActiveWindow.Selection.TypeBackspace
newobj.ActiveWindow.Selection.TypeBackspace
obj.Activate
newobj.SaveAs Filename:=Application.ActiveWorkbook.Path & "\" & Split(ActiveWorkbook.Name, ".")(0)
End Sub It's working correctly. I would like to create a prompt for selecting the origin folder (with Excel files) and the destination folder (Word files created with the script).
Could you please help me to do that?
Regards