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

 

[Chance1234]

adoConn.execute "DELETE * FROM WHATEVERYOURTABLEISCALLED"


Sheet1.range(whateverrange your data is in )

x = 1

Do until x = top of your range

adoconn.execute "APPEND your data into your table
x= x + 1

Loop


Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[fredericofonseca]

woodyinoz

The threads I mentioned have EXACTLY what you need. please try to adapt them to your case, and if you have problems POST the code you are having problems with, not just the one you know is working.

the only new thing is the "delete" bit mentioned by Chance1234. (and this was not part of your original post).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[woodyinoz]

Hi I'm back!

Ok, I think I'm getting there... Eventually!

I'm getting an error on the following line of code:

Set wWork_sheet = Workbooks("TrialCode").Worksheets("Sheet1")

It's giving me a subscript out of range error.

Any ideas?

Woody

 

[xlbo]

means something you are referencing doesn't exist - either the workbook or worksheet name in this case is not correct. I would guess that it is the workbook name which may need to be referenced as:

Workbooks("TrialCode.xls")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question