|
|
|
![]() |
|
|||||||
|
||||||||
![]() |
| Thread Tools | Rate Thread | Display Modes |
|
#1
|
||||
|
||||
|
Connecting VB with Excel
In my computer programming class, we have been learning to program in Visual Basic. I'm not sure how much it is used in real life but I'm trying to connect a VB program to excel. I wrote the code based off of a video I found on youtube. I know very little about computer programming but you can only learn by failing and figuring it out. The road block I hit is not knowing what my Oledb provider is. How do I find my oledb provider? Thanks
|
|
#2
|
||||
|
||||
|
Re: Connecting VB with Excel
Quote:
First, are you hoping to actually "connect" your program to Excel? As in modify the Excel program and/or send messages to it? If so, just ignore this post. If you are trying to import or create Excel files though I can reccomend the library EPPlus. It is a .net library that lets you create a "Spreadsheet" object in memory (or open a saved one), populate it fairly robustly, and save it to disk. There is alot a good deal of support for it online. If that isn't what you were looking to do then, uh, sorry. Useless post ![]() |
|
#3
|
||||
|
||||
|
Re: Connecting VB with Excel
Quote:
|
|
#4
|
||||
|
||||
|
Re: Connecting VB with Excel
It would be much easier to do with: VisualBASIC for Applications which is the macro language for Microsoft Office. You can make forms in there as well. That has plenty of use just about anywhere Office is.
I can dig up the OLE stuff maybe tomorrow I have not used my VisualBASIC 6 license like this in a while. |
|
#5
|
||||
|
||||
|
Re: Connecting VB with Excel
What is the difference between using Visual Basic Express like I use and Visual Basic for Applications? Or are we talking about the same Visual Basic?
|
|
#6
|
||||
|
||||
|
Re: Connecting VB with Excel
Quote:
VBA also happens to work with some CAD software so there is extra value there. Last edited by techhelpbb : 17-11-2015 at 07:02. |
|
#7
|
|||
|
|||
|
Re: Connecting VB with Excel
In my experience, exporting small data sets to an excel file is frequently done to allow end users to quickly view, sort and display their data.
As data becomes easier to collect and cheaper to store, new ways will be found to help end users organize and display data. Tableau, a FIRST sponsor is one of the companies leading this data display evolution. They have a program that does this and they offer free licenses in the KoP. Many teams find it helpful for organizing scouting data. You can also use Excel to accept user input and store the data in a VB file. This was more critical in early 2000 when HTML form tools were limited and there was more paper to digital interactions. More frequently forms are created in HTML with JavaScript entry validation and then sent to a server for storage. |
|
#8
|
||||
|
||||
|
Re: Connecting VB with Excel
Quote:
In your VB program you would have your Save button event read in all the text box values, update your EPPlus ExcelWorksheet object, then call the ExcelWorksheet.Save() function to write it to disk. You can even load the column names from the Excel file directly into your program by importing the file and just accessing the specific cells. Here are some good examples: http://www.jimmycollins.org/blog/?p=547 And I can send you my own example if you like. Last edited by Monochron : 17-11-2015 at 00:35. |
|
#9
|
||||
|
||||
|
Re: Connecting VB with Excel
There's a pretty good example of OLE from an older page at Microsoft:
https://support.microsoft.com/en-us/kb/242243 With VB.NET (VisualBASIC Express) you should probably try this which basically wraps the OLE functionality with ADO: https://msdn.microsoft.com/en-us/library/ms971514.aspx The long and (maybe not so) fascinating backstory of ODBC->OLE->ADO: https://msdn.microsoft.com/en-us/library/ms810287.aspx ......... BASIC was the language I learned 3rd after assembly language and Forth. If you really like BASIC I recommend looking at some of these: PureBASIC's extensive library - $100 and upgrades for life Write PureBASIC get JavaScript for web applications Write BASIC get JAVA for mobile devices DarkBASIC is oriented towards game design work A very QuickBASIC (TM) like language for console applications QuickBASIC was the predecessor of VisualBASIC for DOS and VisualBASIC for Windows. A BASICStamp(TM) like language targeting Microchip PIC microcontrollers A BASIC language targeting AVR microcontrollers Yet another BASIC language targeting 8015, ARM, AVR, FT90x & PIC microcontrollers What the heck is the FT90x you ask: FT90x There are many others. These are just a few I own and use from time to time. Last edited by techhelpbb : 17-11-2015 at 15:39. |
|
#10
|
||||
|
||||
|
Re: Connecting VB with Excel
Quote:
|
|
#11
|
||||
|
||||
|
Re: Connecting VB with Excel
Quote:
A more thorough comparion Here's a reprint from the VisualBASIC Programmers Journal June 2001 discussing upgrading code with the wizard from VB6 to VB.NET This is how you use Visual Studio 2005 instead of VBA in case you want the many features Visual Studio adds Pay careful attention to the notes about: "A common problem when migrating VBA code to Visual Basic is ambiguous references. If the Visual Basic compiler cannot figure out where to find the value for a variable, you need to help the compiler resolve (or disambiguate) the reference. This is discussed in the example in this tip." This can cause some fun and interesting problems when ignored. Ask WikiPedia Please note that some of the newer features of VB.NET could have been added to VB6. For example with different syntax some features make an appearance in VBA7 which started with Office 2010 64bit: https://msdn.microsoft.com/en-us/lib...ngVBA7CodeBase There's a good way to check if you have 64bit VBA7 on this page I know that when the 64bit version of Microsoft Office first shipped some operations took much longer to complete. I had a massive spreadsheet used for QA at a semiconductor company. It grew so large it needed the 64bit version and the calculation payload went from 17 minutes to 2 hours for the same code. Last edited by techhelpbb : 17-11-2015 at 12:06. |
|
#12
|
||||
|
||||
|
Re: Connecting VB with Excel
I'm not too familiar with the specific differences, though I don't think they would be too large. I think in both these approaches you will have to use a data structure in your program to store all of your cells and data and whatnot. They you will have to save it to disk somewhere. Personally I think it would be easier to use the EPPlus library and work withing the VB framework that you already know. But, as I said, I'm not familiar with VBA so I don't know how different it is, or how create an Excel file works in it. You will have to use some googling to figure out which one looks best from your perspective.
|
![]() |
| Thread Tools | |
| Display Modes | Rate This Thread |
|
|