Export Excel to SQL Server

T

Tom Y

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

Any help on this would be great
 
T

Thomas Bartkus

Tom Y said:
Whats the best way to export data from an excel
spreadsheet that contains a Web Query to SQL Server (or
any DB). How can I get the
Web Query to Refresh before the export takes place(DTS or
whatever)

Any help on this would be great
Click to expand...

My approach would be to set up an Access (.mdb) file containing linked
tables to both the SQL Server table you are targeting as well as the
spreadsheet containing the Web query. You can then define (using Accesss)
an APPEND or UPDATE query that will do what you wish with the SQL server
table.

You can automate with Excel/VBA code by doing a QueryTable.Refresh for the
web query then .Execute your stored APPEND or UPDATE query.
 
T

Tom Y

Thanks for the help Thomas!

Where would I be doing the QueryTable.Refresh from? Where
would this code be?

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Embedding excel formula in MS Query that will be evaluated in excelnot in the SQL server 0
VBA to export large tables from Excel to SQL Server 1
Migrating from 2010 to 2013 - having issues: Exporting to xml 0
Unrecognized database format 'S:\xxx.accdb' (Microsoft Jet Database Engine) 0
Exporting Data From Excel to SQL 2
Macro to navigate through a Java Web-Page 1
from mdf to excel data export 2
Excel as front end and SQL Server as back end 0

Top