Spooner

How can I import a Excel Spreadsheet so that I can extract some information from it

I need to do this for about 600 files but I only need the first work sheet out of each workbook.

Thanks



Re: Visual Basic Express Edition Excel Spreadsheet

js06

Do a search in the forums, there are some examples. There are also some in the MSDN help.

If you want to handle that many files with speed and accuracy then i suggest the spreadsheet gear program.

You can get it free in your registration benefits and it is supports excel files perfectly. There website has code examples.

When you install the software it also has source code but it is in C# so you can also install C# express for some more help. If you can give me more info on what you want to do with the data i can help you more.

It supports opening and saving files with any changes that you make and it works like a gridview in VB. You do not have to have excel installed to use it either and neither do your end users.






Re: Visual Basic Express Edition Excel Spreadsheet

Spooner

I had some problems downloading spreadsheet gear from the Registration Benefits Portal, but I have downloaded it and installed it from the spreadsheet gear website.

But to be honest I'm not sure what to do with it now.

What I'm wanting to do is automate a process to extract customers names, addresses, etc from the first worksheet in each workbook, about 600+ workbooks in total. The required information is then output to a csv file.

I've managed to extract this information if I first convert them to CSV and extract the relevant info but I wanted to make it easier and just read direct from the workbooks.

So if you wouldn't mind advising what I need to do with the spreadsheet gear program and just how to read data in from a worksheet.

Thanks for you help

Simon





Re: Visual Basic Express Edition Excel Spreadsheet

js06

I do not know the exact steps you will need to take for all you want to do but i can get you in the right direction.

First you need to add the workbookview to your toolbox in VB.

First open a project or create a new one

Right click in your toolbox and select add tab, name the tab spreadsheet gear or whatever

then right click again in that tab section and select choose items

Check formulabar and workbookview then click ok

Now you need a form and add the workbookview to it.

From this point you have many options to work with your workbooks

The only part i am not sure about is outputting to csv. You may have email their support for that one.

right click on the workbookview and then select workbook explorer. now you can work with the workbooks.

The explorer will let you open new workbooks in the view or close them out.

You can add as many as you like but you can only display 1 at a time.

right click on the workbook set or the workbook name for the options.

When you open up the workbooks in the explorer they will be added to your project and when you publish they will be saved as a different file type. When you run your app you can work with these files and then save them as excel files (not sure about csv though)

or you can open the workbooks at runtime from your computer, a server or end users.

Next from here you need to know exactly what you want to do with the files.

Do you want to add them to your project and keep them there or just open them from a server, etc...

Will the files you want to open always be the same or will you have to work with updated file names or new files etc...

This will get you started, post me back what you want to do from here.

I will post some code later for you about opening files and give you some more info on working with the files later today.

also look at their website examples they have code to get started n basically what you are trying to do.

By the way what trouble did you have with the benefits portal download






Re: Visual Basic Express Edition Excel Spreadsheet

JohnWein

Why don't you want to do this in VBA This should be a very simple task using Excel.



Re: Visual Basic Express Edition Excel Spreadsheet

Spooner

js06,

I've now got a form with a workbookview added to it.

I already have the code to search a specific directory for .csv files, I then plan on processing each one at a time extracting required info and adding to the csv file.

Just need to a code example of how to now populate this workbook I have on screen and then read each field and compare it to some requirements, I will have a look shortly at the examples on their website.

When trying the download from the portal I had to download the activex download manager, which I did but then it would pop up a screen for a fraction of a second which I beleive said at the top that it was download then the screen would change to tell me I need to install the activex download, which I'd already done a few times. I am running Vista, maybe that's the issue

Thanks very much for your help





Re: Visual Basic Express Edition Excel Spreadsheet

Spooner

JohnWein,

Didn't know you could do this sort of thing directly in excel and as I wanted to learn more about VB it thought this could be a bit of a learning process.





Re: Visual Basic Express Edition Excel Spreadsheet

JohnWein

If you're not familiar with using VB in Excel, you may be overextending. I think you'd get a feeling of accomplishment by getting the job done. You could then do it again in VBExpress to learn how to interop with Excel. Most of what you learn by doing the job in Visual Basic for Applications will transfer fully to Visual Basic .Net.



Re: Visual Basic Express Edition Excel Spreadsheet

js06

Spooner,

The code that you use in VBA (excel) and spreadhseet gear in vb express is basically the same. Either way you go you can take what you learn from one and easily transfer to the other. There are quite a few limitations with VBA as apposed to using an embedded tool such as spreadhseet gear in vb.net.

I do not know what to tell you about the vista problem. You may have to talk to microsoft about that. I am using XP

I will get back to you later today or tomorrow. I have to finish working on my website today, i'm behind.






Re: Visual Basic Express Edition Excel Spreadsheet

Spooner

I've started looking at using the Microsoft Excel 12.0 Object Library already in Visual Basic and so far have the code below which just opens excel and then my saved workbook, but how do I now specify a cell and just get the data from that one cell

Once I know how to get data from a cell then I should be okay with the rest.

Thanks again

Public Class Form1

Dim oXL As Microsoft.Office.Interop.Excel.Application

Dim oWB As Microsoft.Office.Interop.Excel.Workbook

Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

Dim oRng As Microsoft.Office.Interop.Excel.Range

Dim data

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

' Start Excel

oXL = CreateObject("Excel.Application")

oXL.Visible = True

' Open a workbook.

oWB = oXL.Workbooks.Open("c:\test.xls")

' Read data from a cell

data = ' Data from Cell in Spreadsheet

End Sub

End Class





Re: Visual Basic Express Edition Excel Spreadsheet

js06

I will look for some code for excel in vb later and see what i can come up with.

I do have some code for spreadsheet gear though.

WorkbookView1.GetLock()

Try

WorkbookView1.ActiveWorkbook = SpreadsheetGear.Factory.GetWorkbook("C:\\ your file location \ your file name.xls")

Catch

Finally

WorkbookView1.ActiveWorkbook.Worksheets(0).Select() 'worksheet 0 is the first sheet

WorkbookView1.ActiveWorksheet.Range("A1").Select() 'you can select a specific cell, a range of cells or a range name

Dim celldata As String = WorkbookView1.ActiveCell.Text

TextBox1.Text = celldata 'textbox is just for an example to see that it retrieved the data

WorkbookView1.ReleaseLock()

End Try

The only problem is you need the free version to continue to use spreasheet gear after 30 days. When the trial one is up your app will not work anymore.

Or you will have to buy the full version.






Re: Visual Basic Express Edition Excel Spreadsheet

Spooner

jso6,

Thanks, that code works brilliantly and I've put that in my my existing code and can now search a directory for xls files load them up and then extract info from cells.

How do you find the range of active cells with Spreadsheet Gear

I'll see if I can download the free version from PC at work.

When I finally do a build does Spreadsheet gear get built with it or does the PC I want to run this on have to have it installed

Thanks Again for all your help





Re: Visual Basic Express Edition Excel Spreadsheet

js06

I haven't quite figured out how to get the activecells data. (more than one selected at a time)

i have seen code examples for a range name but not for several random selected cells.

i'm guessing what you are looking for is to select several cells or rows throughout the sheet and then copy that all at one time

If you want to select one cell or one row at a time and have that copy to another location i can give you some code that will move through the whole row and copy the data one cell at a time.

I know that you can copy a range of random cell though. (would be easier)

This would be a good question to email to spreadsheet gear tech support, they will answer you quickly.

After you get the code from them would you mind posting it for us

Your workbookview is embedded into your app and your end user does not have to have spreadsheet gear installed. They don't even need excel.

Only the computer that you are developing on has to have spreadsheet gear installed.






Re: Visual Basic Express Edition Excel Spreadsheet

Spooner

I emailed support @ spreadsheet gear and they advised me to use the following to get the range

Code Snippet

WorkbookView1.ActiveWorkbook.Worksheets(0).UsedRange

I've tried to use it as below and then just pop up a message box for testing purposes advising of the range

Code Snippet

Dim gridrange = WorkbookView1.ActiveWorkbook.Worksheets(0).UsedRange

MsgBox("gridrange : " & gridrange)

but I get error

System.InvalidCastException was unhandled
Message="Operator '&' is not defined for string "gridrange" and type ' '."

I've emailed them again and asked for advice.

I assume that I correct in my theory that the range would show the cell that is the furthest out from A1 that has data in, i.e. K13

You mentioned you have code that will move through one cell at a time and read that cell, that is what is was going to do but I thought I'd need the range to find the outer limits of the cells in use, is there another way to when you've reached the end then

Thanks






Re: Visual Basic Express Edition Excel Spreadsheet

js06

What you are looking for is something like this:

WorkbookView1.ActiveWorkbook.Worksheets(0).UsedRange.text.tostring()

I have played around with it a little and can only get the data from 1 cell.

You may have to get a little creative to make this work

This will give you the range address for whatever you have selected. (any combination of selected cells)

button click event

MsgBox(Me.WorkbookView1.RangeSelection.Address.ToString())

From here you just need to copy the data maybe by storing the addresses and then accessing the values by address location.

something like:

dim celladdress as string = me.workbookview1.rangeselection.address.tostring()

me.workbookview1.range("A1").formula = celladdress

or store it in a texbox

then loop through you cells retrieving the data and comparing the address to get the correct data.

i have had to get fairly creative to get certain things done but surprisingly with out an excessive amount of code.

example: storing values or addresses in cells with certain formats and matching the values in the cells to search for things like dates

(there are differences between the data itself and what is shown (formatted text) in the cells)

I will have to play with it some more and get back to you.

I will post some code for for looping through the cells also later.

Are you trying to get the data from cells that are all in a single selected range example A1:K13