-
Feb 25th, 2021, 04:24 PM
#1
Thread Starter
New Member
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.
-
Feb 25th, 2021, 05:07 PM
#2
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.
-
Feb 26th, 2021, 01:08 AM
#3
Re: Insert To Excel Starting From Column E
Originally Posted by nadavey21
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.
-
Feb 26th, 2021, 02:38 PM
#4
Thread Starter
New Member
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
-
Feb 26th, 2021, 02:51 PM
#5
Re: Insert To Excel Starting From Column E
Originally Posted by nadavey21
This sort of confirms my suspicion from my original post. Have you tried calling update on the row?
-
Feb 26th, 2021, 03:10 PM
#6
Thread Starter
New Member
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.
-
Feb 26th, 2021, 03:31 PM
#7
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.
-
Feb 26th, 2021, 04:31 PM
#8
Re: Insert To Excel Starting From Column E
This is how your business logic should probably work:
- Starting at row 1, loop until cell A[row] is empty
- Change your SQL command to be an update statement
- The where clause should be where the row index is less than the row from step 1
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|