Send entire row to text file on condition of cell value being Y

This code was used on a project recently to allow data from a worksheet to be output to a csv file with the name Output_ and todays date without the ‘\’s. The code is a good example of taking the data if the value in column AS is ‘Y’ or ‘y’. It also adds the current date to the same row in column AQ to show the most recent update.

This code example can actually be shortened to use a loop to run through and grab the values from the row and put them in the text file. I will add this solution or provide on request, but for now this code is how the client ordered/requested it so that changes could be made if necessary.

Feel free to use or adapt the code for your own use or contact us for further assistance.


Sub CurrentWebsite_Button2_Click()

Dim FileUser As String
Dim FileuserDocFILE As String
Dim ws As Worksheet
Set ws = Worksheets("Current Website")

Application.ScreenUpdating = False

ldate = Format(Now(), "dd/mm/yyyy")

With Worksheets("Current Website")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For n = 6 To lastrow
If ActiveSheet.Range("AS" & n) = "Y" Then
ActiveSheet.Range("AQ" & n) = ldate

'Add the code to send A-AS to a csv file named Output_ldate in folder
'C:\Users\Antony\Google Drive\Website\Product Information\Products\Product Uploads 2016

FileUser = ActiveWorkbook.Name
'On Error Resume Next
FileuserDocFILE = "C:\Users\Beano\Google Drive\Website\Product Information\Products\Product Uploads 2016\Output_" & ldate & ".csv" 'name the .txt database to hold the info
'On Error GoTo 0
'This section lets you choose the fields to go into the .txt file
'The Chr(9) allows a Tab to be included between the fields
Open FileuserDocFILE For Append As #1 'Use Output instead of APPEND to overwrite data
Print #1, ws.Range("A" & n) & "," & ws.Range("B" & n) & "," & ws.Range("C" & n) & "," & ws.Range("D" & n) & "," & ws.Range("E" & n) & "," & ws.Range("F" & n) & "," & ws.Range("G" & n) & "," & _
ws.Range("H" & n) & "," & ws.Range("I" & n) & "," & ws.Range("J" & n) & "," & ws.Range("K" & n) & "," & ws.Range("L" & n) & "," & ws.Range("M" & n) & "," & ws.Range("N" & n) & "," & ws.Range("O" & n) & "," & ws.Range("P" & n) & "," & _
ws.Range("Q" & n) & "," & ws.Range("R" & n) & "," & ws.Range("S" & n) & "," & ws.Range("T" & n) & "," & ws.Range("U" & n) & "," & ws.Range("V" & n) & "," & _
ws.Range("W" & n) & "," & ws.Range("X" & n) & "," & ws.Range("Y" & n) & "," & ws.Range("Z" & n) & "," & ws.Range("AA" & n) & "," & ws.Range("AB" & n) & "," & ws.Range("AC" & n) & "," & ws.Range("AD" & n) & "," & ws.Range("AE" & n) & "," & ws.Range("AF" & n) & "," & ws.Range("AG" & n) & "," & _
ws.Range("AH" & n) & "," & ws.Range("AI" & n) & "," & ws.Range("AJ" & n) & "," & ws.Range("AK" & n) & "," & ws.Range("AL" & n) & "," & ws.Range("AM" & n) & "," & ws.Range("AN" & n) & "," & ws.Range("AO" & n) & "," & ws.Range("AP" & n) & "," & _
ws.Range("AQ" & n) & "," & ws.Range("AR" & n) & "," & ws.Range("AS" & n)
Close #1
End If

End If

Next n
End Sub






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *