locked
Macro moving data from Excel to Word and PDF file RRS feed

  • Question

  • There must be a straightforward way to move data from an Excel range to a table in Word, right?  Or from a pdf to an Excel file?  Or from a pdf to a Word document?  Or am I getting a little too far afield?  So far, in trying to move data from Excel to Word, I've managed to get the data to move, but it gets dumped at the top of the Word document instead of into the table that's waiting for it.  It's a simple range of two rows and 12 columns.  How do I get it to go where I want it to go?

    Here's what I've got so far:

    Sub pasterange()


          Dim MyRange As Object


          Dim WrdApp As Object, WrdDoc As Object
    Dim strMyFolderPath As String, strWrdFileName As String

    Range("b5:m6").Select
        Selection.Copy

    Set WrdApp = GetObject(, "Word.Application")
    If Err = 429 Then
        Set WrdApp = CreateObject("word.application") 'creates a Word application
        Err.Clear
    End If
    WrdApp.Visible = True
    Set WrdDoc = WrdApp.Documents.Open("My Book:ABCDE.docx")
    'WrdApp.Visible = True

          ' Range Example:
          MyRange.Collapse Direction:=wdCollapseStart
          MyRange.PasteSpecial

    End Sub

    Okay, I confess that now this doesn't work, either.  It's opening the Word document, but not pasting anything to the document like it used to do.  I'm getting Run-time error '91': Object variable or With block variable not set.

    There are clearly some lines of code that I am missing!  Can you help, please?

    BTW, I'm using a Mac running Excel 2011.

    Thanks,

    Robinsong

            
    Tuesday, July 7, 2015 11:19 PM

Answers

  • Yes, you can do all of that.  Please see these links for some ideas of how to get started.

    http://excel-macro.tutorialhorizon.com/vba-excel-add-table-and-fill-data-to-the-word-document/

    http://www.thespreadsheetguru.com/blog/2014/5/22/copy-paste-an-excel-table-into-microsoft-word-with-vba

    https://msdn.microsoft.com/en-us/library/office/gg508921.aspx?f=255&MSPPError=-2147217396

    Also, see this Excel VBA script to convert a bunch of Excel files (in a folder) to PDF files...

    Sub Convert_Excel_To_PDF()
        Dim MyPath As String, FilesInPath As String
        Dim MyFiles() As String, Fnum As Long
        Dim mybook As Workbook
        Dim CalcMode As Long
        Dim sh As Worksheet
        Dim ErrorYes As Boolean
        Dim LPosition As Integer
     
        'Fill in the path\folder where the Excel files are
        MyPath = "c:\Documents and Settings\shuerya\Desktop\ExcelFiles\"
     
        FilesInPath = Dir(MyPath & "*.xl*")
        If FilesInPath = "" Then
            MsgBox "No files found"
            Exit Sub
        End If
     
        Fnum = 0
        Do While FilesInPath <> ""
            Fnum = Fnum + 1
            ReDim Preserve MyFiles(1 To Fnum)
            MyFiles(Fnum) = FilesInPath
            FilesInPath = Dir()
        Loop
     
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
     
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
                On Error GoTo 0
     
                If Not mybook Is Nothing Then
     
                        
                        LPosition = InStr(1, mybook.Name, ".") - 1
                        mybookname = Left(mybook.Name, LPosition)
                        mybook.Activate
                        'All PDF Files get saved in the directory below:
                        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                            "C:\Documents and Settings\shuerya\Desktop\PDFFiles\" & mybookname & ".pdf", _
                            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                            :=False, OpenAfterPublish:=False
                            
                End If
                
                mybook.Close SaveChanges:=False
     
            Next Fnum
        End If
     
        If ErrorYes = True Then
            MsgBox "There are problems in one or more files, possible problem:" _
                 & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"
        End If
     
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = CalcMode
        End With
    End Sub
    

    Post back if you have more questions.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by L.Hl Monday, July 20, 2015 4:06 PM
    Thursday, July 9, 2015 2:47 AM

All replies

  • Hi,

    >>  Object variable or With block variable not set

    From what I can see, you dim a variable MyRange, but you didn’t set value for this object.

    >> BTW, I'm using a Mac running Excel 2011

    This is the forum for questions about Excel development on Windows Platform, for Excel develop questions on Mac, I will suggest you post the thread on Mac for Office, and you will get more professional and effective help there.

    Mac for Office : http://answers.microsoft.com/en-us/mac

    >> Or from a pdf to an Excel file?  Or from a pdf to a Word document?

    By the way, if have questions about get data from PDF using VBA, you may post your question in VBA form.

    VBA forum : https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    If you have more than one question, better to post these question separately, for more community members could be involved in these cases to help  you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by L.Hl Wednesday, July 8, 2015 9:20 AM
    Wednesday, July 8, 2015 9:07 AM
  • Yes, you can do all of that.  Please see these links for some ideas of how to get started.

    http://excel-macro.tutorialhorizon.com/vba-excel-add-table-and-fill-data-to-the-word-document/

    http://www.thespreadsheetguru.com/blog/2014/5/22/copy-paste-an-excel-table-into-microsoft-word-with-vba

    https://msdn.microsoft.com/en-us/library/office/gg508921.aspx?f=255&MSPPError=-2147217396

    Also, see this Excel VBA script to convert a bunch of Excel files (in a folder) to PDF files...

    Sub Convert_Excel_To_PDF()
        Dim MyPath As String, FilesInPath As String
        Dim MyFiles() As String, Fnum As Long
        Dim mybook As Workbook
        Dim CalcMode As Long
        Dim sh As Worksheet
        Dim ErrorYes As Boolean
        Dim LPosition As Integer
     
        'Fill in the path\folder where the Excel files are
        MyPath = "c:\Documents and Settings\shuerya\Desktop\ExcelFiles\"
     
        FilesInPath = Dir(MyPath & "*.xl*")
        If FilesInPath = "" Then
            MsgBox "No files found"
            Exit Sub
        End If
     
        Fnum = 0
        Do While FilesInPath <> ""
            Fnum = Fnum + 1
            ReDim Preserve MyFiles(1 To Fnum)
            MyFiles(Fnum) = FilesInPath
            FilesInPath = Dir()
        Loop
     
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
     
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
                On Error GoTo 0
     
                If Not mybook Is Nothing Then
     
                        
                        LPosition = InStr(1, mybook.Name, ".") - 1
                        mybookname = Left(mybook.Name, LPosition)
                        mybook.Activate
                        'All PDF Files get saved in the directory below:
                        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                            "C:\Documents and Settings\shuerya\Desktop\PDFFiles\" & mybookname & ".pdf", _
                            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                            :=False, OpenAfterPublish:=False
                            
                End If
                
                mybook.Close SaveChanges:=False
     
            Next Fnum
        End If
     
        If ErrorYes = True Then
            MsgBox "There are problems in one or more files, possible problem:" _
                 & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"
        End If
     
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = CalcMode
        End With
    End Sub
    

    Post back if you have more questions.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by L.Hl Monday, July 20, 2015 4:06 PM
    Thursday, July 9, 2015 2:47 AM