VB CODE and Excel Book

[akalehzan]

Hi all,

I’m a newbie and have no VB experience.
I’m trying to learn how to import multiple complex Excel sheets into existing tables in MS SQL 2000 database.
Is there any book that quickly walks me through VB CODE and Excel?

I know there is not any easy way to do this, but any help or suggestion would be very nice

Bets regards,

Abrahim

[Ecniv]

Start small - one small sheet (part of a larger one perhaps!), then work up to the larger ones.

You need to look up automation (in the vba stuff).
In vb, you'd need:
createobject
getobject

To mimic Excel vba you'll also need:
wrk as object 'set to a workbook
sht as object 'set to the sheet you are working from
rng as object 'useful as a range object.

Also, when learning, try using excels record macro function to see how it works. Then copy the code and replace with proper objects (instead of the selection object).

[RobDog888]

since your running SSQL Server and want to import Excel to it, you can use DTS (Data Transformation Services) to import/export to and from Excel very easily.

[akalehzan]

Very helpful info!
I have been using DTS, but the problem is that the Excel work sheet containing data has the column’s format set vertically, for example:
Exam1:
First_name : Jim
Last_name: Smith
Address: San Jose

Exam2:
But in standard format it supposed to be like:
First_name Last_name City
Jim Smith San Jose

I don’t think you can use DTS to import Exam1: format into the Database.

Is there any book for the beginner like me to start with?

Thanks in advance,

Abrahim

[billhuard]

You can use transpose(copy/pasteSpecial) to obtain your standard format and then import it using DTS