Professional Web Applications Themes

Importing Web Query From Excel - Microsoft SQL / MS SQL Server

Whats the best way to import data from an excel spreadsheet that contains a Web Query. How can I get the Web Query to Refresh before the import takes place(DTS or whatever) Any help on this would be great...

  1. #1

    Default Importing Web Query From Excel

    Whats the best way to import data from an excel
    spreadsheet that contains a Web Query. How can I get the
    Web Query to Refresh before the import takes place(DTS or
    whatever)

    Any help on this would be great
    Tom Guest

  2. #2

    Default Re: Importing Web Query From Excel

    see these articles:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;321686
    http://support.microsoft.com/default.aspx?scid=kb;en-us;306397


    "Tom Y" <com> wrote in message
    news:08bd01c35cdb$1ae14530$gbl... 


    Tony Guest

  3. #3

    Default Re: Importing Web Query From Excel

    Tom,

    If the query is set to refresh on file open, you could open
    and save the file using the sp_OA... procedures for automation
    whenever the information is too old. It's not pretty, but it might
    do what you need. I set background refresh off for the web query -
    I don't know if that was necessary, but it worked.

    Here's an example:

    create table LastUpdates (
    dataSource varchar(200),
    lastUpdate datetime default getdate()
    )
    go

    insert into LastUpdates(dataSource,lastUpdate) values
    ('WeatherData','19000101')
    go

    create procedure refreshWeather
    as
    declare
    autoobject int,
    fileobject int,
    return int

    exec return = sp_OACreate 'Excel.Application', autoobject output, 4
    if return <> 0 or error <> 0 begin
    print 'Error opening Excel'
    goto finish
    end

    exec return = sp_OAMethod autoobject, 'Workbooks.Open', fileobject
    output, 'E:\excel\Weather.xls'
    if return <> 0 or error <> 0 begin
    print 'Error opening File'
    goto finish
    end

    exec return = sp_OAMethod autoobject,
    'Application.ActiveWorkbook.Save', Null
    if return <> 0 or error <> 0 begin
    print 'Error saving File'
    goto finish
    end

    finish:
    exec return = sp_OAMethod autoobject, 'Quit'

    if return <> 0 or error <> 0 begin
    print 'Error quitting automation'
    end

    exec sp_OADestroy autoobject

    if return <> 0 or error <> 0 begin
    print 'Error destroying automation object'
    end
    go

    create procedure getWeather (
    minutes int
    -- will refresh if last update was more than this long ago
    -- (timing is approximate because of how datediff works)
    ) as
    if (
    select datediff(minute,lastUpdate,getdate())
    from LastUpdates
    where dataSource = 'WeatherData'
    ) > minutes begin
    exec refreshWeather
    update lastUpdates set
    lastUpdate = getdate()
    where dataSource = 'WeatherData'
    end

    select Updated, Place, Temperature
    from OpenRowSet('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=e:\excel\Weather.xls','select * from Weather')
    go

    exec getWeather 1
    go
    drop procedure getWeather, refreshWeather
    drop table lastUpdates

    -- Steve Kass
    -- Drew University
    -- Ref: DAD2D45E-269E-4CA7-839A-C48E08BDECB0


    Tom wrote:
     
    >us;321686
    >

    >us;306397
    >
    > [/ref]
    >or
    >

    >>.
    >>
    >>
    >>[/ref][/ref]

    Steve Guest

Similar Threads

  1. Excel importing data to a .mdb
    By Calico in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: January 2nd, 09:23 PM
  2. Help importing excel file into Access DB
    By macdonald7 in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: July 13th, 08:35 PM
  3. Importing Text From Excel
    By Brett_Matteen@adobeforums.com in forum Adobe Indesign Windows
    Replies: 1
    Last Post: May 21st, 09:26 PM
  4. Importing Excel data
    By chris in forum FileMaker
    Replies: 5
    Last Post: August 19th, 01:34 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139