jdhindsa
asked on
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?
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?
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:\M
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
Dim xlApp as Excel.Application
Dim xlWbk as Excel.Workbook
Set xlApp = New Excel.Application
Set xlWbk = xlApp.Workbooks.Open("C:\M
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
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.
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.
ASKER
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?
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.
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)
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)