Results 1 to 8 of 8

Thread: Insert To Excel Starting From Column E

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    14

    Insert To Excel Starting From Column E

    Hi,

    I'm trying to add info to an excel sheet but rather than add a new line each time I would like it to ignore the first 4 columns as these already contain formulas to generate a barcode.

    Is this possible?

    Code:
    Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim MyCommand As New System.Data.OleDb.OleDbCommand
                Dim SQL As String
                MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + My.Settings.StockItemsFile + ";Extended Properties=Excel 12.0;")
                MyConnection.Open()
                MyCommand.Connection = MyConnection
                SQL = "Insert into [Stock Items$] ([Description],[Category],[Supplier],[Suppliers Code],[Price Each]) values('" + TextBox13.Text + "','" + ComboBox1.Text + "','" + ComboBox2.Text + "','" + TextBox14.Text + "','" + TextBox15.Text + "')"
                MyCommand.CommandText = SQL
                MyCommand.ExecuteNonQuery()
                MyConnection.Close()
    Each time I run this I get the the info in the correct places but on line 1001 as my barcode formulas run to line 1000.

    Thanks.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Insert To Excel Starting From Column E

    I am not familiar with using an OleDbCommand to update an Excel file, but my gut tells me that by you doing an insert rather than an update it is overwriting the formula cells.

    Try doing an update and specifying the row in the WHERE clause.

    Again, I could be completely off base, but that is what my gut is telling me.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Insert To Excel Starting From Column E

    Quote Originally Posted by nadavey21 View Post
    Hi,

    I'm trying to add info to an excel sheet but rather than add a new line each time I would like it to ignore the first 4 columns as these already contain formulas to generate a barcode.

    Is this possible?

    Code:
    Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim MyCommand As New System.Data.OleDb.OleDbCommand
                Dim SQL As String
                MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + My.Settings.StockItemsFile + ";Extended Properties=Excel 12.0;")
                MyConnection.Open()
                MyCommand.Connection = MyConnection
                SQL = "Insert into [Stock Items$] ([Description],[Category],[Supplier],[Suppliers Code],[Price Each]) values('" + TextBox13.Text + "','" + ComboBox1.Text + "','" + ComboBox2.Text + "','" + TextBox14.Text + "','" + TextBox15.Text + "')"
                MyCommand.CommandText = SQL
                MyCommand.ExecuteNonQuery()
                MyConnection.Close()
    Each time I run this I get the the info in the correct places but on line 1001 as my barcode formulas run to line 1000.

    Thanks.
    are these the columns in your Excel sheet?
    Code:
     ([Description],[Category],[Supplier],[Suppliers Code],[Price Each]
    because in your Connection String you have to set HDR=Yes
    which means with your Insert Statment..... Insert values to those Columns

    so you need to add to your Connection string ....
    Code:
        MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + My.Settings.StockItemsFile + ";Extended Properties=Excel 12.0;HDR=Yes")
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    14

    Re: Insert To Excel Starting From Column E

    Hi,

    My connection string is already working ok and adding HDR=Yes didnt change anything.

    I don't think I explained myself very well.

    In columns A to D I have formulas in there to generate a barcodes for the data I'm trying to put in to columns E-I which are my headers ([Description],[Category],[Supplier],[Suppliers Code],[Price Each].

    When I run my code the data goes in the correct column but in the wrong row (the row after my formulas end for the barcode).

    Please see image. Row 5 I'm trying to get to row 2.

    Thanks.Attachment 180335

  5. #5
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Insert To Excel Starting From Column E

    Quote Originally Posted by nadavey21 View Post
    Please see image. Row 5 I'm trying to get to row 2.

    Thanks.Attachment 180335
    This sort of confirms my suspicion from my original post. Have you tried calling update on the row?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  6. #6

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    14

    Re: Insert To Excel Starting From Column E

    Thanks for the reply.

    Its the WHERE bit im struggling with.

    I want to update WHERE nothing exists in rows starting from columns E.

    Thanks again.

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Insert To Excel Starting From Column E

    I'm lost now, in Post#1 you execute a Insert statement
    in Post#6 you talk about a Update statement

    this is how a Update can statment look like
    Code:
    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
            Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=E:\Names.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
            Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
            Cn.Open()
            Dim sSql As String = "Update [mySheet1$] set FirstName = 'New Name' Where FirstName='Tommy'"
            ExecuteSQL(Cn, sSql)
            Cn.Close()
            Cn = Nothing
        End Sub
    
        Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                      ByVal sSQL As String, _
                                      Optional ByRef ErrMessage As String = Nothing, _
                                      Optional ByVal TransAction As  _
                                      OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Insert To Excel Starting From Column E

    This is how your business logic should probably work:
    1. Starting at row 1, loop until cell A[row] is empty
    2. Change your SQL command to be an update statement
    3. The where clause should be where the row index is less than the row from step 1
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width