import Excel data into Word tabel

[Robie]

Hi

I need to create a Word macro which can import Excel data into Word. I created something using MailMerge before (which was bit more complicated than this one) but in this case it is a simple import.
Basically, take the Excel data and copy it to Word table. I don't know if this is possible but thought I would just ask.

The Excel file is basically 7 columns by n rows. Each column has a heading.

52.png

Thanks.
Robie

[StuartR]

Simple copy and paste usually works well enough for me. Copy the data in Excel and then paste into Word...

[Robie]

Simple copy and paste usually works well enough for me. Copy the data in Excel and then paste into Word...

Sorry, I should have made is clear my opening post.

I need to create a VBA in Word to import from a selected Excel spreadsheet. Then there are other things I have to do with the data which are still being discussed.
All my users want is to click a button, specify a file and get the data into Word. They are that simple.

[HansV]

You'll have to specify precisely what you want to import.

[Robie]

You'll have to specify precisely what you want to import.

Thanks Hans.

Quite simply import columns 1-7 for the rows specified (the number of rows would be different for each spreadsheet). That's it really.

[HansV]

What exactly do you mean by "the rows specified"?

[Robie]

What exactly do you mean by "the rows specified"?

The number of rows in the spreadsheet.

[HansV]

Which worksheet do you want to use? The active sheet in the active workbook? Or ...?

[Robie]

Which worksheet do you want to use? The active sheet in the active workbook? Or ...?

The active worksheet please Hans. Thanks.

[HansV]

Here you go:

Code: Select all

Sub ImportFromXL()
    Dim app As Object
    Dim wsh As Object
    Dim rng As Object
    Dim m As Long
    On Error GoTo ErrHandler
    Set app = GetObject(Class:="Excel.Application")
    Set wsh = app.Activesheet
    m = wsh.Range("A:G").Find(What:="*", SearchOrder:=1, SearchDirection:=2).Row
    wsh.Range("A1:G" & m).Copy
    Selection.Paste
ExitHandler:
    Set rng = Nothing
    Set wsh = Nothing
    Set app = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

[Robie]

Here you go:

Code: Select all

Sub ImportFromXL()
    Dim app As Object
    Dim wsh As Object
    Dim rng As Object
    Dim m As Long
    On Error GoTo ErrHandler
    Set app = GetObject(Class:="Excel.Application")
    Set wsh = app.Activesheet
    m = wsh.Range("A:G").Find(What:="*", SearchOrder:=1, SearchDirection:=2).Row
    wsh.Range("A1:G" & m).Copy
    Selection.Paste
ExitHandler:
    Set rng = Nothing
    Set wsh = Nothing
    Set app = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

That's perfect Hans. Thank you so much. I will try it out later today but again thank you so much for your help.

[Robie]

Here you go:

Code: Select all

Sub ImportFromXL()
    Dim app As Object
    Dim wsh As Object
    Dim rng As Object
    Dim m As Long
    On Error GoTo ErrHandler
    Set app = GetObject(Class:="Excel.Application")
    Set wsh = app.Activesheet
    m = wsh.Range("A:G").Find(What:="*", SearchOrder:=1, SearchDirection:=2).Row
    wsh.Range("A1:G" & m).Copy
    Selection.Paste
ExitHandler:
    Set rng = Nothing
    Set wsh = Nothing
    Set app = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

Hi Hans

I went with your code and it works great. Thanks. :)

But, there is one user (there always is one) who gets an error 'ActiveX can't create object' error. There is no error number. Before, I do further investigation, it there something obvious at which I should look?

[HansV]

The most probable explanation is that the user hasn't started Excel when the macro is called. The macro will only work correctly if Excel has been started, a workbook has been opened or created, and a worksheet is active (not a chart sheet).

[Robie]

The most probable explanation is that the user hasn't started Excel when the macro is called. The macro will only work correctly if Excel has been started, a workbook has been opened or created, and a worksheet is active (not a chart sheet).

Thanks Hans. That is exactly what it was. They are happy now.

Thank you so much.