Go to Post Since you have to undo all the connections anyway in order to swap them, you might as well take advantage of the opportunity to do some simple and straightforward troubleshooting. - Alan Anderson [more]
Home
Go Back   Chief Delphi > Technical > Programming
CD-Media   CD-Spy  
portal register members calendar search Today's Posts Mark Forums Read FAQ rules

 
Closed Thread
Thread Tools Rate Thread Display Modes
  #1   Spotlight this post!  
Unread 16-11-2015, 21:39
logank013's Avatar
logank013 logank013 is offline
System.out.println("Ready!");
AKA: Logan Kreisher
FRC #0234 (Cyber Blue)
Team Role: Scout
 
Join Date: Apr 2015
Rookie Year: 2015
Location: Indianapolis, IN
Posts: 709
logank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant future
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
__________________
Cyber Blue Season 2015
IN Indy District Chairman's Award Winner | IN Kokomo District Event Winner (With 135 and 3865)
IN Purdue District Event Winner (With 1024 and 2197) | IN District Championship Winner (With 1024 and 292)
WORLDS:
Archimedes Rank 3 After Quals. | Alliance #3 Captain
Archimedes Division Semi-Finalist (With 503,188, and 836)

Scouting is life. Excel is friend, not foe.
  #2   Spotlight this post!  
Unread 16-11-2015, 22:00
Monochron's Avatar
Monochron Monochron is offline
Engineering Mentor
AKA: Brian O'Sullivan
FRC #4561 (TerrorBytes)
Team Role: Engineer
 
Join Date: Feb 2007
Rookie Year: 2002
Location: Research Triangle Park, NC
Posts: 920
Monochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond repute
Re: Connecting VB with Excel

Quote:
Originally Posted by logank013 View Post
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
Disclaimer: I'm not familiar with VB or Oledb.

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   Spotlight this post!  
Unread 16-11-2015, 22:06
logank013's Avatar
logank013 logank013 is offline
System.out.println("Ready!");
AKA: Logan Kreisher
FRC #0234 (Cyber Blue)
Team Role: Scout
 
Join Date: Apr 2015
Rookie Year: 2015
Location: Indianapolis, IN
Posts: 709
logank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant future
Re: Connecting VB with Excel

Quote:
Originally Posted by Monochron View Post
Disclaimer: I'm not familiar with VB or Oledb.

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
Basically, In my form, i will have multiple text boxes and basically, I have already created an excel file with column names. I have textboxes with corresponding names and basically, every time I hit the "save" button, it is suppose to add the data from the text boxes to the correct cell in the excel file
__________________
Cyber Blue Season 2015
IN Indy District Chairman's Award Winner | IN Kokomo District Event Winner (With 135 and 3865)
IN Purdue District Event Winner (With 1024 and 2197) | IN District Championship Winner (With 1024 and 292)
WORLDS:
Archimedes Rank 3 After Quals. | Alliance #3 Captain
Archimedes Division Semi-Finalist (With 503,188, and 836)

Scouting is life. Excel is friend, not foe.
  #4   Spotlight this post!  
Unread 16-11-2015, 22:39
techhelpbb's Avatar
techhelpbb techhelpbb is offline
Registered User
FRC #0011 (MORT - Team 11)
Team Role: Mentor
 
Join Date: Nov 2010
Rookie Year: 1997
Location: New Jersey
Posts: 1,624
techhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond repute
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   Spotlight this post!  
Unread 16-11-2015, 22:40
logank013's Avatar
logank013 logank013 is offline
System.out.println("Ready!");
AKA: Logan Kreisher
FRC #0234 (Cyber Blue)
Team Role: Scout
 
Join Date: Apr 2015
Rookie Year: 2015
Location: Indianapolis, IN
Posts: 709
logank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant future
Re: Connecting VB with Excel

Quote:
Originally Posted by techhelpbb View Post
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.
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?
__________________
Cyber Blue Season 2015
IN Indy District Chairman's Award Winner | IN Kokomo District Event Winner (With 135 and 3865)
IN Purdue District Event Winner (With 1024 and 2197) | IN District Championship Winner (With 1024 and 292)
WORLDS:
Archimedes Rank 3 After Quals. | Alliance #3 Captain
Archimedes Division Semi-Finalist (With 503,188, and 836)

Scouting is life. Excel is friend, not foe.
  #6   Spotlight this post!  
Unread 16-11-2015, 22:58
techhelpbb's Avatar
techhelpbb techhelpbb is offline
Registered User
FRC #0011 (MORT - Team 11)
Team Role: Mentor
 
