Macro to prep a CSV file - data selection, manipulation, and saving with dynamic name

[Spiritseeker]

Hi all,

I am a VBA novice; I spent many hours today watching Wise Owl tutorials (which I highly recommend), but need a working macro soon, and must concede that I think this is a bit beyond my beginner status. I've tried to lay out my requirements in a way that I think explains what I need, based on the tutorials I watched, and would be very grateful for help in coming up with this macro.

Up front info about what I am trying to do:

I need a macro to prep a file for csv upload which will contain rows with an identifier ("H" for header or "L" for line) in column A.
H rows will contain data from column A to AA and L rows will contain data from column A to I only

Every cell from column A to AE contains a formula, down to row 40. The user can add rows by copying down the formulas, and if they do this will likely end up doing so for more rows than actually needed. The user may input data on 2 rows, or 2000 rows etc - it's as needed.

I would like to include a Macro button on row 1 (users are basic excel users – no Macro knowledge, hence the button to click to run the macro), which is a row that will be deleted as part of the macro – will that cause a problem?

The data to end up in the final CSV file to be uploaded will be a dataset which overall has data in columns A to AA, for all rows with an H or an L in column A. Because the L rows only contain data from column A to I, but the overall dataset goes to column AA, my test files show that the csv file has commas on the rows with L in column A representing blank cells from columns J to AA. These need to be deleted prior to saving the CSV, as the upload process will not accept them and expects the L rows to finish at column I.

The macro enabled sheet will be password protected to be read only, to ensure that it isn’t accidentally saved over by the user.

What I need the Macro to actually do:

1 Delete all tabs apart from sheet1 (will be renamed as AccrualOffer in VBA properties – would like to remove the warning message so the user does not have to select that they are sure they want to delete the tabs)
2 Highlight selection from A4 to last row containing an H or an L , across to column AA (I think this may be: Range("A4", Selection.End(xlDown)).Select Range(Selection, Selection.Offset(0, 26)).Select)
3 Clean any trailing spaces in the cells in the selection
3 Copy selection
4 Hard paste into same place (most data will be formula driven prior to this step)
5 Drop down 1 row and delete next 300 rows (idea is to delete 300 rows of data below the selected area in case the user has put in any data anywhere such as a quick calculation formula, or copied down the formulas on rows that didn’t get used – ie rows without an L or H in column A)
6 Delete rows 1 to 3 (these contained instructions, and the Macro button itself unless this will cause a problem)
7 Delete columns AB:AP (these were columns containing info for formulas in the other columns, and cannot form part of the final file)
8 Delete cells in columns J to AA on rows where column A contains an L (see note in info above to see why – it’s because the L (Line) rows only contain data to column I, and without this step, the csv file would save them as a cell with no data and try to upload it, and it would fail)
9 Open Save as window, with CSV file type with name as follows based on Excel syntax to try to demonstrate what I mean: “ozfaoffer_”&”OffernameFromCellB1”&”V1” so the middle section of the file name will be taken from cell B1 after all the data has been manipulated in the prior steps – but don’t save at this point if possible, I’d like the user to be able to edit the file name at this point in case they have to update it to end with v2, or v3 etc instead of v1 (failed upload attempts due to incorrect data in the csv load ‘utilise’ a file name, so a subsequent file name cannot be the same).

If anyone needs further info, please let me know.

Many thanks in advance for the help

 

[Spiritseeker]

I need to provide a little more info around the requirements for step 9 of the macro, please see below for an updated step 9:

9 Open Save as window, in a defined folder (use desktop for the macro and I can update), with CSV as the file type and with the file name as follows based on Excel syntax to try to demonstrate what I mean: “ozfaoffer_”&”OffernameFromCellB1”&”V1”m so the middle section of the file name will be taken from cell B1 after all the data has been manipulated in the prior steps – but don’t save at this point if possible, I’d like the user to be able to edit the file name at this point in case they have to update it to end with v2, or v3 etc instead of v1 (failed upload attempts due to incorrect data in the csv load ‘utilise’ a file name, so a subsequent file name cannot be the same).

 

[JMeendering]

I took a different approach, instead of deleting cells and a bunch of manipulation to the sheet, it creates a new csv file.
the name it creates is based off a data time stamp so should be pretty unique instead of v1, v2 or v3, etc.

Public Sub Export_To_CSV()
Dim lLastRow As Long
Dim r As Long
Dim i As Long
Dim strRowValue As String
Dim iFile As Integer
Dim cfile As String


With ThisWorkbook.Sheets("Sheet1")
    lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    cfile = ThisWorkbook.Path & "\" & "ozfaoffer_" & .Range("B1").Value & "_" & Format(Now(), "mmddyyyy_hhnnss") & ".csv"


    If Not Dir(cfile) = "" Then Kill (cfile)


    iFile = FreeFile()
    Open cfile For Output Access Write Lock Read Write As #iFile


    For r = 4 To lLastRow
        Select Case Cells(r, 1).Value
            Case "H" 'Cells A-AA
                 For i = 1 To 27
                    strRowValue = strRowValue & Trim(.Cells(r, i).Value) & ","
                 Next i
            Case "L" 'cells A-I
                For i = 1 To 9
                    strRowValue = strRowValue & Trim(.Cells(r, i).Value) & ","
                Next i
                           
        End Select
        strRowValue = Left(strRowValue, Len(strRowValue) - 1)
        Print #iFile, strRowValue
    Next r




