locked
Loops and counters

    Question

  • I am trying to make something that will insert data into excel (I already posted this on the VBA forums but no response, but if someone can give me the starting point I can figure out the excel part).

    Bottom line is I want to insert data in to a column like:
    vp0001.jpg
    vp0002.jpg
    vp0003.jpg
    vp00XX.jpg etc. until the last row.

    My question is how can I make a simple counter to generate the numbers 0001, 0002, etc.  I also would need one that can generate numbers like 1,2,3 and start with number 2 so I can tell it which excel row to go into.

    I know the general idea of how to do this but I have never been good with counters and loops.
    Thursday, October 12, 2006 11:01 AM

Answers

  • Add a reference to the excel object model class library.   The following will write the excel spreadsheet you are asking for.

     

    Imports Microsoft.Office.Interop

    Public Class Form1

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


            Try
                Dim oXL As Excel.Application
                Dim oWB As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                Dim oRng As Excel.Range


                'On Error GoTo Err_Handler

                ' Start Excel and get Application object.
                oXL = CreateObject("Excel.Application")
                oXL.Visible = True

                ' Get a new workbook.
                oWB = oXL.Workbooks.Add
                oSheet = oWB.ActiveSheet

                ' Add cells by looping.
                Dim iRow As Integer = 1
                For iRow = 1 To 10
                    oSheet.Cells(iRow, 1).Value = "vp" & Format(iRow, "0000") & ".jpg"
                Next iRow


                ' Make sure Excel is visible and give the user control
                ' of Microsoft Excel's lifetime.
                oXL.Visible = True
                oXL.UserControl = True

                ' Make sure you release object references.
                oRng = Nothing
                oSheet = Nothing
                oWB = Nothing
                oXL = Nothing
            Catch ex As Exception
                MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
            End Try
        End Sub
    End Class

    Friday, October 20, 2006 3:05 AM

All replies

  • Dim i As Integer

    For i = 0 To 10

    Console.WriteLine(Format(i, "0000"))

    Next

    Thursday, October 12, 2006 12:55 PM
  • Add a reference to the excel object model class library.   The following will write the excel spreadsheet you are asking for.

     

    Imports Microsoft.Office.Interop

    Public Class Form1

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


            Try
                Dim oXL As Excel.Application
                Dim oWB As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                Dim oRng As Excel.Range


                'On Error GoTo Err_Handler

                ' Start Excel and get Application object.
                oXL = CreateObject("Excel.Application")
                oXL.Visible = True

                ' Get a new workbook.
                oWB = oXL.Workbooks.Add
                oSheet = oWB.ActiveSheet

                ' Add cells by looping.
                Dim iRow As Integer = 1
                For iRow = 1 To 10
                    oSheet.Cells(iRow, 1).Value = "vp" & Format(iRow, "0000") & ".jpg"
                Next iRow


                ' Make sure Excel is visible and give the user control
                ' of Microsoft Excel's lifetime.
                oXL.Visible = True
                oXL.UserControl = True

                ' Make sure you release object references.
                oRng = Nothing
                oSheet = Nothing
                oWB = Nothing
                oXL = Nothing
            Catch ex As Exception
                MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
            End Try
        End Sub
    End Class

    Friday, October 20, 2006 3:05 AM