• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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?
1 Solution
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.
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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)

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now