Solved

Collect data from excel using VB

Posted on 2006-06-19
11
495 Views
Last Modified: 2012-05-05
Hello Experts,

I am VERY new to Visual Basic.  I do have VB 6.0, but I wouldn't even say that I have a working knowlege of it.  My question is this.  I know there is a way to retrieve data from an excel spreadsheet using VB.  I'm assuming this might be done in a way similar to SQL.  What I would like to do is query my excel spreadsheet, using VB, and display the results in a VB program.  I would like to set the program up to use multiple query criteria, selectable by a drop down list, and by a date range using a calendar.  Based on what has been entered as the query criteria, I would like to have the resullts displayed in the VB program, with some minor calculations being made.  

 I AM NOT LOOKING FOR SOMEONE TO WRITE THE CODE FOR ME.  I want to learn it myself.  What I am looking for might be considered more of a lesson than anything.  Possible resources, that would explain the necessary components needed to accomplish such a task, and how to implement them.  The first Expert that provides usefu information will receive all 500 points.
0
Comment
Question by:neelyjer
11 Comments
 
LVL 2

Assisted Solution

by:TiwariVikas
TiwariVikas earned 150 total points
ID: 16939882
Hi neelyier,

you need to learn Excel VBA first to access the Excel sheet from the VB Code.

For example: the following application opens and get the handle of excel sheet provided as argument 'Path'

Sub OpenXLWorkBook(Path As String)

    Dim xlApp As Excel.Application
   
    'Check to see if the file name passed in to
    'the procedure is valid
    If Dir(Path) = "" Then
        MsgBox Path & " isn't a valid path!"
        Exit Sub
    Else
        Set xlApp = CreateObject("Excel.Application")
   
        'You do not need to make the application object visible
        'if you close the file and quit the application
        'later in your code in order to remove these objects
        'from memory.
   
        xlApp.Visible = True
        xlApp.Workbooks.Open Path
    End If

End Sub

For more reference, please download the MS Excel VBA Language Reference from

http://www.microsoft.com/downloads/details.aspx?FamilyID=2204a62e-4877-4563-8e83-4848dda796e4&DisplayLang=en

Regards,
Vikas.
0
 
LVL 8

Assisted Solution

by:hiteshgupta1
hiteshgupta1 earned 50 total points
ID: 16939888
To satrt with that u can go thru following article and learn how to read and write data from/to excel file

http://support.microsoft.com/default.aspx?scid=KB;en-us;q278973

den develop ur own code for a simple query and post it here
den u nca take step by step help for how to modify the code to make the query more customized
0
 
LVL 18

Expert Comment

by:JoseParrot
ID: 16940070
Hi,

I use to program VBA, the Visual Basic for Applications that is a companion of all the products of Office.
The special things on VBA are the specific commands related to Excel, Word and Access, beside the standard Visual Basic commands.

My main trick is to use the macro feature of Excel.
By starting the Tools->Macro->Record I make the things (mark a cell, type '=', click another cell, then type '+', click another cell and Enter) and after stoping the recording, take a look at the code, by opening the VBA editor, by using that option in the menu Tools->Macro->VB Editor
Then I can edit and create loops, call other programs, and so forth.

To use buttons, dialog boxes, etc. you can add them in Edit mode (the triangle icon) and use the same trick of macro recording. You can also add images in your program. Just chose Insert->Object and a list of things appears... Some are useful, others don't run well.

For me, the difficult part is not VB, are the specific Excel commands instead.
My recommendation is to adquire a Visual Basic for Applications book (not a manual, but a book, with samples), as "Using Excel Visual Basic for Applications" by Elisabeth Boonin and  
"Microsoft Excel 2002 Visual Basic for Applications Step by Step" by Reed Jacobson.

Good luck!

Jose
0
 
LVL 11

Assisted Solution

by:five22bags
five22bags earned 200 total points
ID: 16941754
Neelyjr:

You are in luck! Accessing MS Excel docs IS just like SQL! With any data access from any programming language, you first need a proper connection string. This is an example from MSDN:

.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;"

(Help with connection strings can be foudn at www.connectionstrings.com, it is a great reference for exactly what you need).

Once you have established a Connection object, you can structure a Command object using an SQL style syntax. Here are some sample SQL-style statements, also from MSDN:

