none
Help me fix my code. RRS feed

  • Question

  • Can some body give me a hand ?

     Dim xlApp As New Excel.Application
            Dim xlWb As Excel.Workbook
            Dim CurrentRange As Excel.Range
            Dim strFileName As String
            strFileName = My.Application.Info.DirectoryPath & "\Proba.xls"
            CurrentRange = xlWb.Worksheets(2).Range("A4")
            xlWb = xlApp.Workbooks.Add
            If IsEmpty(CurrentRange.Value) Then
                CurrentRange.Value = Iznos.Text
                MessageBox.Show("Insert Success")
            End If
            CurrentRange = CurrentRange.Offset(1, 0)
            xlWb.SaveAs(My.Application.Info.DirectoryPath & "\Proba.xls")
            xlApp.DisplayAlerts = True
            xlWb.Close(False)
            xlApp.Quit()
        End Sub

    1) I get some warnings

    2)I get an error saying that xlWb has been previously set as Null

    3)Nothing is happening.

    Here are SS:

    http://s17.postimg.org/ajenlmj1b/Screenshot_1.png

    http://s14.postimg.org/emaiehxg1/Screenshot_2.png

    Saturday, October 12, 2013 7:32 AM

Answers

  • Hi WebPenTester,

    i have made my changes bold, please check now it should work fine.

    Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook = xlApp.Workbooks.Add
        Dim CurrentRange As Excel.Range
        Dim strFileName As String
        strFileName = My.Application.Info.DirectoryPath & "\Proba.xls"
        CurrentRange = xlWb.Worksheets(2).Range("A4")
        If IsEmpty(CurrentRange.Value) Then
            CurrentRange.Value = Iznos.Text
            MessageBox.Show("Insert Success")
        End If
        CurrentRange = CurrentRange.Offset(1, 0)
        xlWb.SaveAs(My.Application.Info.DirectoryPath & "\Proba.xls")
        xlApp.DisplayAlerts = True
        xlWb.Close(False)
        xlApp.Quit()

    Actually you were accessing the variable xlWb before assigning it value,

    Dim xlWb As Excel.Workbook this statement only declares the variable not assign it, hence i combined your initialised statement before accessing them.

    Thanks,

    Amit

    -------------------------------------------------

    Please mark this as answer if it helps you



    • Marked as answer by BeAwareAlien Saturday, October 12, 2013 8:00 AM
    • Edited by Amit Tonk Saturday, October 12, 2013 8:04 AM
    Saturday, October 12, 2013 7:59 AM
  • Hi WebPenTester,

    Here is your code

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim strFileName As String
            strFileName = My.Application.Info.DirectoryPath & "\excel.xls"
            Dim xlApp As New Excel.Application
            Dim xlWb As Excel.Workbook = Nothing
            Dim CurrentRange As Excel.Range = Nothing
            If IO.File.Exists(strFileName) Then
                xlWb = xlApp.Workbooks.Open(strFileName)
                If (xlWb.Worksheets.Count > 1) Then
                    For columnIndex As Integer = 1 To 256
                        CurrentRange = CType(xlWb.Worksheets(2).Cells(1, columnIndex), Excel.Range)
                        If String.IsNullOrEmpty(CurrentRange.Value) Then
                            CurrentRange.Value = Text1.Text
                            MessageBox.Show("Insert Success")
                            Exit For
                        End If
                    Next
                End If
            Else
                xlWb = xlApp.Workbooks.Add()
                CurrentRange = xlWb.Worksheets(2).Range("A1")
                CurrentRange.Value = Text1.Text
                MessageBox.Show("Insert Success")
            End If
            CurrentRange = CurrentRange.Offset(1, 0)
            xlApp.DisplayAlerts = True
            If IO.File.Exists(strFileName) Then
                xlWb.Save()
            Else
                xlWb.SaveAs(strFileName)
            End If
            xlWb.Close(False)
            xlApp.Quit()
            ReleaseComObject(CurrentRange)
            ReleaseComObject(xlWb)
            ReleaseComObject(xlApp)
        End Sub

        Public Sub ReleaseComObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub

    Regards

    Amit Tonk

    ----------------

    Please mark this as answer if it helps you

    • Marked as answer by BeAwareAlien Saturday, October 12, 2013 4:24 PM
    Saturday, October 12, 2013 4:17 PM

All replies

  • Hi WebPenTester,

    i have made my changes bold, please check now it should work fine.

    Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook = xlApp.Workbooks.Add
        Dim CurrentRange As Excel.Range
        Dim strFileName As String
        strFileName = My.Application.Info.DirectoryPath & "\Proba.xls"
        CurrentRange = xlWb.Worksheets(2).Range("A4")
        If IsEmpty(CurrentRange.Value) Then
            CurrentRange.Value = Iznos.Text
            MessageBox.Show("Insert Success")
        End If
        CurrentRange = CurrentRange.Offset(1, 0)
        xlWb.SaveAs(My.Application.Info.DirectoryPath & "\Proba.xls")
        xlApp.DisplayAlerts = True
        xlWb.Close(False)
        xlApp.Quit()

    Actually you were accessing the variable xlWb before assigning it value,

    Dim xlWb As Excel.Workbook this statement only declares the variable not assign it, hence i combined your initialised statement before accessing them.

    Thanks,

    Amit

    -------------------------------------------------

    Please mark this as answer if it helps you



    • Marked as answer by BeAwareAlien Saturday, October 12, 2013 8:00 AM
    • Edited by Amit Tonk Saturday, October 12, 2013 8:04 AM
    Saturday, October 12, 2013 7:59 AM
  • Will check it, I'm not home atm. Thank you for you reply very much.

    God bless you my friend.

    :)

    Regards

    Saturday, October 12, 2013 8:00 AM
  • It haven't solved my problem.

    Here is my full code can you take a look and solve it ?

    Imports Microsoft.Office.Interop
    Imports System.IO
    Public Class frmMain
    
        Private _isEmpty As Boolean
    
        Private Property IsEmpty(ByVal p1 As Object) As Boolean
            Get
                Return _isEmpty
            End Get
            Set(ByVal value As Boolean)
                _isEmpty = 1
            End Set
        End Property
    
        Private Sub klik_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles klix.Click
            Dim xlApp As New Excel.Application
            Dim xlWb As Excel.Workbook = xlApp.Workbooks.Add
            Dim CurrentRange As Excel.Range
            Dim strFileName As String
            strFileName = My.Application.Info.DirectoryPath & "\excel.xls"
            CurrentRange = xlWb.Worksheets(2).Range("A1")
            If File.Exists(strFileName) Then
                xlApp.Workbooks.Open(strFileName)
            Else
                xlApp.Workbooks.Add()
            End If
            If IsEmpty(CurrentRange.Value) Then
                CurrentRange.Value = Text1.Text
                MessageBox.Show("Insert Success")
            End If
            CurrentRange = CurrentRange.Offset(1, 0)
            xlWb.SaveAs(strFileName)
            xlApp.DisplayAlerts = True
            xlWb.Close(False)
            xlApp.Quit()
        End Sub
    
    End Class

    Saturday, October 12, 2013 12:38 PM
  • Any solution for this ?

    Imports Microsoft.Office.Interop
    Imports System.IO
    Public Class frmMain
    
        Private _isEmpty As Boolean
    
        Private Property IsEmpty(ByVal p1 As Object) As Boolean
            Get
                Return _isEmpty
            End Get
            Set(ByVal value As Boolean)
                _isEmpty = 1
            End Set
        End Property
    
        Private Sub klik_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles klix.Click
            Dim xlApp As New Excel.Application
            Dim xlWb As Excel.Workbook = xlApp.Workbooks.Add
            Dim CurrentRange As Excel.Range
            Dim strFileName As String
            strFileName = My.Application.Info.DirectoryPath & "\excel.xls"
            CurrentRange = xlWb.Worksheets(2).Range("A1")
            If File.Exists(strFileName) Then
                xlApp.Workbooks.Open(strFileName)
            Else
                xlApp.Workbooks.Add()
            End If
            If IsEmpty(CurrentRange.Value) Then
                CurrentRange.Value = Text1.Text
                MessageBox.Show("Insert Success")
            End If
            CurrentRange = CurrentRange.Offset(1, 0)
            xlWb.SaveAs(strFileName)
            xlApp.DisplayAlerts = True
            xlWb.Close(False)
            xlApp.Quit()
        End Sub
    
    End Class

    Saturday, October 12, 2013 12:39 PM
  • Hi WebPenTester,

    earlier while answering your first question, i created this project. So it didn't take much time to reformat and correct it. I tried my best and its working too.

    Please check.

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim strFileName As String
            strFileName = My.Application.Info.DirectoryPath & "\excel.xls"
            Dim xlApp As New Excel.Application
            Dim xlWb As Excel.Workbook = Nothing
            Dim CurrentRange As Excel.Range = Nothing
            If IO.File.Exists(strFileName) Then
                xlWb = xlApp.Workbooks.Open(strFileName)
                If (xlWb.Worksheets.Count > 1) Then
                    CurrentRange = xlWb.Worksheets(2).Range("A1")
                    If IsEmpty(CurrentRange.Value) Then
                        CurrentRange.Value = Text1.Text
                        MessageBox.Show("Insert Success")
                    End If
                End If
            Else
                xlWb = xlApp.Workbooks.Add()
                CurrentRange = xlWb.Worksheets(2).Range("A1")
                CurrentRange.Value = Text1.Text
                MessageBox.Show("Insert Success")
            End If
            CurrentRange = CurrentRange.Offset(1, 0)
            xlWb.SaveAs(strFileName)
            xlApp.DisplayAlerts = True
            xlWb.Close(False)
            xlApp.Quit()
    End Sub


    Thanks,
    Amit
    ----------------

    Please mark this as answer/helpful if it helps you.

    Saturday, October 12, 2013 1:11 PM
  • Let me explain you my needs.

    This solution creates ( opens ) a file and inserts in it Text1.Text into A1, after submiting again it asks me to overwrite the file.

    I need a solution that will open a file and If A1 is filled, to skip it and go to A2, now if A2 is empty to insert Text1.Text into A2, in case that A2 is filled it goes to A3.

    And after Submiting to Save Automaticly the Excel Workfile.

    Hope you understood my need and you will be able to help me.

    God bless you.

    Saturday, October 12, 2013 2:32 PM
  • Hi WebPenTester,

    Here is your code

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim strFileName As String
            strFileName = My.Application.Info.DirectoryPath & "\excel.xls"
            Dim xlApp As New Excel.Application
            Dim xlWb As Excel.Workbook = Nothing
            Dim CurrentRange As Excel.Range = Nothing
            If IO.File.Exists(strFileName) Then
                xlWb = xlApp.Workbooks.Open(strFileName)
                If (xlWb.Worksheets.Count > 1) Then
                    For columnIndex As Integer = 1 To 256
                        CurrentRange = CType(xlWb.Worksheets(2).Cells(1, columnIndex), Excel.Range)
                        If String.IsNullOrEmpty(CurrentRange.Value) Then
                            CurrentRange.Value = Text1.Text
                            MessageBox.Show("Insert Success")
                            Exit For
                        End If
                    Next
                End If
            Else
                xlWb = xlApp.Workbooks.Add()
                CurrentRange = xlWb.Worksheets(2).Range("A1")
                CurrentRange.Value = Text1.Text
                MessageBox.Show("Insert Success")
            End If
            CurrentRange = CurrentRange.Offset(1, 0)
            xlApp.DisplayAlerts = True
            If IO.File.Exists(strFileName) Then
                xlWb.Save()
            Else
                xlWb.SaveAs(strFileName)
            End If
            xlWb.Close(False)
            xlApp.Quit()
            ReleaseComObject(CurrentRange)
            ReleaseComObject(xlWb)
            ReleaseComObject(xlApp)
        End Sub

        Public Sub ReleaseComObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub

    Regards

    Amit Tonk

    ----------------

    Please mark this as answer if it helps you

    • Marked as answer by BeAwareAlien Saturday, October 12, 2013 4:24 PM
    Saturday, October 12, 2013 4:17 PM
  • Thank you Amit Tonk, but can you help me identify the row, I need it to go cell down not cell next
    Saturday, October 12, 2013 4:24 PM
  • Hi WebPenTester,

    Your last comment was not clear to me, please elaborate. Actually as per your need the code now checks for all columns in 1st row till 256th column(last column) for existing value also now no overwrite pop-ups comes. Is this something you are talking about.

    Regards,

    Amit Tonk

    Saturday, October 12, 2013 4:44 PM
  • Lets say that I've inserted in A1 a text, now the next insert should go to A2. Now your goes to B1.

    Do you understand me ?

    Now there is a problem as I cant define a range for multiple textboxes in different ranges ?

    like Text2.text, text3.text lets say text2 in C5 to down, text3 in E7 to down.

    Saturday, October 12, 2013 4:45 PM
  • Amit Tonk

    Please can you help me fix this ?

    Sunday, October 13, 2013 6:42 PM
  • Sure :),

    But first let me confirm my understanding on it, the current code actually checks with in a single row upto 256th column, but now you are saying that if A1 is already fillled then A2 should be checked, and if A2 is also filled then B1 should be checked, then B2,C1,C2,D1,D2,etc.

    So actually it should go upto 2 columns only in a row and after that switch to another row.

    Is there any limit on number of rows which could be checked ?

    Thanks,

    Amit Tonk

    Monday, October 14, 2013 6:16 AM
  • Thats correct only note that it should go down unlimitd, as much as it can go. And please could you help me identify where is the range. I have 3 textboxes, and for every the range is different. TextBox1.Text A4 ( from A4 to A5, A6, and so on), TextBox2.Text from B6 ( to B7, B8, and so on). I hope God will reward you for helping me. Many love Amit Tonk
    Monday, October 14, 2013 6:22 AM
  • Amit Tonk any update ? :(
    Monday, October 14, 2013 1:13 PM
  • Hi,

    Here is the updated code, yesterday i didn't get time to check the code.

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim strFileName As String
            strFileName = My.Application.Info.DirectoryPath & "\excel.xls"
            Dim xlApp As New Excel.Application
            Dim xlWb As Excel.Workbook = Nothing
            Dim CurrentRange As Excel.Range = Nothing
            Dim isValueSet As Boolean = False
            If IO.File.Exists(strFileName) Then
                xlWb = xlApp.Workbooks.Open(strFileName)
                If (xlWb.Worksheets.Count > 1) Then
                    For rowIndex As Integer = 1 To 100000 ' check the maximum number of rows supported in your excel version and change this value according to that
                        For columnIndex As Integer = 1 To 2
                            CurrentRange = DirectCast(xlWb.Worksheets(2).Cells(rowIndex, columnIndex), Excel.Range) 'CType(xlWb.Worksheets(2).Cells(rowIndex, columnIndex), Excel.Range)
                            If String.IsNullOrEmpty(CurrentRange.Value) Then
                                'xlWb.Worksheets(2).Cells(rowIndex, columnIndex).value = Text1.Text
                                CurrentRange.Value = Text1.Text
                                isValueSet = True
                                MessageBox.Show("Insert Success")
                                Exit For
                            End If
                        Next
                        If isValueSet = True Then
                            Exit For
                        End If
                    Next
                End If
            Else
                xlWb = xlApp.Workbooks.Add()
                CurrentRange = xlWb.Worksheets(2).Range("A1")
                CurrentRange.Value = Text1.Text
                MessageBox.Show("Insert Success")
            End If
            xlApp.DisplayAlerts = True
            If IO.File.Exists(strFileName) Then
                xlWb.Save()
            Else
                xlWb.SaveAs(strFileName)
            End If
            CurrentRange = Nothing
            xlWb.Close(False)
            xlApp.Quit()
            ReleaseComObject(CurrentRange)
            ReleaseComObject(xlWb)
            ReleaseComObject(xlApp)
        End Sub

        Public Sub ReleaseComObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub

    Also i didn't get these lines,  "I have 3 textboxes, and for every the range is different. TextBox1.Text A4 ( from A4 to A5, A6, and so on), TextBox2.Text from B6 ( to B7, B8, and so on)." are there multiple textboxes on your form.

    Thanks,

    Amit Tonk

    -------------

    Please mark this post as answer if it helps you.


    • Edited by Amit Tonk Tuesday, October 15, 2013 5:39 AM
    Tuesday, October 15, 2013 5:36 AM