none
how to send data to multiple rows RRS feed

Answers

  • Hello:

    The answer to your question is yes.  It is a common developer's procedure to get input from forms and place them in various cells.

    First, as you probably know, every combo-box and text box has a name.  One common method is to also include a button on the form so that when all the data is collected, the button is pushed.  That button would be assigned a "Click" event.  In that event, you would move the value from the named combo-box or text box to the cell(s) of your choice.  As sample statement might be something like this:

    Private Sub SaveContactInformation()
    
    Select Case intGblLastContactNumberUpdated
        Case 1
            wksContactStagingArea.Cells(56, 2) = Me.txtContactTitle.Value
            wksContactStagingArea.Cells(57, 2) = Me.txtContactName.Value
            wksContactStagingArea.Cells(58, 2) = Me.txtOfficePhone.Value
            wksContactStagingArea.Cells(59, 2) = Me.txtCellPhone.Value
            wksContactStagingArea.Cells(60, 2) = Me.txtFaxNumber.Value
            wksContactStagingArea.Cells(61, 2) = Me.txtEmailAddress.Value
            
            If IsDate(wksContactStagingArea.Cells(62, 2)) Then
                wksContactStagingArea.Cells(63, 2) = Date
            Else
                wksContactStagingArea.Cells(62, 2) = Date
                wksContactStagingArea.Cells(63, 2) = Date
            End If
            
            If IsDate(wksContactStagingArea.Cells(56, 3)) Then
                Range(wksContactStagingArea.Cells(56, 4), wksContactStagingArea.Cells(61, 4)) = Date
            Else
                Range(wksContactStagingArea.Cells(56, 3), wksContactStagingArea.Cells(61, 3)) = Date
            End If
            
            Call UpdateMasterCreatedRevisedDates(Date)
            
        Case 2
            wksContactStagingArea.Cells(66, 2) = Me.txtContactTitle.Value
            wksContactStagingArea.Cells(67, 2) = Me.txtContactName.Value
            wksContactStagingArea.Cells(68, 2) = Me.txtOfficePhone.Value
            wksContactStagingArea.Cells(69, 2) = Me.txtCellPhone.Value
            wksContactStagingArea.Cells(70, 2) = Me.txtFaxNumber.Value
            wksContactStagingArea.Cells(71, 2) = Me.txtEmailAddress.Value
            
            If IsDate(wksContactStagingArea.Cells(72, 2)) Then
                wksContactStagingArea.Cells(73, 2) = Date
            Else
                wksContactStagingArea.Cells(72, 2) = Date
                wksContactStagingArea.Cells(73, 2) = Date
            End If
            
            If IsDate(wksContactStagingArea.Cells(66, 3)) Then
                Range(wksContactStagingArea.Cells(66, 4), wksContactStagingArea.Cells(71, 4)) = Date
            Else
                Range(wksContactStagingArea.Cells(66, 3), wksContactStagingArea.Cells(71, 3)) = Date
            End If
    
            Call UpdateMasterCreatedRevisedDates(Date)
                
        Case 3
            wksContactStagingArea.Cells(76, 2) = Me.txtContactTitle.Value
            wksContactStagingArea.Cells(77, 2) = Me.txtContactName.Value
            wksContactStagingArea.Cells(78, 2) = Me.txtOfficePhone.Value
            wksContactStagingArea.Cells(79, 2) = Me.txtCellPhone.Value
            wksContactStagingArea.Cells(80, 2) = Me.txtFaxNumber.Value
            wksContactStagingArea.Cells(81, 2) = Me.txtEmailAddress.Value
            
            If IsDate(wksContactStagingArea.Cells(82, 2)) Then
                wksContactStagingArea.Cells(83, 2) = Date
            Else
                wksContactStagingArea.Cells(82, 2) = Date
                wksContactStagingArea.Cells(83, 2) = Date
            End If
            
            If IsDate(wksContactStagingArea.Cells(76, 3)) Then
                Range(wksContactStagingArea.Cells(76, 4), wksContactStagingArea.Cells(81, 4)) = Date
            Else
                Range(wksContactStagingArea.Cells(76, 3), wksContactStagingArea.Cells(81, 3)) = Date
            End If
    
            Call UpdateMasterCreatedRevisedDates(Date)
    End Select
    
    End Sub

    Notice that the target cells (on the left) are fully qualified.  This application used multiple worksheets and it's a best practice to always qualify which worksheet is the recipient of the data.  The right side uses the names of the form objects which is the forms's source of the data.

    I hope this helps.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Friday, June 19, 2015 4:58 AM
  • Hi,

    >>I want to send data to multiple rows from user form to worksheet. Is it possible ?

    Yes it is, as RichLocus said that you just need to specify the value to the range with the textbox/combox value. The UpdateMasterCreatedRevisedDates is the custom function, you don’t need to use it.

    For Range and Cell properties, please refer to:

    # Worksheet.Range Property (Excel)

    https://msdn.microsoft.com/EN-US/library/office/ff836512.aspx

    # Worksheet.Cells Property (Excel)

    https://msdn.microsoft.com/EN-US/library/office/ff194567.aspx

    We also could setting a range of cells via an array.

    # Fun with Excel--setting a range of cells via an array

    http://blogs.msdn.com/b/eric_carter/archive/2004/05/04/126190.aspx

    >> Can I make an option that data from particular groups of source rows to be transferred ?

    The new issue is not directly related to the original issue, it would be better if you open up a new thread for the new question, in this way, our discussion here will not deviate too much from the original issue, this will make answer searching in the forum easier and be beneficial to other community members as well.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 23, 2015 3:15 AM
    Moderator

