VBA Copy tables from excel to Wd
Hi! I am new to VBA and I have a question. I have the following code, with it to copy a table which has a header (A1 to F1) from each sheet (GestiuneCCS; AlimATM; RidDepAng) to a designated bookmark (1;2;3…) in Wd. It works “fine”, but in case I don’t have information in one table the macro brings in word whole table blank, and I don’t want that, I want it to bring only the Header of that table. Attach the macro. How can I correct the macro? Thanks
Code:
SubExportExcelDataToWordDocument()‘DimwdExcelApp As Application ‘Excel is the default library (optional)
Dim wdWordApp As Word.Application ‘Word app
Application.ScreenUpdating= False
‘Creating a new instance of Word
Set wdWordApp = New Word.Application ‘instantiate a new instance of Word 2010
WithwdWordApp
‘Making Word Visible on the screen
.Visible = True ‘iff false, document is invisible.
.Activate ‘ make it the top pane, bring it to the front.
””””””””””””””””””””””””””””””””””””””””””””””’
‘ create a new Word Document based on the specified template
””””””””””””””””””””””””””””””””””””””””””””””’
.Documents.Add “C:Usersstefan.georgescuDesktopTemplate fisa de esantionare –ver. 5-2019.dotm”
‘asbefore, copy the whole table from sheet to clipboard.
Worksheets(“GestiuneSSC”).Activate
Range(“A1”, Range(“A1″).End(xlDown).End(xlToRight)).Copy
.Selection.GoTowhat:=-1, Name:=”bookmark1” ‘ -1 means “wdgotobookmark”
.Selection.Paste ‘paste from the clipboard to the Word Doc.
‘****************
‘as before, copy the whole table from sheet to clipboard.
Worksheets(“AlimATM”).Activate
Range(“A1”, Range(“A1″).End(xlDown).End(xlToRight)).Copy
.Selection.GoTowhat:=-1, Name:=”bookmark2” ‘ -1 means “wdgotobookmark”
.Selection.Paste ‘paste from the clipboard to the Word Doc.
‘****************
‘as before, copy the whole table from sheet to clipboard.
Worksheets(“DepRidAngajati”).Activate
Range(“A1”, Range(“A1″).End(xlDown).End(xlToRight)).Copy
.Selection.GoTowhat:=-1, Name:=”bookmark3” ‘ -1 means “wdgotobookmark”
.Selection.Paste ‘paste from the clipboard to the Word Doc.
”””””””””””””””””””””””””””’
‘ Save WORD Document
”””””””””””””””””””””””””””’
Dim TheFileName As String
TheFileName = “C:Usersstefan.georgescuDesktopFisa.docx”
‘(SaveAsis for Office 2003 and earlier – deprecated)
.ActiveDocument.SaveAs2 TheFileName
‘replaces existing .doc iff exists
‘Close Documents and Quit Word
.ActiveDocument.Close ‘close .DOCx
.Quit ‘exit Word
End With
Application.ScreenUpdating= True
‘MEMORYCLEANUP
Set wdWordApp = Nothing ‘garbage collection
‘Set wdExcelApp = Nothing ‘OPTIONAL
End Sub
