Read a user selected Excel file

[the Core]

Hi,

I would like to run a macro in Word to read data from an Excel sheet that is selected by the user.

First a dialogbox should pop up so the user can select the Excel file. I've tried this, but it doesn't work:
ExcelSheet = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
Does anyone have a better idea?

Now I want to get data from the ExcelSheet without opening/showing it.

What codes do I need?

Any help is welcome.

Regards,
Cor

[Jay Freedman]

The code your showed isn't valid Word VBA; it's strictly for Excel. When you write a macro in Word to operate on Excel, you need to do two things:
* In the Tools menu of the macro editor, click References and check the box next to the Microsoft Excel object library. This makes the Excel commands available in the Word macro.
* "Qualify" each Excel command you use -- that is, precede it with the name of an Excel object and a dot. That distinguishes it from any possible Word command that has the same name.

The skeleton of a Word macro for doing things in Excel is at http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm. Building on that, here's a macro that opens a workbook in the way you want, leaves it invisible (but open), and pulls information from it. When the macro ends, it closes the workbook (and Excel, if it wasn't running when the macro started).

Sub WorkOnAWorkbook()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

'specify the workbook to work on
'WorkbookToWorkOn = "C:\My Documents\myworkbook.xls"

'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible

'Open the workbook
WorkbookToWorkOn = oXL.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process each of the spreadsheets in the workbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets
'put guts of your code here
MsgBox oSheet.Name
'get next sheet
Next oSheet

If ExcelWasNotRunning Then
oXL.Quit
End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

You need to replace "For Each oSheet" loop with the code to get data from specific cells, if that's what you want to do. That data can then be inserted into a Word document.