Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to open excel file (97/2000)

    I was able to record a macro to open up an Excel spreadsheet. What I can't figure out is how to eliminate the popup dialog box asking me to open the Entire Workbook or select a worksheet.

    All I need is to be able to import the spreadsheet (sheet1) into Word.

    Thanks for any help.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro to open excel file (97/2000)

    I'm guessing you're using Selection.InsertFile, and no matter what you try, the Range parameter doesn't let you override the dialog. I haven't worked with this before, and I couldn't find any previously posted solutions in a quick search. There are certainly other way to get information from Excel using automation. A good deal more coding is involved to do that, but it might be worth it. I will leave it to the Excel gurus to show the way.

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Macro to open excel file (97/2000)

    Can you name the data range in Sheet 1?

    You may need to include that in the recorded code where is says Range:=""
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to open excel file (97/2000)

    Hi Andrew, Jscher:

    Thank you for your input. I was able to insert the spreadsheet as a table using Database feature instead. This way, I don't get prompted to select which sheet [img]/forums/images/smilies/smile.gif[/img]

    I have another question though. I recorded another macro to delete columns 4-13. The macro works but too much codes, lol. I can't figure out how to set my range only to those columns and delete them. Can either of you show me an example.

    Thanks,

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro to open excel file (97/2000)

    I think there is a problem using Ranges in tables because ranges run from left to right, rather than in columns. However, you might be able to delete an entire column. I haven't done it before, and it's too late tonight to test it out...

  6. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Macro to open excel file (97/2000)

    It's not very elegant but it should work
    <pre>With Selection.Tables(1)
    .Columns(13).Delete
    .Columns(12).Delete
    .Columns(11).Delete
    .Columns(10).Delete
    .Columns(9).Delete
    .Columns(8).Delete
    .Columns(7).Delete
    .Columns(6).Delete
    .Columns(5).Delete
    .Columns(4).Delete
    End With</pre>

    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to open excel file (97/2000)

    Thanks so much Andrew! Works like a charm.

    --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •