[collegegirl7]

I have an application in which I am using vb.net and excel. Daily readings are inputted into vb.net and then saved into excel. The readings will be different everyday. For example if the date is 15th of Feb, the data goes into to the cells in column C:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        oBook = oExcel.Workbooks.Open("C:\Documents and Settings\Desktop\Excelfyp")

        Try
            With oExcel
                .Visible = False
                'Add details

                If Gdate = "15 February 2010" Then


                    .Range("C99").Value = TextBox1.Text
                    .Range("C100").Value = TextBox2.Text
                    .Range("C101").Value = TextBox3.Text
                    .Range("C102").Value = TextBox4.Text
                    .Range("C103").Value = TextBox5.Text
                    .Range("C3104").Value = TextBox6.Text
                    .Range("C105").Value = TextBox7.Text
                    .Range("C106").Value = TextBox8.Text
                    .Range("C107").Value = TextBox9.Text
                    .Range("C108").Value = TextBox10.Text
                    .Range("C109").Value = TextBox11.Text

                    .Range("C111").Value = TextBox12.Text
                    .Range("C112").Value = TextBox13.Text
                    .Range("C113").Value = TextBox14.Text



                End If



            End With
        Catch ex As Exception
            MsgBox("Error: " & ex.ToString, MsgBoxStyle.Critical, "Error!")
        End Try
        oBook.Save()
        oBook.Close()




    End Sub
End Class


This code works and the information goes in. If the date is 16th of feb I want any data that i type in to go into the same cells as above but in COLUMN E instead and if the date is the 17th of feb I want any data that I type in to go onto the same cells as above but in COLUMN G etc.. For every extra date i want it to increment by 2 columns. I have tried adding in:


                Dim column As String = "C"
                Dim Gdate As Date = "01 January 2010"
                Dim todayDate As Date = DateTime.Now

                Do

                    column = Chr(Asc(column) + 2)
                    Gdate = Gdate.AddDays(1)


                Loop Until Gdate = todayDate

But it doesn't work. Any help at all would be greatly appreciated. Thank you.

This post has been edited by AdamSpeight2008: 15 February 2010 - 07:32 AM
Reason for edit:: Added Code Tags

[Bort]

It looks like you are not giving it an Excel file to work with. This line:

oBook = oExcel.Workbooks.Open("C:\Documents and Settings\Desktop\Excelfyp")

should really end in '.xls")' or '.xlt")'.

Other than that, your code looks fine

I have an Office Automation tutorial posted here which might help you, although there is not much info in there about Excel that you don't already appear to know

[collegegirl7]

Thank you. The information saves into the file. If it is the 15th of feb, all the data goes into the cells above in column C. The problem is if the next date I want the information I type in to go into all the cells above but in COLUMN E. Insead of having to say:

If Gdate = "15 February 2010" Then


.Range("C99").Value = TextBox1.Text
.Range("C100").Value = TextBox2.Text
.Range("C101").Value = TextBox3.Text
.Range("C102").Value = TextBox4.Text
.Range("C103").Value = TextBox5.Text
.Range("C3104").Value = TextBox6.Text
.Range("C105").Value = TextBox7.Text
.Range("C106").Value = TextBox8.Text
.Range("C107").Value = TextBox9.Text
.Range("C108").Value = TextBox10.Text
.Range("C109").Value = TextBox11.Text

.Range("C111").Value = TextBox12.Text
.Range("C112").Value = TextBox13.Text
.Range("C113").Value = TextBox14.Text



End If

If Gdate = "16 February 2010" Then


.Range("E99").Value = TextBox1.Text
.Range("E100").Value = TextBox2.Text
.Range("E101").Value = TextBox3.Text
.Range("E102").Value = TextBox4.Text
.Range("E103").Value = TextBox5.Text
.Range("E3104").Value = TextBox6.Text
.Range("E105").Value = TextBox7.Text
.Range("E106").Value = TextBox8.Text
.Range("E107").Value = TextBox9.Text
.Range("E108").Value = TextBox10.Text
.Range("E109").Value = TextBox11.Text

.Range("E111").Value = TextBox12.Text
.Range("E112").Value = TextBox13.Text
.Range("E113").Value = TextBox14.Text



End If

is there a way to move across two columns for every extra day instead of having to type this out 365 times for every day of the year. Thanks.

[Bort]

I am sure there is a way to do this, although I can't remember it off the top of my head. I will look into it and see what I can find.

By the way collegegirl7, please use code tags so you don't upset the mods. they cry easily

This post has been edited by Bort: 15 February 2010 - 08:14 AM

[collegegirl7]

Thank you very much. I have tried:

Dim column As String = "C"
Dim Gdate As Date = "01 January 2010"
Dim todayDate As Date = DateTime.Now

Do

column = Chr(Asc(column) + 2)
Gdate = Gdate.AddDays(1)


Loop Until Gdate = todayDate



But it doesn't work. I have also tried:


Dim tx as TextBox()={TextBox1,TextBox2,TextBox3,Textbox4}

Dim dt as Date=Date.Parse(gdate)

Dim dy as Integer = dt.Day

Dim cell as String=""

If dy > 26 Then

cell = "C" & Chr(64 + dy Mod 26)

Else



cell = Chr(dy + 64)

End If





For i As Integer = 0 To tx.GetUpperBound(0)

.Range(cell & (i + 2)).Value=tx(i).Text

Next

This post has been edited by collegegirl7: 15 February 2010 - 08:20 AM

[Bort]

Ok, what you are looking for is something in Excel called the R1C1 reference style. Basically it rewrites your column headers so instead of rading A, B, C, and so on, they read 1, 2, 3, etc, just like the rows.

So, referencing your 15th Feb date, you would need to use the line...

.Range("R99C3").Value = TextBox1.Text

This will make it easier for you to use some kind of variable for the column number and simply add +2 per day depending on the date.

For example...

.Range("R99C" & column).Value = TextBox1.Text

You'll need to change some options in Excel to change to R1C1 referencing. Quoted from the Microsoft website on this:

Quote

1. Start Microsoft Excel.
2. On the Tools menu, click Options.
3. Click the General tab.
4. Under Settings, click to clear the R1C1 reference style check box (upper-left corner), and then click OK.

Hope this helps

[CharlieMay]

You could use Cells instead of Range and increment a counter based on current date - the start date.
instead of:

.Range("C99").Value = TextBox1.Text

You could use:

.Cells(99,ctr) = TextBox1.Text

With this method, you would have a know start date that equals column 3

Then take the new date - the old date using datediff to find the number of days different and add that number to the start value of ctr

Dim gDate As Date = #2/14/2010# 'We'll use this date to represent the day to start in column C
Dim daystoadd As Integer = CInt(DateDiff(DateInterval.Day, gDate, Date.Now))
Dim ctr As Integer = 3 + daystoadd 'since today is the 15th, daystoadd = 1 so on the 15th the start column is 4 or Column D

'then on to your code
oBook = oExcel.Workbooks.Open.......

...

Try
  With oExcel
    .Visible = False
    .cells(99,ctr) = textbox1.text
    .cells(100,ctr) = textbox2.text 
...

This post has been edited by CharlieMay: 15 February 2010 - 08:24 AM

[collegegirl7]

Thank you!!!

Is this correct:

Dim gDate As Date = "15 February 2010" 'We'll use this date to represent the day to start in column C 
        Dim ctr As Integer = 3 'Start at the 3rd column in the row (Column C) 
        Dim daystoadd As Integer = CInt(DateDiff(DateInterval.Day, gDate, Date.Now))
        Dim ctr As Integer = 3 + daystoadd = 1

Because im getting an error sayin that local variable 'ctr' is already declared in the cuurent block.

[Bort]

Bah! damn code tags not allowing text formatting in them, lol.

You need to lose this line:

Dim ctr As Integer = 3 'Start at the 3rd column in the row (Column C) 

And also the '= 1' at the end of the last line.

This post has been edited by Bort: 15 February 2010 - 08:44 AM

[CharlieMay]

Remove the first Dim ctr as Integer = 3 line

and remove the =1 from the last line.

[collegegirl7]

P

rivate Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim gDate As Date = "15 February 2010"

Dim daystoadd As Integer = CInt(DateDiff(DateInterval.Day, gDate, Date.Now))
Dim ctr As Integer = 3 + daystoadd

oBook = oExcel.Workbooks.Open("C:\Documents and Settings\Desktop\Excelfyp")

Try
With oExcel
.Visible = False
'Add details



If gDate = "15 February 2010" Then


.Cells(99, ctr) = TextBox1.Text
.Cells(100, ctr) = TextBox2.Text
End If



End With
Catch ex As Exception
MsgBox("Error: " & ex.ToString, MsgBoxStyle.Critical, "Error!")
End Try
oBook.Save()
oBook.Close()




End Sub
End Class

I put in this code and it is overwriting into the same cell in the same column again.

This post has been edited by AdamSpeight2008: 15 February 2010 - 09:28 AM
Reason for edit:: Please use the code tags :code:

[CharlieMay]

Please hilight your code and in the toolbar of the editor click on the <> symbol to put your code in a code window. It makes it easier to read.

With the code I provided you do not need to use the IF statement. I'll look over your code and see if I find anything else.

OK, first off, are you changing the date anywhere in your system? You have the starting date as the 15th and today is the 15th so everything will write to column C until there is a difference in days.

Also you need to change this line:

Dim ctr As Integer = 3 + daystoadd

to this

Dim ctr as Integer = 3 + (daystoadd*2)

so that it will skip a column inbetween dates.

This post has been edited by CharlieMay: 15 February 2010 - 09:30 AM

[AdamSpeight2008]

collegegirl7 please start using the code tags. If you don't know how to use them press the button that looks like <>