strQuery = "SELECT * FROM [Sheet1$]"
strQuery = "SELECT * FROM `Sheet1$`"
strQuery = "SELECT * FROM [Sheet1$A1:B10]"

These examples fame from this article: http://support.microsoft.com/kb/257819

If you would prefer some more specific code blocks, I have several apps that pull data from excel files, and would be glad to show you if you like.

Good luck!
0
 
LVL 1

Accepted Solution

by:
chandurs earned 100 total points
ID: 16945584
I can think of two ways to get read the excel data from vb
1) import the excel sheet into a table (Sql Server etc.,) using import and open up the table using ADO connection and you can do anything you want from there, read/write/display through that ADO record set

2) Open up excel sheet using excel objects for vb. Sample code to open up an excel file and work sheet.
Public     oExcelApplication as new excel.application
public oExcelWorkBook as new excel.WorkBook
Public OExcelSheet as new excel.WorkSheet
    Set oExcelApplication = CreateObject("Excel.Application")
    Set oExcelWorkBook = oExcelApplication.Workbooks.Open("C:Test.xls")
    Set oExceworkSheet = oExcelWorkBook.WorkSheets(1)

Let me know if this is what you are looking for.,
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:neelyjer
ID: 16947979
There are alot of options here. More than I ever expected.  I will look into each one of these and get back to you all ASAP.  Thanks so much for your direction.
0
 

Author Comment

by:neelyjer
ID: 16948606
Hello All,

Using the following code:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
    .Open
End With

I will obviously get an error, because that path and workbook do not exist on my machine.  But more curious to me is the following error:

Compile error:
User-defined type not defined

and cn As ADODB.Coneection is highlighted.

Is there something missing from my install of Visual Basic 6.0, and if so can I download it somewhere?  I did a complete install when installing VB 6.0.
0
 
LVL 11

Assisted Solution

by:five22bags
five22bags earned 200 total points
ID: 16950077
Go to Project menu:References..., and make sure you have checked the 'Microsoft ActiveX Data Objects (ADO) Library'.

Without it, VB doesn't know what an ADODB.Anything is.
0
 
LVL 7

Expert Comment

by:assyst
ID: 16967030
Hi

You can use Linked Server and give your excel file as the data source..

Since you want to write the code by your own....

Try after reading the following example..

Good Luck!


Use the Microsoft OLE DB Provider for Jet on an Excel Spreadsheet
To create a linked server definition using the Microsoft OLE DB Provider for Jet to access an Excel spreadsheet, first create a named range in Excel specifying the columns and rows of the Excel worksheet to select. The name of the range can then be referenced as a table name in a distributed query.

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\Test.xls',
   NULL,
   'Excel 5.0'
GO

In order to access data from an Excel spreadsheet, associate a range of cells with a name. A given named range can be accessed by using the name of the range as the table name. The following query can be used to access a named range called SalesData using the linked server set up as above.

SELECT *
FROM EXCEL...TestData
GO

Further Doubts please refer sp_addlinkedserver (Transact-SQL Reference) in Books Online...

0
 

Author Comment

by:neelyjer
ID: 16977579
EVERYONE,
0
 

Author Comment

by:neelyjer
ID: 16977586
EVERYONE,  I WANT TO THANK YOU ALL FOR YOUR ADVICE AND DIRECTION.  MOST OF THE INFORMATION THAT WAS PROVIDED WAS MOST HELPFUL AND HAS BROADENED MY SCOPE OF WHAT EXCEL VBA CAN ACHIEVE.  I HAVE DIVIDED THE POINTS AMONGST THE ANSWERS THAT HAVE HELPED ME THE MOST.  I COULD NOT DECIDE WHICH PROVIDED INFORMATION WAS MORE VALUABLE, AS THEY HAVE ALL HAD AN IMPACT ON MY CURRENT AND FUTURE APPROACH TO USING VISUAL BASIC. TiwariVikas, I GAVE YOU THE MOST BECAUSE YOU WERE THE FIRST TO ANSWER, AND THE RECOMMENDED DOWNLOAD HAS BEEN MY "GOTO" REFERENCE FOR HELP.  ALTHOUGH, I HAVEN'T USED THE CODE YOU PROVIDED WITH YOUR ANSWER, I THINK I AM CLOSE TO ACHEIVING THE FIRST OF A FEW GOALS I HAVE SET FOR THIS PROGRAM.  AGAIN, THANK YOU ALL FOR THE INFORMATION!!!!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now