Join Date: Nov 2010
Rookie Year: 1997
Location: New Jersey
Posts: 1,624
techhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond repute
Re: Connecting VB with Excel

Quote:
Originally Posted by logank013 View Post
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?
VBA pretty much is VB6 with all the Office object model exposed. Modern VisualBASIC has different syntax but shares the form design concepts. Microsoft for some reason made a break from VB6 syntax and in the process pretty much killed a large portion of their community. RealBASIC which became Xojo picked up the syntax as a way to attract developers.

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   Spotlight this post!  
Unread 16-11-2015, 23:23
DaveL DaveL is offline
Registered User
FRC #2976
 
Join Date: Jan 2013
Location: WA
Posts: 175
DaveL is a jewel in the roughDaveL is a jewel in the roughDaveL is a jewel in the roughDaveL is a jewel in the rough
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   Spotlight this post!  
Unread 17-11-2015, 00:31
Monochron's Avatar
Monochron Monochron is offline
Engineering Mentor
AKA: Brian O'Sullivan
FRC #4561 (TerrorBytes)
Team Role: Engineer
 
Join Date: Feb 2007
Rookie Year: 2002
Location: Research Triangle Park, NC
Posts: 920
Monochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond repute
Re: Connecting VB with Excel

Quote:
Originally Posted by logank013 View Post
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?
If you are willing to jump to VBA, it could be a great tool for what you are looking to do. On the other hand, if you are looking to stretch your legs a bit with regular VB, I whole heatedly recommend EPPlus.

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   Spotlight this post!  
Unread 17-11-2015, 07:57
techhelpbb's Avatar
techhelpbb techhelpbb is offline
Registered User
FRC #0011 (MORT - Team 11)
Team Role: Mentor
 
Join Date: Nov 2010
Rookie Year: 1997
Location: New Jersey
Posts: 1,624
techhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond repute
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   Spotlight this post!  
Unread 17-11-2015, 09:50
logank013's Avatar
logank013 logank013 is offline
System.out.println("Ready!");
AKA: Logan Kreisher
FRC #0234 (Cyber Blue)
Team Role: Scout
 
Join Date: Apr 2015
Rookie Year: 2015
Location: Indianapolis, IN
Posts: 709
logank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant futurelogank013 has a brilliant future
Re: Connecting VB with Excel

Quote:
Originally Posted by Monochron View Post
If you are willing to jump to VBA, it could be a great tool for what you are looking to do. On the other hand, if you are looking to stretch your legs a bit with regular VB, I whole heatedly recommend EPPlus.

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.
Just out of curiousity, how different is the language from VB to VBA
__________________
Cyber Blue Season 2015
IN Indy District Chairman's Award Winner | IN Kokomo District Event Winner (With 135 and 3865)
IN Purdue District Event Winner (With 1024 and 2197) | IN District Championship Winner (With 1024 and 292)
WORLDS:
Archimedes Rank 3 After Quals. | Alliance #3 Captain
Archimedes Division Semi-Finalist (With 503,188, and 836)

Scouting is life. Excel is friend, not foe.
  #11   Spotlight this post!  
Unread 17-11-2015, 11:34
techhelpbb's Avatar
techhelpbb techhelpbb is offline
Registered User
FRC #0011 (MORT - Team 11)
Team Role: Mentor
 
Join Date: Nov 2010
Rookie Year: 1997
Location: New Jersey
Posts: 1,624
techhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond reputetechhelpbb has a reputation beyond repute
Re: Connecting VB with Excel

Quote:
Originally Posted by logank013 View Post
Just out of curiousity, how different is the language from VB to VBA
Differences from VB.NET to VB6 (which is basically VBA)

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   Spotlight this post!  
Unread 18-11-2015, 11:24
Monochron's Avatar
Monochron Monochron is offline
Engineering Mentor
AKA: Brian O'Sullivan
FRC #4561 (TerrorBytes)
Team Role: Engineer
 
Join Date: Feb 2007
Rookie Year: 2002
Location: Research Triangle Park, NC
Posts: 920
Monochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond reputeMonochron has a reputation beyond repute
Re: Connecting VB with Excel

Quote:
Originally Posted by logank013 View Post
Just out of curiousity, how different is the language from VB to VBA
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.
Closed Thread


Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -5. The time now is 03:12.

The Chief Delphi Forums are sponsored by Innovation First International, Inc.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright © Chief Delphi