-
Nov 28th, 2020, 02:53 AM
#1
Thread Starter
New Member
Using Visual Basic to update excel file
I know this is easy, but everything is hard for a noobs. I am using VB to create a database in excel, so far i have figured out how to open and write data in the excel file, but the problem is it keeps writing over the same row. How do i get it to write to the next empty row below, any help would be appreciated, thank you.
-
Nov 28th, 2020, 06:17 AM
#2
Re: Using Visual Basic to update excel file
There are a wide variety of technologies and coding methods you could be using to interact with an Excel file, so we aren't going to tell you the possible solutions for each of them.
If you show us your current code, we can probably provide a simple change that fixes it.
-
Nov 28th, 2020, 08:18 AM
#3
Re: Using Visual Basic to update excel file
-
Nov 28th, 2020, 10:51 AM
#4
Thread Starter
New Member
Re: Using Visual Basic to update excel file
Sorry about not initially showing the code, see below:
Code:
Imports Microsoft.Office.Interop
Public Class form1
Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
Dim app As New Excel.Application
Dim wk As Excel.Workbook = app.Workbooks.Open(Application.StartupPath & "\Good.xlsx",, True)
Dim wh As Excel.Worksheet = app.Worksheets(1)
wh.range("A2").value = txtID.Text
wh.range("B2").value = txtStuName.Text
wh.range("C2").value = cboGender.Text
wh.range("D2").value = dtDOM.Text
app.Visible = True
End Sub
End Class
-
Nov 28th, 2020, 03:23 PM
#5
Thread Starter
New Member
Re: Using Visual Basic to update excel file
Is there anyone that can help with this please?
-
Nov 28th, 2020, 03:34 PM
#6
Re: Using Visual Basic to update excel file
If you look at the code you can see that everything is always written at the same position.
You have to add code to find the last used row and then increment the row number
-
Nov 28th, 2020, 03:44 PM
#7
Thread Starter
New Member
Re: Using Visual Basic to update excel file
Arnoutdv i know, thats what i am trying to figure out, the last time i used this i was in school, that many, many years ago. I am trying to feel my way through, but i have been searching online with no luck.
-
Nov 28th, 2020, 04:51 PM
#8
Re: Using Visual Basic to update excel file
Is there a specific reason for putting the data in an excel sheet?
Excel is a spreadsheet program, not a database
-
Nov 28th, 2020, 05:46 PM
#9
Thread Starter
New Member
Re: Using Visual Basic to update excel file
Because i need to manipulate the data in a spreadsheet. Would you be able to help me with this incremented row loop?
-
Nov 28th, 2020, 06:37 PM
#10
Re: Using Visual Basic to update excel file
The easiest way to learn Excel automation is be recording macros in Excel.
So start wit a blank workbook, put manually some data in the first few rows.
Then select the top left cell, A1.
Now start the macro recorder and use the keyboard shortcut to jump to the last line.
Stop the macro recorder and inspect the code.
This should give you some clues how to jump to the last line in your sheet and the increase the row number and put your data in first empty row
-
Nov 28th, 2020, 09:03 PM
#11
Thread Starter
New Member
Re: Using Visual Basic to update excel file
The issue is not to do a VBA automation in Excel, my challenge is running the excel automation using VB, the arguments to do this is not the same as VBA.
-
Nov 28th, 2020, 09:35 PM
#12
Re: Using Visual Basic to update excel file
Here is a class that can read and write from an Excel file. It's a portion of a small APP I sold for 83 US dollars so it definitely works. I've really never dealt with Excel outside of this project so I can't tell you how to solve your problem with any confidence but perhaps something in that class can give you an idea.
vbnet Code:
'
Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Public Class SimpleExcel
Implements IDisposable
Private _application As Application
Private _workBook As Workbook
Private _workSheet As Worksheet
Private _bExcelOpen As Boolean = False
Public Sub New()
Me.New(Nothing)
End Sub
Public Sub New(ByVal fileName As String)
_application = New Application
_application.DisplayAlerts = False
If String.IsNullOrEmpty(fileName) Then
_workBook = _application.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
Else
_workBook = _application.Workbooks.Open(fileName)
End If
_workSheet = _workBook.Worksheets.Item(1)
End Sub
Public Sub OpenExcel()
_bExcelOpen = True
_application.Visible = True
End Sub
Public Sub AutoFit()
_workSheet.Columns.AutoFit()
End Sub
Public Sub WriteExcelData(ByVal rowIndex As Integer, ByVal columnIndex As Integer, text As String)
Marshal.FinalReleaseComObject(Me.InternalWriteExcelData(rowIndex, columnIndex, text))
End Sub
Public Sub WriteExcelData(ByVal rowIndex As Integer, ByVal columnIndex As Integer, ByVal moneyValue As Decimal)
Dim r As Range = Me.InternalWriteExcelData(rowIndex, columnIndex, moneyValue.ToString)
r.NumberFormat = "$###,###,###,###,###.00"
Marshal.FinalReleaseComObject(r)
End Sub
Public Function ReadExcelData(ByVal rowIndex As Integer, ByVal columnIndex As Integer) As String
Dim r As Range = _workSheet.Cells(rowIndex + 1, columnIndex + 1)
Try
Return CStr(r.Value)
Finally
Marshal.FinalReleaseComObject(r)
End Try
End Function
Private Function InternalWriteExcelData(ByVal rowIndex As Integer, ByVal columnIndex As Integer, ByVal value As String) As Range
Dim r As Range = _workSheet.Cells(rowIndex + 1, columnIndex + 1)
r.Value = value
Return r
End Function
Public Sub Save(ByVal fileName As String)
_workBook.SaveAs(fileName)
End Sub
Public Sub Close()
Me.Dispose()
End Sub
#Region "IDisposable Support"
Private disposedValue As Boolean ' To detect redundant calls
' IDisposable
Protected Overridable Sub Dispose(disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
' TODO: dispose managed state (managed objects).
End If
' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
' TODO: set large fields to null.
End If
If Not _bExcelOpen Then
_workBook.Close()
End If
Marshal.FinalReleaseComObject(_workBook)
Marshal.FinalReleaseComObject(_workSheet)
If Not _bExcelOpen Then
_application.Quit()
End If
Marshal.FinalReleaseComObject(_application)
Me.disposedValue = True
End Sub
' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
'Protected Overrides Sub Finalize()
' ' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
' Dispose(False)
' MyBase.Finalize()
'End Sub
' This code added by Visual Basic to correctly implement the disposable pattern.
Public Sub Dispose() Implements IDisposable.Dispose
' Do not change this code. Put cleanup code in Dispose(disposing As Boolean) above.
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region
End Class
-
Nov 29th, 2020, 03:45 AM
#13
Re: Using Visual Basic to update excel file
Originally Posted by Kmakjop
The issue is not to do a VBA automation in Excel, my challenge is running the excel automation using VB, the arguments to do this is not the same as VBA.
You didn’t get the hint.
Sample here:
https://www.vbforums.com/showthread....=1#post4249141
-
Nov 29th, 2020, 06:30 AM
#14
Re: Using Visual Basic to update excel file
Originally Posted by Kmakjop
Because i need to manipulate the data in a spreadsheet. Would you be able to help me with this incremented row loop?
you can manipulate the Data in a Database Table, much easier than in the spreadsheet.
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.
-
Nov 30th, 2020, 07:54 AM
#15
Re: Using Visual Basic to update excel file
Originally Posted by Arnoutdv
Is there a specific reason for putting the data in an excel sheet?
Excel is a spreadsheet program, not a database
That has been discussed here many times and in my opinion, by definition, Excel is a database. It does not have a DBMS over it but is a database. I realize that in most programming forums it is not considered a "true" database for discussion purposes but if the OP wants it as a database than I say go for it. Same thing for text files. They are just not relational.
Last edited by TysonLPrice; Nov 30th, 2020 at 08:18 AM.
Please remember next time...elections matter!
-
Nov 30th, 2020, 08:36 AM
#16
Re: Using Visual Basic to update excel file
I agree to disagree here :-)
When you see to troubles you have to get into to add rows and update cells using Office Automation then this would never be my first choice for data storage.
Even a plain text file better.
And when you are going to use ADO to access to the data from an Excel file then why not use a MDB file.
-
Nov 30th, 2020, 08:47 AM
#17
Re: Using Visual Basic to update excel file
Originally Posted by Arnoutdv
I agree to disagree here :-)
When you see to troubles you have to get into to add rows and update cells using Office Automation then this would never be my first choice for data storage.
Even a plain text file better.
And when you are going to use ADO to access to the data from an Excel file then why not use a MDB file.
It would never be my first choice either. I'm just commenting that by definition Excel is a database. I disagree when you say "Excel is a spreadsheet program, not a database". It can be used as a database. Lots of people do that. It just isn't what most programmers refer to as a "real" database. Here are some database types:
Centralised database.
Distributed database.
Personal database.
End-user database.
Commercial database.
NoSQL database.
Operational database.
Relational database.
Please remember next time...elections matter!
-
Nov 30th, 2020, 12:04 PM
#18
Re: Using Visual Basic to update excel file
Does using a screwdriver handle like a hammer make the screwdriver a hammer? Maybe, but it certainly won't be as effective. And people will tell you if you need a hammer, use a hammer - oh, and don't expect me to help you to more effectively use a screwdriver to hammer something.
-
Nov 30th, 2020, 12:12 PM
#19
Re: Using Visual Basic to update excel file
Originally Posted by OptionBase1
Does using a screwdriver handle like a hammer make the screwdriver a hammer? Maybe, but it certainly won't be as effective. And people will tell you if you need a hammer, use a hammer - oh, and don't expect me to help you to more effectively use a screwdriver to hammer something.
I really don't know what your point is...NOBODY said using Excel as a database is a good idea. The point is that by definition it is a database. Just as by definition a screw driver drives screws. Not too many people would suggest using it for a hammer though.
Please remember next time...elections matter!
-
Nov 30th, 2020, 12:42 PM
#20
Re: Using Visual Basic to update excel file
if were to use a Hammer I would go with Powerpoint as a Database
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.
-
Nov 30th, 2020, 12:45 PM
#21
Re: Using Visual Basic to update excel file
Originally Posted by ChrisE
if were to use a Hammer I would go with Powerpoint as a Database
I wonder if there are any electric rolodexs?
Please remember next time...elections matter!
-
Nov 30th, 2020, 12:48 PM
#22
Re: Using Visual Basic to update excel file
Originally Posted by TysonLPrice
I wonder if there are any electric rolodexs?
you got me there
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.
-
Nov 30th, 2020, 05:39 PM
#23
Re: Using Visual Basic to update excel file
Originally Posted by TysonLPrice
I wonder if there are any electric rolodexs?
yes there is.... https://www.youtube.com/watch?v=34T-Kh7Foj4
The best friend of any programmer is a search engine
"Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
“They did not know it was impossible so they did it” (Mark Twain)
-
Dec 1st, 2020, 07:13 AM
#24
Re: Using Visual Basic to update excel file
Originally Posted by Delaney
I didn't see a USB port...I wonder how the interface to VB .NET works? Maybe WiFi.
Please remember next time...elections matter!
-
Dec 1st, 2020, 08:27 AM
#25
Re: Using Visual Basic to update excel file
Originally Posted by TysonLPrice
I really don't know what your point is...NOBODY said using Excel as a database is a good idea. The point is that by definition it is a database. Just as by definition a screw driver drives screws. Not too many people would suggest using it for a hammer though.
Personally I go with the more generic term of DataStore or a DataSource, which would include Excel, XML, JSON, Databases, text files, CSV, etc.
Meanwhile, can we get the thread back on track? It went off the rails so far that I'm not sure the original problem got solved.
-tg
-
Dec 1st, 2020, 08:54 AM
#26
Re: Using Visual Basic to update excel file
There are many popular "true" databases that are not relational databases. An Excel spreadsheet can be used as a database. It wasn't designed with that in mind but that kind of use is not prohibited.
-
Dec 1st, 2020, 09:42 AM
#27
Re: Using Visual Basic to update excel file
Originally Posted by TysonLPrice
I didn't see a USB port...I wonder how the interface to VB .NET works? Maybe WiFi.
that's because you need an adapter that you can interface with :https://www.youtube.com/watch?v=rNSC...ature=emb_logo
The best friend of any programmer is a search engine
"Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
“They did not know it was impossible so they did it” (Mark Twain)
-
Dec 2nd, 2020, 01:48 AM
#28
Re: Using Visual Basic to update excel file
Originally Posted by Kmakjop
Sorry about not initially showing the code, see below:
Code:
Imports Microsoft.Office.Interop
Public Class form1
Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
Dim app As New Excel.Application
Dim wk As Excel.Workbook = app.Workbooks.Open(Application.StartupPath & "\Good.xlsx",, True)
Dim wh As Excel.Worksheet = app.Worksheets(1)
wh.range("A2").value = txtID.Text
wh.range("B2").value = txtStuName.Text
wh.range("C2").value = cboGender.Text
wh.range("D2").value = dtDOM.Text
app.Visible = True
End Sub
End Class
try this way for adding a new row
Code:
'Ref:
Option Strict On
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Try
Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
xlWb = xlApp.Workbooks.Open("E:\Names.xls")
Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("mySheet1"), Worksheet)
Dim rowRange = xlSt.UsedRange.Rows.Count 'count the used rows
Dim NewRow = rowRange + 1 '+1 is for the new Row
With xlSt
.Range("A" & NewRow).Value = "Test"
.Range("B" & NewRow).Value = "Test5"
.Range("C" & NewRow).Value = 55
.Range("D" & NewRow).Value = 123
End With
xlWb.Save()
xlApp.Quit()
xlApp = Nothing
Catch g As Exception
MessageBox.Show(g.ToString)
End Try
End Sub
here a Image
the other option for adding or updating is OLEDB
here a sample for Updating, I use the Sheet that you see in the Image
since I have in Row 1 Headers = FirstName, LastName etc...
I use those in the SQL to Select what I want to Update
Code:
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.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
Last edited by ChrisE; Dec 2nd, 2020 at 02:30 AM.
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.
-
Dec 4th, 2020, 12:06 AM
#29
Lively Member
Re: Using Visual Basic to update excel file
Code:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
For more information follow this link
https://docs.microsoft.com/en-us/off...-excel-from-vb
Last edited by Prahlad; Dec 4th, 2020 at 12:08 AM.
Reason: improve Post
-
Dec 4th, 2020, 12:59 AM
#30
Re: Using Visual Basic to update excel file
Originally Posted by Prahlad
Code:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
you have posted the same code that the OP posted in Post#4
so I don't know how this is going to help the OP
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.
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
|