Filling Multiple cells in excel

M

monkey_man

New member
Joined
Dec 2, 2003
Messages
3
Location
Sheffield,England
I have a gauge record system which logs all the gauges we currently have in the system , the user fills the details in and presses save and it is sent to an excel spreadsheet.

Code: 
 TextBox1.Text = (xl.Range("d2").Value)

what i would like to do is when it comes to save the spreadsheet with the data I want it to find the next blank line and insert the details there, how would I go about doing that.

Thanks for any help you can give me
 
Off the top of my head, you could do it a couple of different ways. If no one manually edits the spreadsheet outside of your app, just store a hidden value within the spreadsheet itself that contains the next blank row. Read this value before inserting new data, and increment it afterwords.

If the excel spreadsheet is being used outside of your app, then Id just try running a loop to read one or more values from each row until a blank row is found. Even with thousands of rows, you should be able to find the next blank in less than a couple of seconds. Hope this helps, Ben
 
Just a start (i.e. you might need to tweak it), but Id try something like this:

Code: 
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Dim i As Integer
Dim BlankLine As Integer = -1

xlApp = New Excel.Application
xlBook = xlApp.Workbooks.Open(pathToMyFile)
xlSheet = xlBook.Worksheets(1)

Loop until some max value
For i = 0 To 10000

     Check if the value of the cell is empty
     If CStr(xlSheet.Range("A" & i.ToString).Value).Length = 0 Then
          BlankLine = i
          Exit For
     End If

Next

Check blank line was found
If BlankLine = -1 Then MsgBox("No Blank Lines Found")

Note that this would only check one column (column A), you might want to check several to verify a line is really "blank". Theres probably better (i.e. faster) ways to loops through cells, since constantly setting a new range object will slow things down. I know you can set multiple cell values at once using an array, so Im guessing you could also read multiple values, which would be much faster. Id try setting the range to several cells, then setting the value of the range to an array. In case you guessed, Im no excel expert. Hope this helps
 
Heres an easier and faster way. If column A is where your data is:
Code: 
BlankLine=xlSheet.Range("A65536").End(xlUp).Row + 1
The only complication is that if you have no data in the sheet, this will return 2 instead of 1, so youd have to check for that special case.

(Caveat: this works in VB6; in VB.Net you might need to break this up into several steps. Sorry, I have nowhere to test this at the moment.)
 
Back
Top