Using VBA to Import a Range from an Excel Workbook 
Author Message
 Using VBA to Import a Range from an Excel Workbook

I'm using Word 97.  I'm working on a macro that will import a range from
an Excel workbook.  The Excel workbook consist of 13 sheets.  I just want
to import the data from one of those sheets.  My current macro prompts
the user for the name of the worksheet to import.  I'd like to make it
import the appropriate range automatically so that no input is required
from the user.

The Range parameter in the following statements refers to the same range
in the Excel workbook.  Version 1 works, but pulls in the correct data
only if the correct sheet is active in the Excel workbook (it pastes into
this message funny, but it works in Word).  Version 2 does not work at
all.

Here's the information regarding the range in the Excel workbook:

Sheet Name:  Memo Data  (I've tried it without the space)
Range Name:  MemoData   (I've tried a different range name)

Version 1
---------
    Selection.InsertFile FileName:="Monthly Case Reviews.xls",
Range:="A1:D5", _
        ConfirmConversions:=False, Link:=False, Attachment:=False

Version 2
---------
    Selection.InsertFile FileName:="Monthly Case Reviews.xls",
Range:="MemoData", _
        ConfirmConversions:=False, Link:=False, Attachment:=False

I would prefer to use a syntax similar to version 2 because referring to
the range by name insures that the correct data is pulled into Word
without regard to which sheet is active in the Excel workbook.  Can
anyone help me get version 2 of the statement working?

Thanks for any help you can offer.

--

Please reply to the newsgroup so that all may learn
from your wisdom.

--Tom



Fri, 11 Oct 2002 03:00:00 GMT  
 Using VBA to Import a Range from an Excel Workbook

Quote:
> Hi Thomas,

> 2 options:

> (1) Import as an Excel object, specifying the sheet:

> Selection.InsertFile _
>     FileName:="J:\My Documents\Travel.xls", _
>     Range:="Sheet2!A1:A4", _
>     ConfirmConversions:=False, _
>     Link:=False, _
>     Attachment:=False

> (2) Import the Excel named range as a Word Table (cf: Excel Object) as
> follows:

> Selection.Range.InsertDatabase _
>     Format:=0, _
>     Style:=0, _
>     LinkToSource:=False, _
>     Connection:="NamedRange", _
>     DataSource:="J:\My Documents\Travel.xls"

I was able to get both of these options working, but I ended up using the
first option because it lends itself more readily to some of the
formatting stuff that my macro is doing.  Thanks for your help.

--

Please reply to the newsgroup so that all may learn
from your wisdom.

--Tom



Sat, 12 Oct 2002 03:00:00 GMT  
 Using VBA to Import a Range from an Excel Workbook
Hi Thomas,

2 options:

(1) Import as an Excel object, specifying the sheet:

Selection.InsertFile _
    FileName:="J:\My Documents\Travel.xls", _
    Range:="Sheet2!A1:A4", _
    ConfirmConversions:=False, _
    Link:=False, _
    Attachment:=False

(2) Import the Excel named range as a Word Table (cf: Excel Object) as
follows:

Selection.Range.InsertDatabase _
    Format:=0, _
    Style:=0, _
    LinkToSource:=False, _
    Connection:="NamedRange", _
    DataSource:="J:\My Documents\Travel.xls"

--
Hope this helps.
Regards
ibby

Please post replies or follow-ups to the **newsgroup** so that participants
may benefit or contribute.


Quote:
> I'm using Word 97.  I'm working on a macro that will import a range from
> an Excel workbook.  The Excel workbook consist of 13 sheets.  I just want
> to import the data from one of those sheets.  My current macro prompts
> the user for the name of the worksheet to import.  I'd like to make it
> import the appropriate range automatically so that no input is required
> from the user.

> The Range parameter in the following statements refers to the same range
> in the Excel workbook.  Version 1 works, but pulls in the correct data
> only if the correct sheet is active in the Excel workbook (it pastes into
> this message funny, but it works in Word).  Version 2 does not work at
> all.

> Here's the information regarding the range in the Excel workbook:

> Sheet Name:  Memo Data  (I've tried it without the space)
> Range Name:  MemoData   (I've tried a different range name)

> Version 1
> ---------
>     Selection.InsertFile FileName:="Monthly Case Reviews.xls",
> Range:="A1:D5", _
>         ConfirmConversions:=False, Link:=False, Attachment:=False

> Version 2
> ---------
>     Selection.InsertFile FileName:="Monthly Case Reviews.xls",
> Range:="MemoData", _
>         ConfirmConversions:=False, Link:=False, Attachment:=False

> I would prefer to use a syntax similar to version 2 because referring to
> the range by name insures that the correct data is pulled into Word
> without regard to which sheet is active in the Excel workbook.  Can
> anyone help me get version 2 of the statement working?

> Thanks for any help you can offer.

> --

> Please reply to the newsgroup so that all may learn
> from your wisdom.

> --Tom



Sun, 13 Oct 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Creating an Excel Workbook using Access VBA, Excel VBA or C

2. Using VBA to select a highlighted range and paste values to another workbook

3. How to import a specific range from a workbook on disk into the active workbook

4. Pasting Range from Excel 2002 via VBA to Word causes a New Workbook to open

5. Import multiple workbooks into ACCESS using VBA

6. Importing entire workbook into Access using VBA

7. Import multiple workbooks into Access using VBA

8. copy range of cells from another workbook using command button in EXCEL 2000

9. Posting Excel Workbooks to Public Outlook Folders using VBA

10. Using VBA to reference another Excel Workbook


 
Powered by phpBB® Forum Software © phpBB Group