Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    8

    Unanswered: VBA form - search and export row

    Hi all, I am hoping you can help.

    I currently have a user form where the user enters search criteria and the result is displayed in a list box on the same form.

    When the user double clicks on the result in the list box it take them straight to where the info is on the spreadsheet.

    I would like to introduce a command button (to the search userform) that selects the entire row (on which the search result lies) and exports that data to a text file. Client data is recorded on the spreadsheet by row with many columns of information.

    Just to make it harder, the data needs to be exported onto separate lines so that each column of info gets its own line.

    Any help would be appreciated.

    Thanks,

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    It sounds like you already know how to find the row of data in the worksheet, so I'll concentrate on how to export a row of data into a text file. Let's suppose you want to put row 1 into a textfile; here is a basic file I/O example:
    Code:
    Sub foo()
        Dim vValues As Variant
        Dim ff As Long, c As Long
        
        'put the row 1 of data into an 2D array
        vValues = Range("A1").EntireRow.Value
        
        'get the next free file number
        ff = VBA.FreeFile()
        
        'open/create a text file which we want to send data to
        Open "Y:\Excel\MyNewTextFile.text" For Output As ff
        
        'put the values into the text file
        For c = LBound(vValues, 2) To UBound(vValues, 2)
           Print #ff, vValues(1, c)
        Next c
        
        'close the text file
        Close ff
        
    End Sub
    There's a really nice File I/O tutorial over at one of this forum's iNet sister sites, XVBT.

    I hope that gives you some ideas...

  3. #3
    Join Date
    Nov 2009
    Posts
    8
    Thanks Colin. That worked nicely. Sorry I have some more questions.

    That code specifies A1 as the export row reference. How do i make this user defined? In other words, the user clicks on their search result in the search user form (which highlights the result on the spreadsheet) then they click an export button to run your code and export that row depending on which cell is highlighted.

    Also, Is there a way of grouping exported data? For example in the text file:
    <date><clinic><user> next line
    <surname><firstname><title>next line
    <sex>...etc... next line

    I have been looking for a solution for this all day. Any help would be greatly appreciated.
    Thanks

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Cunnie,
    That code specifies A1 as the export row reference. How do i make this user defined? In other words, the user clicks on their search result in the search user form (which highlights the result on the spreadsheet) then they click an export button to run your code and export that row depending on which cell is highlighted.
    Yes, it does. I have no idea (because I cannot see your userform and workbook) how to find the relevant row of data, so I chose A1. To make it "user defined", use the same logic to find the row as you are using for your listbox and go from there. I really can't be more specific - unless "highlighted" means "selected", in which case you can use Selection.EntireRow or something similar.

    Also, Is there a way of grouping exported data? For example in the text file:
    Yes, you can print onto the same line. The ease of this will depend on the way the data is structured in the worksheet versus the way you want it grouped in the text file. For example, if <date><clinic><user> are all grouped next to each other in the worksheet then it will be easier to put them on the same line in the text file than if those columns are scattered.


    Perhaps if you attach a workbook with some dummy data and explain how the output should be formatted, I may be able to put together a quick demo for you?

  5. #5
    Join Date
    Nov 2009
    Posts
    8
    Thanks very much for your help Colin. I will have a go with the suggestions you made. If I hit any problems i will upload the workbook. cheers

    OK - i have added the spreadsheet with much of the data removed except for the search box. Once you type in a keyword and hit search, the result is highlighted on the spreadsheet. If the user wants to export that row of info they click on the 'Export row' command button. My issue is what script to add to the export button so that it selects the row that the result is on.

    The other issue is how to format the exported txt file so that some info is grouped together (like client demographics - together, treatment details - together).

    Thanks again Colin.
    Attached Files Attached Files
    Last edited by cunnie; 01-14-10 at 02:40. Reason: added file

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Okay, here's a 'quick n dirty' way to give you some ideas.

    So first I added some named ranges (Insert|Name|Define) as follows:
    Code:
    Name		Defined As
    ClinicDetails 	=ISOHdata!$A:$C
    Demographics 	=ISOHdata!$D:$P
    Eligibility 	=ISOHdata!$Q:$T
    Other 		=ISOHdata!$AY:$BC
    Treatments 	=ISOHdata!$U:$AX
    Then, I amended the Export Row button's click event handler as follows:
    Code:
    Private Sub CommandButton2_Click()
        Dim rngToExport As Range
        
        With ListBox1
        
            If .ListIndex > -1 Then
                
                Set rngToExport = Worksheets(.List(.ListIndex, 1)).Range(.List(.ListIndex, 2))
                
                Module6.ExportRow rngToExport
            
            Else
                MsgBox "Please select an item to export"
            End If
            
        End With
    End Sub
    I haven't checked how the listbox is populated, so you may need to add defensive coding in here if it may not return a valid worksheet/range address.

    Then in module 6 I amended the foo sub to this:
    Code:
    Sub ExportRow(ByVal rngToExport As Range)
        Dim vClinicDetails, vDemographics, vEligiblity, vTreatments, vOther
        Dim ff As Long, c As Long
        
        'put the relevant row data into 1-D arrays (tranpose 2x) so we can use Join()
        With Application
            vClinicDetails = .Transpose(.Transpose(.Intersect(rngToExport.EntireRow, Range("ClinicDetails")).Value))
            vDemographics = .Transpose(.Transpose(.Intersect(rngToExport.EntireRow, Range("Demographics")).Value))
            vEligiblity = .Transpose(.Transpose(.Intersect(rngToExport.EntireRow, Range("Eligibility")).Value))
            vTreatments = .Transpose(.Transpose(.Intersect(rngToExport.EntireRow, Range("Treatments")).Value))
            vOther = .Transpose(.Transpose(.Intersect(rngToExport.EntireRow, Range("Other")).Value))
        End With
    
        'get the next free file number
        ff = VBA.FreeFile()
        
        'open/create a text file which we want to send data to
        Open "Thepathnameyouwantinhere\MyNewTextFile.text" For Output As ff
    
        Print #ff, Join(vClinicDetails)
        Print #ff, Join(vDemographics)
        Print #ff, Join(vEligiblity)
        Print #ff, Join(vTreatments)
        Print #ff, Join(vOther)
    
        'close the text file
        Close ff
        
    End Sub
    Does that help?
    Last edited by Colin Legg; 01-14-10 at 11:35.

  7. #7
    Join Date
    Nov 2009
    Posts
    8
    Hi Colin, that worked excellently!! Exactly what i wanted. Thanks for all of your help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •