Transferring data from Excel to SQL Server using VBA

[woodyinoz]

Hi all,

I'm looking to update a table in SQL Server with information held in an Excel spreadsheet. I want to be able to do this by clicking a button in Excel. I don't want the user to have to go into SQL Server.

I know it may be possible by calling a DTS package but do I need SQL installed on the client to do this? Is there any way of doing this without DTS? By just using VBA??

Thanks in advance,

Woody

 

[fredericofonseca]

search these forums for ADO and insert. you will find some samples of what you need to do.

The only thing you will need is the SQL client components (which may or not be installed with the latest MDAC), and the latest MDAC you can get (2.8 or greater).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[woodyinoz]

Ok,

I've checked through a million posts but I'm still no closer to working out how to do it!

All I have is 3 columns worth of data in an Excel spreadsheet that I need to move into a SQL Server table. Everythime the Excel data needs to overwrite whats in the table.....

Can anyone give me an example just to at least get me started?!

Cheers,

Woody

 

[Chance1234]

connection example

Global objUserConnection As Object
Public Sub psubUserConnect()

Dim strConnectionString As String
'Set the connection
strConnectionString = "DSN=DLNFISEC;uid=XXXXX;pwd=dXXXXX;database=app_tt2"
Set objUserConnection = CreateObject("ADODB.Connection")
objUserConnection.ConnectionString = strConnectionString
objUserConnection.ConnectionTimeout = 30
objUserConnection.Open

End Sub
Public Sub psubUserDisconnect()
On Error Resume Next
objUserConnection.Close
End Sub

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[woodyinoz]

Can you explain this a little.... Where do I put it? I'm a complete and utter beginner when it comes to this!

The terms 'spoon feeding' comes to mind!

 

[Chance1234]

OK firstly break it down into steps.

First of all you need to make a connection to the database as in the above code.

Next you need to get your data off your spreadsheet somehow. There are lots of example of manipulating data in this forum just do a search for recordset and excel

1. You could Load the range into a array
2. You could loop through the cells

Once you have your data in your code, you then need to send that back to the database.

As you mention overwrite. You are going to need to run a DELETE Query on the SQL table, and then run a Append query.

Finally you need to close the connection,


Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[woodyinoz]

Chance,

Cheers for this...

I've taken the above example you've given me but am getting an error on the Global objUserConnection As Object line.

It gives me a compile error saying:
Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of an object module

 

[Chance1234]

Put it in a normal module, not a class module

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[woodyinoz]

I am stupid! Thanks Chance.

 

[fredericofonseca]

thread707-1128751
thread707-1097373

and others contain the information you need to use SQL. not bothered to place link here.

Regarding using a button, search your Excel help and these forums for how to assign a macro to a button, and on how to create a new button.
If you still have problems get to us again with what you have attempted to do and with whichever errors you may have.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[woodyinoz]

Frederico,

I've taken a look at the two examples you've given me and I have a question about it.

If I change the example you have given to suit my datasource I get a user defined type not defined error on the following line:

Dim adoConn As ADODB.Connection

I'm assuming that this is because I am missing some code prior to the example you have shown?

 

[PHV]

Tick the Microsoft ActiveX Data Object 2.# Library in the menu Tools -> References ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[woodyinoz]

I don't get a References option within the Tools menu!

 

[woodyinoz]

Ignore that! Yes I do!

 

[Chance1234]

which version of excel are you using btw woodyinoz ?

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[woodyinoz]

I'm using Excel 2003. Sorry I should have said that earlier.

 

[woodyinoz]

Ok,

I've managed to set up a connection with the following code:

Sub putdata()

Dim sConn As String
Dim sSQL As String
Dim strConnectionString As String

'Set the connection
strConnectionString = "DSN=SQLSvr;uid=sa;pwd=5ql53rv3r;database=devlog"
Set adoConn = CreateObject("ADODB.Connection")
adoConn.ConnectionString = strConnectionString
adoConn.ConnectionTimeout = 30
adoConn.Open

End Sub

So, where do I go next to actually take the data from the spreadsheet and put it into my SQL Server table?
For your info, I am testing this with one column updating one field although in reality I'll be updating a few columns.

I've looked at the code sample and only really understand bits and pieces.... Any explainations?

Cheers for all your help so far,

Woody

 

[woodyinoz]

Sorry....

Replace

Dim sConn as String
Dim sSQL As String

with

Dim adoConn As ADODB.Connection
Dim adoComm As ADODB.Command

 

[Chance1234]

Are you doing any processing on the data ? if so i would load teh range into into an array , carry out the processing the pass to the database,

then it would be a case of executing a append query

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[woodyinoz]

This is what I am trying to do.

I will have a sheet with 5 or 6 columns in it, filled with data. At the end of every month the user will change this data to what they want, possibly adding more rows, possibly removing some.
When they are happy with what they have, I then want them to press a button that will overwrite the data held in a SQL Server table with the data held on the Excel sheet.

Cheers,

Woody