All replies

  • Hello:

    The answer to your question is yes.  It is a common developer's procedure to get input from forms and place them in various cells.

    First, as you probably know, every combo-box and text box has a name.  One common method is to also include a button on the form so that when all the data is collected, the button is pushed.  That button would be assigned a "Click" event.  In that event, you would move the value from the named combo-box or text box to the cell(s) of your choice.  As sample statement might be something like this:

    Private Sub SaveContactInformation()
    
    Select Case intGblLastContactNumberUpdated
        Case 1
            wksContactStagingArea.Cells(56, 2) = Me.txtContactTitle.Value
            wksContactStagingArea.Cells(57, 2) = Me.txtContactName.Value
            wksContactStagingArea.Cells(58, 2) = Me.txtOfficePhone.Value
            wksContactStagingArea.Cells(59, 2) = Me.txtCellPhone.Value
            wksContactStagingArea.Cells(60, 2) = Me.txtFaxNumber.Value
            wksContactStagingArea.Cells(61, 2) = Me.txtEmailAddress.Value
            
            If IsDate(wksContactStagingArea.Cells(62, 2)) Then
                wksContactStagingArea.Cells(63, 2) = Date
            Else
                wksContactStagingArea.Cells(62, 2) = Date
                wksContactStagingArea.Cells(63, 2) = Date
            End If
            
            If IsDate(wksContactStagingArea.Cells(56, 3)) Then
                Range(wksContactStagingArea.Cells(56, 4), wksContactStagingArea.Cells(61, 4)) = Date
            Else
                Range(wksContactStagingArea.Cells(56, 3), wksContactStagingArea.Cells(61, 3)) = Date
            End If
            
            Call UpdateMasterCreatedRevisedDates(Date)
            
        Case 2
            wksContactStagingArea.Cells(66, 2) = Me.txtContactTitle.Value
            wksContactStagingArea.Cells(67, 2) = Me.txtContactName.Value
            wksContactStagingArea.Cells(68, 2) = Me.txtOfficePhone.Value
            wksContactStagingArea.Cells(69, 2) = Me.txtCellPhone.Value
            wksContactStagingArea.Cells(70, 2) = Me.txtFaxNumber.Value
            wksContactStagingArea.Cells(71, 2) = Me.txtEmailAddress.Value
            
            If IsDate(wksContactStagingArea.Cells(72, 2)) Then
                wksContactStagingArea.Cells(73, 2) = Date
            Else
                wksContactStagingArea.Cells(72, 2) = Date
                wksContactStagingArea.Cells(73, 2) = Date
            End If
            
            If IsDate(wksContactStagingArea.Cells(66, 3)) Then
                Range(wksContactStagingArea.Cells(66, 4), wksContactStagingArea.Cells(71, 4)) = Date
            Else
                Range(wksContactStagingArea.Cells(66, 3), wksContactStagingArea.Cells(71, 3)) = Date
            End If
    
            Call UpdateMasterCreatedRevisedDates(Date)
                
        Case 3
            wksContactStagingArea.Cells(76, 2) = Me.txtContactTitle.Value
            wksContactStagingArea.Cells(77, 2) = Me.txtContactName.Value
            wksContactStagingArea.Cells(78, 2) = Me.txtOfficePhone.Value
            wksContactStagingArea.Cells(79, 2) = Me.txtCellPhone.Value
            wksContactStagingArea.Cells(80, 2) = Me.txtFaxNumber.Value
            wksContactStagingArea.Cells(81, 2) = Me.txtEmailAddress.Value
            
            If IsDate(wksContactStagingArea.Cells(82, 2)) Then
                wksContactStagingArea.Cells(83, 2) = Date
            Else
                wksContactStagingArea.Cells(82, 2) = Date
                wksContactStagingArea.Cells(83, 2) = Date
            End If
            
            If IsDate(wksContactStagingArea.Cells(76, 3)) Then
                Range(wksContactStagingArea.Cells(76, 4), wksContactStagingArea.Cells(81, 4)) = Date
            Else
                Range(wksContactStagingArea.Cells(76, 3), wksContactStagingArea.Cells(81, 3)) = Date
            End If
    
            Call UpdateMasterCreatedRevisedDates(Date)
    End Select
    
    End Sub

    Notice that the target cells (on the left) are fully qualified.  This application used multiple worksheets and it's a best practice to always qualify which worksheet is the recipient of the data.  The right side uses the names of the form objects which is the forms's source of the data.

    I hope this helps.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Friday, June 19, 2015 4:58 AM
  • So  I guess it'a  a problem of writing textbox value to excel problem, you can try Interop. Import "Microsoft.Office.Core" and "Microsoft.Office.Interop.Excel" COM references. Then try following code:

    public void WriteToExcel()
    {
       string myPath = @"C:\Excel.xls"; // this must be full path.
       FileInfo fi = new FileInfo(myPath);
       if (!fi.Exists)
       {
           Console.Out.WriteLine("file doesn't exists!");
       }
       else
       {
            var excelApp = new Microsoft.Office.Interop.Excel.Application();
            var workbook = excelApp.Workbooks.Open(myPath);
            Worksheet worksheet = workbook.ActiveSheet as Worksheet; 
     
    
            Microsoft.Office.Interop.Excel.Range range = worksheet.Cells[1,1] as Range;
            range.Value2 = "5";
     
            //excelApp.Visible = true;
            workbook.Save();
            workbook.Close();
       }
    }

    refer post here.

    Friday, June 19, 2015 6:49 AM
  • Hi RichLocus Logicwurks,
    Thank you for your reply.
    I could not make it work. There is an error.
    please see it,

    Friday, June 19, 2015 1:45 PM
  • Beside this with user form. Can we use Excel work sheet format as we do with user form ? or not.

    regards

    Friday, June 19, 2015 1:47 PM
  • Hi,

    This code

    Sub Copy_Over()
     Dim v As Variant, dst As Worksheet
     Dim s As String, LastRow As Long
     Dim Sh As Worksheet, lr As Long
     Set dst = Sheets("DATA") 
     s = "ISSUE"
     v = Split(s, ",")
     For Each Sh In ThisWorkbook.Worksheets
         If Not IsError(Application.Match(CStr(Sh.Name), v, 0)) Then
         LastRow = dst.Cells(Cells.Rows.Count, "A").End(xlUp).Row
         lr = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row
         Sh.Rows("7:" & lr).Copy dst.Cells(LastRow + 1, 1)

         End If
     Next Sh
     End Sub

    works fine. I need make some modifications. I want it to paste value and formatting only. No formula or others present on source sheet. Can I make an option that data from particular groups of source rows to be transferred ?

    regards


    Saturday, June 20, 2015 2:29 PM
  • Hi,

    >>I want to send data to multiple rows from user form to worksheet. Is it possible ?

    Yes it is, as RichLocus said that you just need to specify the value to the range with the textbox/combox value. The UpdateMasterCreatedRevisedDates is the custom function, you don’t need to use it.

    For Range and Cell properties, please refer to:

    # Worksheet.Range Property (Excel)

    https://msdn.microsoft.com/EN-US/library/office/ff836512.aspx

    # Worksheet.Cells Property (Excel)

    https://msdn.microsoft.com/EN-US/library/office/ff194567.aspx

    We also could setting a range of cells via an array.

    # Fun with Excel--setting a range of cells via an array

    http://blogs.msdn.com/b/eric_carter/archive/2004/05/04/126190.aspx

    >> Can I make an option that data from particular groups of source rows to be transferred ?

    The new issue is not directly related to the original issue, it would be better if you open up a new thread for the new question, in this way, our discussion here will not deviate too much from the original issue, this will make answer searching in the forum easier and be beneficial to other community members as well.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 23, 2015 3:15 AM
    Moderator