Results 1 to 30 of 30

Thread: Using Visual Basic to update excel file

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    7

    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.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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.

  3. #3
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: Using Visual Basic to update excel file

    Show your code.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  4. #4

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    7

    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

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    7

    Re: Using Visual Basic to update excel file

    Is there anyone that can help with this please?

  6. #6
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    7

    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.

  8. #8
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    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

  9. #9

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    7

    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?

  10. #10
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    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

  11. #11

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    7

    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.

  12. #12
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    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:
    1. '
    2. Imports Microsoft.Office.Interop.Excel
    3. Imports System.Runtime.InteropServices
    4.  
    5. Public Class SimpleExcel
    6.     Implements IDisposable
    7.  
    8.     Private _application As Application
    9.     Private _workBook As Workbook
    10.     Private _workSheet As Worksheet
    11.  
    12.     Private _bExcelOpen As Boolean = False
    13.     Public Sub New()
    14.         Me.New(Nothing)
    15.     End Sub
    16.  
    17.     Public Sub New(ByVal fileName As String)
    18.  
    19.         _application = New Application
    20.  
    21.         _application.DisplayAlerts = False
    22.  
    23.         If String.IsNullOrEmpty(fileName) Then
    24.             _workBook = _application.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
    25.         Else
    26.             _workBook = _application.Workbooks.Open(fileName)
    27.         End If
    28.  
    29.         _workSheet = _workBook.Worksheets.Item(1)
    30.  
    31.     End Sub
    32.  
    33.     Public Sub OpenExcel()
    34.         _bExcelOpen = True
    35.         _application.Visible = True
    36.     End Sub
    37.  
    38.     Public Sub AutoFit()
    39.         _workSheet.Columns.AutoFit()
    40.     End Sub
    41.  
    42.     Public Sub WriteExcelData(ByVal rowIndex As Integer, ByVal columnIndex As Integer, text As String)
    43.         Marshal.FinalReleaseComObject(Me.InternalWriteExcelData(rowIndex, columnIndex, text))
    44.     End Sub
    45.  
    46.     Public Sub WriteExcelData(ByVal rowIndex As Integer, ByVal columnIndex As Integer, ByVal moneyValue As Decimal)
    47.         Dim r As Range = Me.InternalWriteExcelData(rowIndex, columnIndex, moneyValue.ToString)
    48.         r.NumberFormat = "$###,###,###,###,###.00"
    49.  
    50.         Marshal.FinalReleaseComObject(r)
    51.     End Sub
    52.  
    53.     Public Function ReadExcelData(ByVal rowIndex As Integer, ByVal columnIndex As Integer) As String
    54.         Dim r As Range = _workSheet.Cells(rowIndex + 1, columnIndex + 1)
    55.  
    56.         Try
    57.             Return CStr(r.Value)
    58.         Finally
    59.             Marshal.FinalReleaseComObject(r)
    60.         End Try
    61.     End Function
    62.  
    63.     Private Function InternalWriteExcelData(ByVal rowIndex As Integer, ByVal columnIndex As Integer, ByVal value As String) As Range
    64.  
    65.         Dim r As Range = _workSheet.Cells(rowIndex + 1, columnIndex + 1)
    66.         r.Value = value
    67.  
    68.         Return r
    69.     End Function
    70.     Public Sub Save(ByVal fileName As String)
    71.         _workBook.SaveAs(fileName)
    72.     End Sub
    73.  
    74.     Public Sub Close()
    75.         Me.Dispose()
    76.     End Sub
    77.  
    78. #Region "IDisposable Support"
    79.     Private disposedValue As Boolean ' To detect redundant calls
    80.  
    81.     ' IDisposable
    82.     Protected Overridable Sub Dispose(disposing As Boolean)
    83.         If Not Me.disposedValue Then
    84.             If disposing Then
    85.                 ' TODO: dispose managed state (managed objects).
    86.             End If
    87.  
    88.  
    89.             ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
    90.             ' TODO: set large fields to null.
    91.         End If
    92.  
    93.         If Not _bExcelOpen Then
    94.             _workBook.Close()
    95.         End If
    96.  
    97.         Marshal.FinalReleaseComObject(_workBook)
    98.         Marshal.FinalReleaseComObject(_workSheet)
    99.  
    100.         If Not _bExcelOpen Then
    101.             _application.Quit()
    102.         End If
    103.         Marshal.FinalReleaseComObject(_application)
    104.  
    105.  
    106.         Me.disposedValue = True
    107.     End Sub
    108.  
    109.     ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
    110.     'Protected Overrides Sub Finalize()
    111.     '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
    112.     '    Dispose(False)
    113.     '    MyBase.Finalize()
    114.     'End Sub
    115.  
    116.     ' This code added by Visual Basic to correctly implement the disposable pattern.
    117.     Public Sub Dispose() Implements IDisposable.Dispose
    118.         ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
    119.         Dispose(True)
    120.         GC.SuppressFinalize(Me)
    121.     End Sub
    122. #End Region
    123.  
    124. End Class
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  13. #13
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by Kmakjop View Post
    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

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

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by Kmakjop View Post
    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.

  15. #15
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by Arnoutdv View Post
    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!

  16. #16
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    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.

  17. #17
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by Arnoutdv View Post
    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!

  18. #18
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    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.

  19. #19
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by OptionBase1 View Post
    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!

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

    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.

  21. #21
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by ChrisE View Post
    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!

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

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by TysonLPrice View Post
    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.

  23. #23
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by TysonLPrice View Post
    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)

  24. #24
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by Delaney View Post
    I didn't see a USB port...I wonder how the interface to VB .NET works? Maybe WiFi.
    Please remember next time...elections matter!

  25. #25
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by TysonLPrice View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  26. #26
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    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.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  27. #27
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by TysonLPrice View Post
    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)

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

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by Kmakjop View Post
    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
    Name:  Names.jpg
Views: 1470
Size:  41.3 KB

    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.

  29. #29
    Lively Member
    Join Date
    Jan 2020
    Posts
    120

    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

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

    Re: Using Visual Basic to update excel file

    Quote Originally Posted by Prahlad View Post
    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
  •  



Click Here to Expand Forum to Full Width