Close #iFile


End With


MsgBox "Export to csv successful!"


End Sub

 

[Spiritseeker]

Brilliant, thank you! I will give this a go.
I was obviously literally listing out the steps as I would manually prepare the data to save as a csv file - your approach looks far simpler, I can't believe how concise it looks compared to what I was expecting/working towards.

Great idea on the file name, thank you! Would it be possible to add that file name in to the message box so that the user knows what file it is they need to select as part of the upload process? There will be a large number of these files in the folder and I want this to be as easy/obvious for them as possible!

 

[Spiritseeker]

I just thought of something - on my steps, I incorrectly said that the Cleaning of trailing spaces (this was step 3 - the 1st step 3, I didn't' spot that I put two step 3s till now! - in my numbered step approach) should be done before copying and hard pasting the data.
As a result, the trim will not actually work on every cell.

I don't follow the code well enough to know if you've built it to trim first and then copy/paste value, or paste 1st and then trim?
If the former, could you please adjust it so that the trim step is after the paste value step?

many thanks

 

[Spiritseeker]

I am having a bit of difficulty with specifying the file path in the code. I need the file to save to here:

\\ukdsdfsdf\Sales\SOA and PP documentation\Oracle uploads

How do I insert this into the code? Every version I have tried, has not worked.
And re my question about when the trim happens, I can't get past the line of code: If Not Dir(cfile) = "" Then Kill (cfile) to see if the trim is happening before or after the paste value step (it needs to be after).
While helping me with the file path, could you let me know if it would be possible to put the File Name in the message pop up at the end too?

again, thanks a ton for your help

 

[JMeendering]

there is no need copy and paste, it is using the direct value that is present in the cell.

Public Sub Export_To_CSV()
Dim lLastRow As Long
Dim r As Integer
Dim c As Integer
Dim expDate As String
Dim effDate As String
Dim strRowValue As String
Dim bDirExists As Boolean
Dim oFSO As Object


Dim iFile As Integer
Dim cfile As String
Dim cPath As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
With ThisWorkbook.Sheets("Sheet1")
    lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' get the last row in column a
    cPath = "\\ukdsdfsdf\Sales\SOA and PP documentation\Oracle uploads" ' file path
    bDirExists = oFSO.folderExists(cPath) ' check to make sure the path exists
    If bDirExists = False Then cPath = ThisWorkbook.Path ' if the path does not exist then use the workbook's path as the default
    
    cfile = cPath & "\" & "ozfaoffer_" & .Range("B1").Value & "_" & Format(Now(), "mmddyyyy_hhnnss") & ".csv"
    
    iFile = FreeFile()
    Open cfile For Output Access Write Lock Read Write As #iFile ' open the text file in the path and name specified


    For r = 4 To lLastRow ' loop through the rows
        strRowValue = ""
        Select Case Cells(r, 1).Value ' check the row and cell for a H or L
            Case "H" 'if the cell is an H then loop through that row Cells A through AA
                 For i = 1 To 27
                    strRowValue = strRowValue & Trim(.Cells(r, i).Value) & "," ' use the cell's value and remove any trailing spaces
                 Next i
            Case "L" 'if the cell is an L then loop through that row Cells A through I
                For i = 1 To 9
                    strRowValue = strRowValue & Trim(.Cells(r, i).Value) & "," ' use the cell's value and remove any trailing spaces
                Next i


                           
        End Select
        strRowValue = Trim(Left(strRowValue, Len(strRowValue) - 1)) ' remove the trailing ","
        Print #iFile, strRowValue ' add the comma delimited data to the file
    Next r  ' loop to the next row




Close #iFile   ' close the file


End With
InputBox "File Created:", "Export to CSV successful!", cfile ' using a input box so user can copy the file path and name.
'MsgBox "Export to csv successful!" & Chr(10) & "File created:" & cfile


End Sub

 

[Spiritseeker]

Thanks for responding, and the additional comments to explain it all.

I'm still not being successful - it won't get past this line, saying that the file is not found:

Open cfile For Output Access Write Lock Read Write As #iFile ' open the text file in the path and name specified

The path does exist, I created it for this purpose. It's on a server as opposed to my computer so I wondered if that might be causing a problem, but I tried updating the folder location to my desktop (again, after creating a folder called Oracle Uploads), and this gave the same error.

 

[Spiritseeker]

I figured it may be easier if I shared my file - it can be found here:

https://www.dropbox.com/s/415isci66y9ha59/test accrual offer.xlsm?dl=0

 

[JMeendering]

sorry, I can't access any file sharing services, I am at work.
not sure if the server path that would cause the issue.

Open cfile for Output as #iFile [COLOR=#333333]' open the text file in the path and name specified[/COLOR]