Run MS Excel 2007 macro/vba from MS SQL Server 2008

Question

Run MS Excel 2007 macro/vba from MS SQL Server 2008

shafiq_n

Old Hand

Points: 381
More actions
May 3, 2011 at 7:20 pm

#249706

Hello everyone,
I am new to SQL Server programming. My query is regarding how to run MS Excel macro/vba from MS SQL Server 2008.
I am moving to MS SQL Server 2008 from MS Access DB due to contents limitation of MS Access. Previously, Macro available in MS Excel 2007 was run from MS Access vba automatically to upload data in MS Access. Now, I want same calculated data to be moved to SQL Server but don't know how to do this. Is it possible to activate/run MS Excel code from MS SQL Server or some code to open this MS Excel from MS SQL Server?
I searched for this query but not found anywhere in this forum. I would greatly appreciate for all your guidance.
Thanks.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

What are you using as front end? If you are still using access then nothing's changed (well almost). You'd just need to update the connection string in the excel macro and you "should" be good to go. There may be some little things to change here and there but hopefully not.

shafiq_n Old Hand Points: 381 More actions · Answer 2

Thanks for reply.

yes, frontend is MS Access which will be connected to MS SQL Server but frontend will be used by users only. Data need to be moved from MS Excel to MS SQL Server automatically. Excel file contains vba code which does some calculations inside it, now I want to trigger that code from MS SQL Server and upload calculated excel file to SQL Server.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

So this is a 1 time import you want to do?

Just run the code manually in excel after you've changed the connection string. It should work straight away or require very slight modifications.

kfleschner SSC Enthusiast Points: 144 More actions · Answer 4

If you link your tables in the front end from your new SQL database, then your VBA should still work.

shafiq_n Old Hand Points: 381 More actions · Answer 5

Thanks for all replies.

this is not a one time task.

In fact, on the daily basis excel files will get uploaded in a directory (at least 20 files), there is a vba code in excel file which does calculation in it. Now, once all the calculations done (by that vba code), the result data need to be uploaded in MS SQL Server.

The front end will not help since its connected to MS SQL Server and provided to users (i might be wrong on this, pls suggest).......since this is a daily task, i require some code in sql server to run the vba code available in excel file and upload that file in sql server.

kfleschner SSC Enthusiast Points: 144 More actions · Answer 6

You should be able to automate the Excel tasks using additional VBA in Access. Using VBA automation, the front end can have a button users can press to select a file, have Access open it, and run the macros in the excel file. There are a lot of websites with good information on automating one Office product from another using VBA. If your tables are linked in Access to your SQL server, then moving data from your Excel file to a linked Access table will also then transfer the data to your SQL server.

LutzM SSC Guru Points: 107049 More actions · Answer 7

You eventually also might be able to perform the calculation on the SQL Server side. Depends on the complexity of the calculation.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

shafiq_n Old Hand Points: 381 More actions · Answer 8

Thanks everyone.

I finally got the code which will import excel data to sql server.

INSERT INTO sqlservertable

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel

8.0;Database=C:\1.xls', 'SELECT * FROM [sheet1$]')

This code is correct but due to some reason sql server gives the following error message on this code :crying: ...

"OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".

I checked ISAM in registry and renamed dll file as microsoft site suggested, restarted server as well as pc but no chance.

Any help on this pls.

Run MS Excel 2007 macro/vba from MS SQL Server 2008

Cookies on SQLServerCentral