A better way to do things

Sooo....I'm using DTS packages within Excel VBA to upload data to a SQL Server 2000 database.  What I would like to do is use VB instead of Excel to develop an "upload tool", however the only problem is that in part of the process of uploading i have to format Excel files (Excel files have my input data), into a format that i can import into SQL Server via "macros"...

So...if you use VB, you can't use a "macro" to format an Excel data file, is Excel VBA the only way to create this tool?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I don't know Visual Basic at all, but I thought I would chime in on this one because in the C++ and C# world,  ADO.NET can treat an Excel spreadsheet just like a database table.  See if there is similar functionality in VB and just use data access components to move the data into SQL.
You can add a reference to the Excel object model in your VB application. Then:

 Dim ObjExcel As Object
 Dim ObjWorkbook As Object
 Set ObjExcel = CreateObject("Excel.Application.11")
 Set ObjWorkbook = objExcel.Workbooks.Open("C:\mySheet.xls")
 ObjWorkbook.Worksheets("Sheet1").Cells(8, 1).Value = "Something"

You can open a workbook, select sheets, retrieve (or add) data, format cells, pretty much everything you can do in VBA.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VB6 code is almost identical to VBA. You can use COM to open an Excel Application and workbook. From thereon, you could copy and paste your macro code.

Dim xlApp as Excel.Application
Dim xlWbk as Excel.Workbook

Set xlApp = New Excel.Application
Set xlWbk = xlApp.Workbooks.Open("C:\MyFolder\MyWorkbook.xls")

ADO (not .NET) can be used with Excel sheets.

You can do the same job in VB.Net, but there will be more coding differences
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Hmm. That's ironic.

I have shown the code for early binding, but failed to mention that you need a set a reference to the Excel Object library.

ddbishop has mentioned it, but shows the code for late binding.

The advantage of early binding is it runs faster and that you get 'intellisense' -  where the editor pops up a list of appropriate properties and methods as you type. The disadvantage is that on rare occasions library version differences give probems when the application is deployed.
jdhindsaAuthor Commented:
Are you sure you can do the same things in VB6 that you can do in a macro?  Can someone direct me to example of file manipulation in VB6 code?  

Is C# similar to VB in that way?
Yes, this is VB6. I would also suggest early binding as Graham suggests. C# is .net. Although I am getting into .net, I've not done anything with any Office products with it yet, although I assume you can.
Also, you should be able to do anything through VB/Excel object model that you can do in VBA, perhaps even more.
What type of file manipluation are you talking about? I've done some pretty complex stuff via VB/Word object model, and some moderate stuff with Excel object model. In fact, you should be able to pretty much copy code from Excel macro into VB as long as you are referencing the proper object (activesheet, etc.)

Also, see: http://support.microsoft.com/kb/219151/
haven't looked at it, but check: http://www.freevbcode.com/ShowCode.asp?ID=1729

You should be able to do a google search and find others (excel object model visual basic 6)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.