Hi,
I have an invoice template in word that I need to fill in with line information from an excel table and it's a very manual process. Is there some sort of macro for this? Thank you!
Hi,
I have an invoice template in word that I need to fill in with line information from an excel table and it's a very manual process. Is there some sort of macro for this? Thank you!
A quick check with 'google' ... see if this works for you.
Sub CopyTables()
Dim oWord As Word.Application
Dim WordNotOpen As Boolean
Dim oDoc As Word.Document
Dim oTbl As Word.Table
Dim fd As Office.FileDialog
Dim FilePath As String
Dim wbk As Workbook
Dim wsh As Worksheet
' Prompt for document
Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.Filters.Clear
.Filters.Add "Word Documents (*.docx)", "*.docx", 1
.Title = "Choose a Word File"
If .Show = True Then
FilePath = .SelectedItems(1)
Else
Beep
Exit Sub
End If
End With
On Error Resume Next
Application.ScreenUpdating = False
' Create new workbook
Set wbk = Workbooks.Add(Template:=xlWBATWorksheet)
' Get or start Word
Set oWord = GetObject(Class:="Word.Application")
If Err Then
Set oWord = New Word.Application
WordNotOpen = True
End If
On Error GoTo Err_Handler
' Open document
Set oDoc = oWord.Documents.Open(Filename:=FilePath)
' Loop through the tables
For Each oTbl In oDoc.Tables
' Create new sheet
Set wsh = wbk.Worksheets.Add(After:=wbk.Worksheets(wbk.Worksheets.Count))
' Copy/paste the table
oTbl.Range.Copy
wsh.Paste
Next oTbl
' Delete the first sheet
Application.DisplayAlerts = False
wbk.Worksheets(1).Delete
Application.DisplayAlerts = True
Exit_Handler:
On Error Resume Next
oDoc.Close SaveChanges:=False
If WordNotOpen Then
oWord.Quit
End If
'Release object references
Set oTbl = Nothing
Set oDoc = Nothing
Set oWord = Nothing
Application.ScreenUpdating = True
Exit Sub
Err_Handler:
MsgBox "Word caused a problem. " & Err.Description, vbCritical, "Error: " & Err.Number
Resume Exit_Handler
End Sub
Display More
Thank you, it says user-defined type not defined. What am I doing wrong?
Here's the data I'm working with, I need a macro to create an invoice for each line in the excel 220705_Initial_QuickBooks_Sync copy.xlsm220705_Sample_Invoice_in_Draft.docmile. Here's the invoice template I need to use.
Thank you, it says user-defined type not defined. What am I doing wrong?
What line of code is highlighted ?
Probably because there is a Function Routine missing, named "Function WordNotOpen() As Boolean "
Thanks for the help. I have recently started using Excel and I don't know much about it. I was looking for purple cv review online on google search and while searching for reviews online, I found your post too.
very good
Don’t have an account yet? Register yourself now and be a part of our community!