VBA form - search and export row

cunnie · #1 (**permalink**) 01-11-10, 23:01

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,

Colin Legg · #2 (**permalink**) 01-12-10, 04:43

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...

cunnie · #3 (**permalink**) 01-12-10, 23:23

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

Colin Legg · #4 (**permalink**) 01-13-10, 05:43

Hi Cunnie,

Quote:

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.

Quote:

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?

cunnie · #5 (**permalink**) 01-13-10, 17:50

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.

Colin Legg · #6 (**permalink**) 01-14-10, 09:11

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?

cunnie · #7 (**permalink**) 01-18-10, 00:55

Hi Colin, that worked excellently!! Exactly what i wanted. Thanks for all of your help.

Internet Services

Web Development

Digital Marketing

Consumer Tech