VBA - Export cells/columns to text file based on values in OTHER cells/columns

[jdt507]

Hi All,

I'm a novice user of Excel and have quite a lot of data to sift through and export for use in a statistical programme. I know essentially no VBA so could really use some help, otherwise this will be a very long process to do manually.

In short, I have a number of Excel files that contain inflation data for different years. In each row there is a date (Column A) and three columns of relevant data (Columns F,G,H). I have attached a link to one of these files as an example.

http://www.ons.gov.uk/ons/guide-met...s-and-price-quotes/1996/1996-item-indices.zip

What I would like to do is:

1. Extract data from, say, Column F, if it's corresponding date in Column A is within a set date range.
2. Save this data to a text file with commas after each value.
3. If possible, though much less important, name the text file as "[Name of Excel File]_[Column F Heading]_[Latest Date given in range from Column A].txt".

In case this is unclear, here is an example of what I am looking for:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date (Col A)[/TD]
[TD]Data1 (Col F)[/TD]
[TD]Data2 (Col G)[/TD]
[TD]Data3 (Col H)[/TD]
[/TR]
[TR]
[TD]1996_01[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1996_01[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1996_01[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1996_02[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1996_03[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1996_05[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1996_05[/TD]
[TD]9[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1996_06[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1996_07[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1996_08[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1996_08[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


I'd like to be able to extract (as an example) all the data from column F that falls between 1996_01 and 1996_04, then save this to a text file with commas separating each value, with the text file to be called "[Name Of Excel File]_Data1_1996_04.txt".

Could anyone give me any ideas about where to start or where to look in order to be able to do this? Or are there any templates I could search for that I could modify relatively easily to do this?

Thank guys!

 

[jdt507]

In case anyone else was wondering how to do similar, I thought I'd upload the code I eventually used.

Due to my lack of knowledge of VBA I took samples of codes from various sources and eventually (I'm sure in a horribly inefficient way) managed to edit them into something that fitted my requirements. This is definitely NOT the best way to go about it, but I thought it might give someone a basis if they were ever as stuck as I was.

The following code was used to copy all the data in column F for which the date in column A was within certain ranges, remove any values of zero, then export it to a text file and automatically name the text file based on the dates used in column A and the title of the data in column F. This procedure was then repeated, replacing column F with column G, which had different data.

Hopefully this will be helpful or a starting point for someone.

Sub Data_Extract()

    Dim VAL As Long, VALS As Long, VALF As Long
    
    Dim RowCountA As Long
    RowCountA = Range("A100000").End(xlUp).Row
    'http://www.mrexcel.com/forum/excel-questions/584966-visual-basic-applications-sum-variable-length-column.html

    Dim a As Long, b As Long, o As Variant, cell As Range
    ReDim o(1 To 10000, 1 To 1)
    ' hiker95, 10/24/2013
    ' http://www.mrexcel.com/forum/excel-questions/734929-create-range-visual-basic-applications-where-cells-not-equal-zero.html
    
    Dim RowCountN As Long
    Dim c As Long, d
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    'http://www.ozgrid.com/forum/showthread.php?t=146100
    
    With ActiveSheet
        
        Set rng = Range("A1:A10000")

        If Not rng Is Nothing Then
        
        
        'Procedure to extract RPI data
        
        
        VALS = .Cells(2, "A").Value
        VALF = VALS + 11
            For VAL = VALS To VALF
                .Cells(1, "M").Value = VAL
            
                For a = 2 To RowCountA
                    If .Cells(a, "A").Value = VAL Then
                        .Cells(a, "M").Value = .Cells(a, "F").Value
                    End If
                Next a
                a = 0
                For Each cell In Range("M2:M10000")
                    If cell <> 0 Then
                        b = b + 1
                        o(b, 1) = cell
                    End If
                Next cell
                Range("N2").Resize(UBound(o, 1), UBound(o, 2)) = o
                RowCountN = Range("N100000").End(xlUp).Row
                b = 0
            
                Set d = fs.CreateTextFile("F:\Project\......\RPI" & VAL & ".txt", True)
                For c = 2 To RowCountN
                    d.writeline Cells(c, "N")
                Next c
                
                Range("M:N").Delete
            Next VAL
            
            
            'Repeat Procedure Again For CPI data Extraction
            
            
            VALS = .Cells(2, "A").Value
        VALF = VALS + 11
            For VAL = VALS To VALF
                .Cells(1, "M").Value = VAL
            
                For a = 2 To RowCountA
                    If .Cells(a, "A").Value = VAL Then
                        .Cells(a, "M").Value = .Cells(a, "G").Value
                    End If
                Next a
                a = 0
                For Each cell In Range("M2:M10000")
                    If cell <> 0 Then
                        b = b + 1
                        o(b, 1) = cell
                    End If
                Next cell
                Range("N2").Resize(UBound(o, 1), UBound(o, 2)) = o
                RowCountN = Range("N100000").End(xlUp).Row
                b = 0
            
                Set d = fs.CreateTextFile("F:\Project\......\CPI" & VAL & ".txt", True)
                For c = 2 To RowCountN
                    d.writeline Cells(c, "N")
                Next c
                
                Range("M:N").Delete
            Next VAL
        End If
    End With

End Sub