js06

I haven't put the whole thing together yet.

This is just to give you an idea of how it will work incase anyone wants to give it a try.

The basics are here you just have to put it together.

Here is the basic process:

you have a datagridview and a details view (textboxes) on a form

(you don't have to have the gridview but it helps to see it work)

once the file is open you can use navigation code that will loop through the row (one cell at a time)

and put the data from each cell into the corresponding textbox (the boxes are bound to the grid so they will update it)

use code to navigate to the next row in the datagridview

move to the next row of cells in excel.

The key to doing this with the excel rows is to use the offset method

Let's say that you start with cell A1

you will use the select method to activate that cell range("A1").select

you then tell it that textbox1.text = activecell.value

then you call offset to move to the next cell in the row

then textbox2.text = activecell.value

you continue this on untill you reach the end of your row then you can call the offset to jump back to the beginning of the row

and then offset down 1 to the next row.

Now you can add a new record to the datagridview and repeat the process

You can wrap this entire code in a do loop

The do loop will be something simple like do until activecell.value = "" (a blank cell at the end of your rows in excel)

Here is some sample code to get you started

Dim oExcel As Excel.Application

Dim oBook As Excel.Workbook

Dim oSheet As Excel.Worksheet

oExcel = CreateObject("Excel.Application")

oBook = oExcel.Workbooks.open("c:\Book1.xls", [ReadOnly]:=False)

oExcel.Visible = True

oExcel.DisplayAlerts = False '//So It doesnt prompt

oSheet = oBook.Worksheets(1) '.Worksheets("Sheet1") ˇ®.worksheets(ˇ°sheetnameˇ±)

oSheet.select()

oSheet.Range("A1").Value = "Test worked"

'oSheet.delete()

oBook.SaveAs("C:\Book1.xls")

as you can see the reference that will let you navigate the cells is osheet

offset example: osheet.activecell.offet(1, 0).select

the (1, 0) is the row and column reference ( i always forget which one is row and which is column)

this is not a specific cell reference this is a direction reference (hence the word offset) (move)

you can use postive numbers to move right and down

and negative numbers to move left and up

You can increase the offset by any number

let's say you have 6 columns and you want to offset to the last column you would use (6, 0) or (0, 6)

here is some sample code to add a new row of data

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

Me.BindingContext(DataSet1, DataSet1.Customers.TableName).AddNew()

End Sub

"TableName" Do not change this "customers" is the name of your table

I see many people asking about excel so i thought this might interest some of you.

If anyone puts it together then please post for everyone. I will do the same if i get time for it. I need to work on my own project for a little while.





Re: Visual Basic Express Edition Automation to get excel data to your database (getting started)

Jon1s

that brilliant, just what i needed!

one thing though, how would you pull a variable amount of excel rows into your datagridview, would you use a count statement to determine the number of rows with data in excel if i was doing this in vba i would simply do:

i = Cells(1, 1).End(xlDown).Row

to determine the last filled row 's number, an this be applied in vb

so can you loop your vb code to do this i.e.

For i = 1 to i

osheet.Cells(i, 1).Select

'Code here

Next






Re: Visual Basic Express Edition Automation to get excel data to your database (getting started)

js06

That i can't answer exactly

You might be able to use the same code slightly modified

or what you might be looking for is something utilizing - usedrange

The code in vb.net to automate excel as far as i can tell is basically the same as vba

If you have some trouble putting that one together you can always put an end catch at the end of your excel file

in the last cell of the first column you can put the word - end do until activecell = "end"

and then put your loop to look and stop when it reaches that

as long as you have continuous rows, meaning no blank rows inbetween then you can just use the do until activecell = ""

But in order for you to add data properly into your database you can't have any blank rows unless you code to skip those

The next statement would be nice though

I think if you play with it, you can get it to work

If you do then please post it.

The first program i made was in excel and i stored the data i worked with forms in a hidden row that i had my boxes bound to.

And i used offset to run the whole thing (to add new rows, etc...) But that was before i knew how to store data in variables. I had never used a variable before so i didn't understand it.

Since you have vba experience you can see that you might be able to do a lot more with your database and excel mixed

one drawback to sql is that it doesn't handle calculations except for minor things but i have been looking at some code to use excel as a calculation engine to do math for you. Not automation but using excel's abilities in the background.

I am getting ready to start a new project pretty soon that will use both spreadsheets and sql. I like the way sql has quite a bit built in for you already to handle data but i have really thought about just using spreadsheets to handle all the data storage and then code many of the things that sql has. The only thing i don't like is that i don't think excel would be as secure as sql. There is another program that i have been using called spreadsheet gear that helps with this in some ways. You can hide sheets more securely than with excel. I like the power that excel has but if you plan on giving your app to someone who doesn't have excel then you can't use automation, spreadhsheet gear is fully embedded and has a view that you add to your own form. It has some drawbacks that i have come across but overall it is very good. And there is a free version in your registration benefits portal. The code is also basically the same as vba.