Avatar of Juan Velasquez
Juan VelasquezFlag for United States of America asked on

Need help on a for loop

Hello,
In the following code, I want to append a row to a csv file provided that rng.value <> 0 Or rng.value <> "".  If rng.value = 0 or rng.value = "", I want to go to the next row.  I have two spreadsheet, I am processing.  Each one has a zero in two rows in the relevant cell. And there are 7 rows in each spread sheet for a total of 14 rows for both spreadsheets.  When I run the code I want to see on 10 rows in the resulting csv sheet because two rows in each spreadsheet have a value of zero in the designated cell.  However, I am still getting 14 rows in the final output.  I have stepped through the code and I am executing the goto statement correctly, but it appears that the value from the adjacent cell is being transposed to the cell that previously had a value of zero.  
Public Function RangeToCSV(list As Range) As String
    ' Comments:
    ' Params  : list
    ' Returns : String
    ' Modified:

    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Dim strTmp As String
    Dim lngCurrentRow As Long
    Dim lngCurrentColumn As Long
    Dim rng As Range
    Dim xlsRange As Range

    If TypeName(list) = "Range" Then
        lngCurrentRow = 15

        For Each rng In list.Cells
            If Range(rng.Address).Column > 14 Then
                If Range(rng.Address).Column = 19 Then
                    If rng.value = 0 Or rng.value = "" Then
                       ' Debug.Print "Yes"
                        GoTo SkipProcessing
                    End If
                End If

                If rng.row = lngCurrentRow Then
                    If strTmp = vbNullString Then
                        strTmp = rng.value
                    Else
                        strTmp = strTmp & "," & rng.value
                    End If
                Else
                    lngCurrentRow = lngCurrentRow + 1
                    If strTmp = vbNullString Then
                        strTmp = rng.value
                    Else
                        strTmp = strTmp & Chr(10) & rng.value
                    End If
                End If
 
            End If
SkipProcessing:
        Next
    End If

    RangeToCSV = strTmp

    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Application.EnableEvents = True
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.RangeToCSV"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

End Function

Open in new window

Microsoft ExcelMicrosoft ApplicationsMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

I don't know if it helps, but using GoTo's like that is not considered good programming practice. How about this instead?

      For Each Rng In List.Cells
            If Range(Rng.Address).Column > 14 Then
                If Range(Rng.Address).Column = 19 Then
                    If Rng.Value = 0 Or Rng.Value = "" Then
                       ' Debug.Print "Yes"
                    End If
                ElseIf Rng.Row = lngCurrentRow Then
                    If strTmp = vbNullString Then
                        strTmp = Rng.Value
                    Else
                        strTmp = strTmp & "," & Rng.Value
                    End If
                Else
                    lngCurrentRow = lngCurrentRow + 1
                    If strTmp = vbNullString Then
                        strTmp = Rng.Value
                    Else
                        strTmp = strTmp & Chr(10) & Rng.Value
                    End If
                End If
 
            End If
        Next

Open in new window

ASKER
Juan Velasquez

Hello Martin,

I tried the code you sent and now there are no rows in the output
ASKER
Juan Velasquez

Oh I see what the problem is.  I'll try it again
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Martin Liss

Okay, sorry, without the workbook I couldn't test. The point is still the same and that is that by properly using If's (which I apparently failed to do) you can avoid the GoTo.
ASKER
Juan Velasquez

I've tried it again and it looks like what is happening now is that the values in column F are being transposed to column E as shown in the attached output file
OutputFile.csv.xlsx
Martin Liss

I'm going out and I'll get back to you in a few hours.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Juan Velasquez

Hello,

Something just occurred to me.   Maybe i could test for the value of the critical cell before it start processing the first cell of a row
Martin Liss

Since there's no code in the workbook you attached and there's only one zero anywhere in the ws (Cell X1) which is different than your original problem statement I'm a little confused. The easiest thing to do would be for you to tell me which rows (by number) you don't want in your output, and why you don't want them.
ASKER
Juan Velasquez

Hello Martin

I've attached the workbook.  The code in question is executed via the Approve Time button on the Summary Sheet.  The code is located in the summary sheet.  I've also attached the output with the rows I want deleted, highlighted in Yellow.  Those roles need to be deleted because there is a zero in column E.  Open that file in Excel to view the highlighted columns
Test-Timesheets-Workbook.xlsm
OutputFile.csv
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Harry Lee

Chtullu135,

If you want your For loop to go to next row, use

"Resume Next" instead of "GoTo SkipProcessing".

Try it and let me know how it goes.
ASKER
Juan Velasquez

When I put the Resume Next in the code you posted,  I get a message stating resume without error. and the file produced is blank.  When I put it in my original code, I get the same error message and I get a blank file
Public Function NewRangeToCSV(list As Range) As String

    Dim strTmp As String
    Dim lngCurrentRow As Long
    Dim lngCurrentColumn As Long
    Dim rng As Range
    Dim xlsRange As Range
    
If TypeName(list) = "Range" Then
    lngCurrentRow = 15
          For Each rng In list.Cells
            If Range(rng.Address).Column > 14 Then
                If Range(rng.Address).Column = 19 Then
                    If rng.value = 0 Or rng.value = "" Then
                       Resume Next
                    End If
                ElseIf rng.row = lngCurrentRow Then
                    If strTmp = vbNullString Then
                        strTmp = rng.value
                    Else
                        strTmp = strTmp & "," & rng.value
                    End If
                Else
                    lngCurrentRow = lngCurrentRow + 1
                    If strTmp = vbNullString Then
                        strTmp = rng.value
                    Else
                        strTmp = strTmp & Chr(10) & rng.value
                    End If
                End If
 
            End If
        Next
                                            
    End If
NewRangeToCSV = strTmp
End Functio

Open in new window

Martin Liss

I'm finding that stepping through the code using the rng object is difficult. I'm sure I can produce the CSV you need in a different way so I have the following questions:

1) Will the format of the WTS sheets always be the same? In other words the data will always be for a week and it is shown in two sections the first in A14:N21 and the second in G26:M35?

2) Is it true that if a value in column G is zero or blank that you don't want any data for that day in the CSV

3) Given a row with hours > 0, like row 15 on WTS_Don Johnson, can you tell me which cells should be in the CSV file (and in the proper order)?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Juan Velasquez

Hello Martin
With respect to point 1.  The number of rows may vary in that users may generate mulitple rows for the same day.  For example, he or she may work 8 hour regular time on a Monday and then work an additional two hour overtime.  In which case, he would click the Add a New Line item button and a new row would appear below monday , where the user can make the entries for overtime  As for the actual data that will populate the csv file.  That will come from columns O to AG.  The csv file is to be imported into a system that requires the columns in a certain order, along with several other columns that the user does not interact with.  So rather than exporting the columns as is from A15:N21, and then manipulating the csv file.  I used A15:N21 to populate O15 to AQ21 (which are in the correct order) and then just use that as the range for the csv file, hence the reason I test for the column number in the code and only process column numbers greater than 14

1) Will the format of the WTS sheets always be the same? In other words the data will always be for a week and it is shown in two sections the first in A14:N21 and the second in G26:M35
?

With respect to point 2, That is correct, if the value in column G is zero or blank I don't want that ROW in the csv file

2) Is it true that if a value in column G is zero or blank that you don't want any data for that day in the CSV

The csv file will only contain the data from columns O to AQ
Martin Liss

Since all the data comes from O to AQ can I use column 'S' to determine if the data should be in the CSV?

Also, and this may be a lot to ask, but as I asked in point 3, can you tell me by range address which cells and in which order they go into the CSV including the "heading" which looks like this in your posted CSV file.

12345
234

R
12345

L
8/26/2013
ASKER
Juan Velasquez

Hello Martin,

Yes column "S" is the column to go by.  I'm am not including the headers in the csv.  As I go from O to AQ I write the contents into the csv file. row by row. , particularly trailing columns. Below is the code that I wrote to populate the range that I send to the RangeToCSV function that I am trying to get to work.

Private Sub GenerateOutputFile()
    ' Comments:
    ' Params  :
    ' Created : 08/13/13 14:42 JV
    ' Modified:
    
    On Error GoTo PROC_ERR
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strFilter As String
    Dim rng As Range
    Dim OutputRange As Range
    Dim strJobType As String
    Dim strTmpCSV As String 'string to hold the CSV info
    Dim intF As Integer
    Dim cell As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    
    'strFilter = ahtAddFilterItem(strFilter, "CSV (*.csv)", "*.csv")
    'gstrFilePath = ahtCommonFileOpenSave(DialogTitle:="Save Output File", Filter:=strFilter, OpenFile:=False)
    'Kill gstrFilePath
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    KillProperly (gstrFilePath)
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    
    strJobType = wb.Worksheets("Summary").Range("JobType").value
    'MsgBox gstrFilePath
    
    For Each ws In wb.Worksheets
        ws.Unprotect
        If Left(ws.Name, 3) = "WTS" Then
            
            intF = FreeFile
             
            Open gstrFilePath For Append As #intF
            
            gstrEmployeeId = ws.Range("EmployeeID").value
            Application.EnableEvents = False
            
            'Set EmployeeId Range
            'Set r9 = ws.Range("rngWorkingDates")
            ws.Unprotect
            Set rng = ws.Range("WorkDate")
            For Each cell In rng.Cells
                If strJobType = "Construction" Then
                    cell.Offset(0, 14).value = cell.Offset(0, 8).value  'Batch code
                ElseIf strJobType = "Service" Then
                    cell.Offset(0, 14).value = gstrEmployeeId           'Batch code
                End If
                cell.Offset(0, 15).value = gstrEmployeeId           'Employee Code
                cell.Offset(0, 16).value = cell.Offset(0, 11).value 'Department
                cell.Offset(0, 17).value = cell.Offset(0, 7).value  'PayType
                cell.Offset(0, 18).value = cell.Offset(0, 6).value  'Hours
                cell.Offset(0, 19).value = cell.Offset(0, 8).value  'Job
                cell.Offset(0, 20).value = cell.Offset(0, 9).value  'Phase
                cell.Offset(0, 21).value = cell.Offset(0, 10).value 'Cost Type
                cell.Offset(0, 22).value = cell.Offset(0, 0).value  'Date
                cell.Offset(0, 23).value = cell.Offset(0, 13).value 'Message
                cell.Offset(0, 24).value = "'"                      'Payrate Level
                cell.Offset(0, 25).value = "'"                      'WageCode
                cell.Offset(0, 26).value = "'"                      'UnionCode
                cell.Offset(0, 27).value = "'"                      'WorkersComp
                cell.Offset(0, 28).value = "'"                      'StateTaxCode
                cell.Offset(0, 29).value = "'"                      'CountyTaxCode
                cell.Offset(0, 30).value = "'"                      'LocalTaxCode
                cell.Offset(0, 31).value = "'"                      'EquipmentCode
                cell.Offset(0, 32).value = "'"                      'EquipmentHours
                cell.Offset(0, 33).value = "'"                      'Qty
                cell.Offset(0, 34).value = "'"                      'CompanyCode
                cell.Offset(0, 35).value = "'"                      'PayRate
                cell.Offset(0, 36).value = "'"                      'EquipmentCostCategory
                cell.Offset(0, 37).value = "'"                      'CrewNumber
                cell.Offset(0, 38).value = cell.Offset(0, 12).value 'Cost Center
                cell.Offset(0, 39).value = cell.Offset(0, 2).value  'WorkOrder
                cell.Offset(0, 40).value = "'"                      'SiteEquipment
                cell.Offset(0, 41).value = "'"                      'SiteComponent
                cell.Offset(0, 42).value = "'"                      'Contract
                cell.Offset(0, 43).value = "'"                      'EquipmentWorkOrder
            Next
          
           
            Application.EnableEvents = True
           
            strTmpCSV = RangeToCSV(ws.Range("TimesheetEntry"))
          
            Print #intF, strTmpCSV
            Close #intF
            
            
        End If
       
    Next
   ' Kill gstrSavedWorkbookPath
    gstrSavedWorkbookPath = Environ$("temp") & "\Timesheets.xlsm"
    
    ThisWorkbook.SaveCopyAs FileName:=gstrSavedWorkbookPath
    'ThisWorkbook.SaveAs gstrSavedWorkbookPath, xlOpenXMLWorkbookMacroEnabled
    'If MsgBox("Do you wish to email the output file", vbYesNo, "Email Dialog") = vbYes Then
        frmEmailDialog.Show
    'End If
    
PROC_EXIT:
    Application.EnableEvents = True
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.GenerateOutputFile"
    Resume PROC_EXIT
    
End Sub

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Martin Liss

If you have that code, then what is RangeToCSV all about?
ASKER
Juan Velasquez

That how I create the CSV file.  The purpose of the above code is to provide the CSV file with a range having the columns in the correct order.  In addition, there are columns that are required but are not available in the user entry portion of the timesheet.  However, these columns need to be included in the csv file so that csv file can be imported into another system (it's called spectrum).  Once it hits the RangeToCSV, only those columns that are required to be imported into the spectrum system are loaded into the csv file.  This columns O to AQ
Martin Liss

Yes, sorry, I figured that out.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Juan Velasquez

Not a problem.  I find myself getting confused at times:)   I'm still working on it.  It seems there should be an easier way to do this.  Perhaps by reading in the populated csv file, looping through it and deleting rows where the HoursWorked (Column S in the spreadsheet) = 0
Martin Liss

Does the following work for you? Note the new lines 47 and 82. They stop the 0 or black time entries from being passed to RangeToCSV, and so you can then code RangeToCSV any way you want without worrying about invalid data.

Private Sub GenerateOutputFile()
    ' Comments:
    ' Params  :
    ' Created : 08/13/13 14:42 JV
    ' Modified:
    
    On Error GoTo PROC_ERR
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strFilter As String
    Dim rng As Range
    Dim OutputRange As Range
    Dim strJobType As String
    Dim strTmpCSV As String 'string to hold the CSV info
    Dim intF As Integer
    Dim cell As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    
    'strFilter = ahtAddFilterItem(strFilter, "CSV (*.csv)", "*.csv")
    'gstrFilePath = ahtCommonFileOpenSave(DialogTitle:="Save Output File", Filter:=strFilter, OpenFile:=False)
    'Kill gstrFilePath
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    KillProperly (gstrFilePath)
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    
    strJobType = wb.Worksheets("Summary").Range("JobType").value
    'MsgBox gstrFilePath
    
    For Each ws In wb.Worksheets
        ws.Unprotect
        If Left(ws.Name, 3) = "WTS" Then
            
            intF = FreeFile
             
            Open gstrFilePath For Append As #intF
            
            gstrEmployeeId = ws.Range("EmployeeID").value
            Application.EnableEvents = False
            
            'Set EmployeeId Range
            'Set r9 = ws.Range("rngWorkingDates")
            ws.Unprotect
            Set rng = ws.Range("WorkDate")
            For Each cell In rng.Cells
                If cell.Offset(0, 6).value <> "0" And cell.Offset(0, 6).value <> "" Then
                    If strJobType = "Construction" Then
                        cell.Offset(0, 14).value = cell.Offset(0, 8).value  'Batch code
                    ElseIf strJobType = "Service" Then
                        cell.Offset(0, 14).value = gstrEmployeeId           'Batch code
                    End If
                    cell.Offset(0, 15).value = gstrEmployeeId           'Employee Code
                    cell.Offset(0, 16).value = cell.Offset(0, 11).value 'Department
                    cell.Offset(0, 17).value = cell.Offset(0, 7).value  'PayType
                    cell.Offset(0, 18).value = cell.Offset(0, 6).value  'Hours
                    cell.Offset(0, 19).value = cell.Offset(0, 8).value  'Job
                    cell.Offset(0, 20).value = cell.Offset(0, 9).value  'Phase
                    cell.Offset(0, 21).value = cell.Offset(0, 10).value 'Cost Type
                    cell.Offset(0, 22).value = cell.Offset(0, 0).value  'Date
                    cell.Offset(0, 23).value = cell.Offset(0, 13).value 'Message
                    cell.Offset(0, 24).value = "'"                      'Payrate Level
                    cell.Offset(0, 25).value = "'"                      'WageCode
                    cell.Offset(0, 26).value = "'"                      'UnionCode
                    cell.Offset(0, 27).value = "'"                      'WorkersComp
                    cell.Offset(0, 28).value = "'"                      'StateTaxCode
                    cell.Offset(0, 29).value = "'"                      'CountyTaxCode
                    cell.Offset(0, 30).value = "'"                      'LocalTaxCode
                    cell.Offset(0, 31).value = "'"                      'EquipmentCode
                    cell.Offset(0, 32).value = "'"                      'EquipmentHours
                    cell.Offset(0, 33).value = "'"                      'Qty
                    cell.Offset(0, 34).value = "'"                      'CompanyCode
                    cell.Offset(0, 35).value = "'"                      'PayRate
                    cell.Offset(0, 36).value = "'"                      'EquipmentCostCategory
                    cell.Offset(0, 37).value = "'"                      'CrewNumber
                    cell.Offset(0, 38).value = cell.Offset(0, 12).value 'Cost Center
                    cell.Offset(0, 39).value = cell.Offset(0, 2).value  'WorkOrder
                    cell.Offset(0, 40).value = "'"                      'SiteEquipment
                    cell.Offset(0, 41).value = "'"                      'SiteComponent
                    cell.Offset(0, 42).value = "'"                      'Contract
                    cell.Offset(0, 43).value = "'"                      'EquipmentWorkOrder
                End If
            Next
          
           
            Application.EnableEvents = True
           
            strTmpCSV = RangeToCSV(ws.Range("TimesheetEntry"))
          
            Print #intF, strTmpCSV
            Close #intF
            
            
        End If
       
    Next
   ' Kill gstrSavedWorkbookPath
    gstrSavedWorkbookPath = Environ$("temp") & "\Timesheets.xlsm"
    
    ThisWorkbook.SaveCopyAs FileName:=gstrSavedWorkbookPath
    'ThisWorkbook.SaveAs gstrSavedWorkbookPath, xlOpenXMLWorkbookMacroEnabled
    'If MsgBox("Do you wish to email the output file", vbYesNo, "Email Dialog") = vbYes Then
        frmEmailDialog.Show
    'End If
    
PROC_EXIT:
    Application.EnableEvents = True
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.GenerateOutputFile"
    Resume PROC_EXIT
    
End Sub

Open in new window

Martin Liss

Well, I'm embarrassed since my conclusion is wrong. You are still passing the whole range.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Juan Velasquez

I just tested it with the new code, and you're right, the entire range is still being passed
Martin Liss

I tried to send the output csv file to myself by entering my email address but I never received anything, so try this. In my code above at line 47, instead of just checking for 0 or blank, add code that filters or hides those rows. I'm not sure about hiding them but if I remember correctly, filtering them out will cause them not to be in the range.
ASKER
Juan Velasquez

As a test I tried manually filtering the timesheets and reran the code and the whole range was still passed
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Juan Velasquez

Hello Martin,

I have to agree.  I came with a possible solution, but it was a messy and difficult to maintain.  I'm going try your approach. After I populate columnsO to AQ, I'll loop through the worksheets again and append the rows to the csv file.  

Private Sub GenerateOutputFile()
    ' Comments:
    ' Params  :
    ' Created : 08/13/13 14:42 JV
    ' Modified:
    
    On Error GoTo PROC_ERR
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strFilter As String
    Dim rng As Range
    Dim OutputRange As Range
    Dim strJobType As String
    Dim strTmpCSV As String 'string to hold the CSV info
    Dim intF As Integer
    Dim cell As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
 
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    KillProperly (gstrFilePath)
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    
    strJobType = wb.Worksheets("Summary").Range("JobType").value
    
    
    For Each ws In wb.Worksheets
        ws.Unprotect
        If Left(ws.Name, 3) = "WTS" Then
            
            intF = FreeFile
             
           
            
            gstrEmployeeId = ws.Range("EmployeeID").value
            Application.EnableEvents = False
            
            ws.Unprotect
            Set rng = ws.Range("WorkDate")
            For Each cell In rng.Cells
                If strJobType = "Construction" Then
                    cell.Offset(0, 14).value = cell.Offset(0, 8).value  'Batch code
                ElseIf strJobType = "Service" Then
                    cell.Offset(0, 14).value = gstrEmployeeId           'Batch code
                End If
                cell.Offset(0, 15).value = gstrEmployeeId           'Employee Code
                cell.Offset(0, 16).value = cell.Offset(0, 11).value 'Department
                cell.Offset(0, 17).value = cell.Offset(0, 7).value  'PayType
                cell.Offset(0, 18).value = cell.Offset(0, 6).value  'Hours
                cell.Offset(0, 19).value = cell.Offset(0, 8).value  'Job
                cell.Offset(0, 20).value = cell.Offset(0, 9).value  'Phase
                cell.Offset(0, 21).value = cell.Offset(0, 10).value 'Cost Type
                cell.Offset(0, 22).value = cell.Offset(0, 0).value  'Date
                cell.Offset(0, 23).value = cell.Offset(0, 13).value 'Message
                cell.Offset(0, 24).value = "'"                      'Payrate Level
                cell.Offset(0, 25).value = "'"                      'WageCode
                cell.Offset(0, 26).value = "'"                      'UnionCode
                cell.Offset(0, 27).value = "'"                      'WorkersComp
                cell.Offset(0, 28).value = "'"                      'StateTaxCode
                cell.Offset(0, 29).value = "'"                      'CountyTaxCode
                cell.Offset(0, 30).value = "'"                      'LocalTaxCode
                cell.Offset(0, 31).value = "'"                      'EquipmentCode
                cell.Offset(0, 32).value = "'"                      'EquipmentHours
                cell.Offset(0, 33).value = "'"                      'Qty
                cell.Offset(0, 34).value = "'"                      'CompanyCode
                cell.Offset(0, 35).value = "'"                      'PayRate
                cell.Offset(0, 36).value = "'"                      'EquipmentCostCategory
                cell.Offset(0, 37).value = "'"                      'CrewNumber
                cell.Offset(0, 38).value = cell.Offset(0, 12).value 'Cost Center
                cell.Offset(0, 39).value = cell.Offset(0, 2).value  'WorkOrder
                cell.Offset(0, 40).value = "'"                      'SiteEquipment
                cell.Offset(0, 41).value = "'"                      'SiteComponent
                cell.Offset(0, 42).value = "'"                      'Contract
                cell.Offset(0, 43).value = "'"                      'EquipmentWorkOrder
            Next
          
           
            Application.EnableEvents = True
           
            
            
        End If
       
    Next
    
    
    Dim lngLastRow As Long
    Dim lngFirstRow As Long
    Dim lngRow As Long
    
    For Each ws In Worksheets
        If Left(ws.Name, 3) = "WTS" Then
            intF = FreeFile
             
            Open gstrFilePath For Append As #intF
            ' The 1st section
            lngLastRow = Range("A65536").End(xlUp).row
            lngFirstRow = 15
            For lngRow = lngFirstRow To lngLastRow
                If Cells(lngRow, 7) <> 0 And Cells(lngRow, 7) <> 0 Then
                    Open gstrFilePath For Append As #intF
                  ' strTmpCSV = RangeToCSV(ws.Range())
                    Print #intF, strTmpCSV
                    Close #intF
                End If
            Next
            ' the second section - only needed if you actually get any info for the csv from here
            For lngRow = lngLastRow To lngLastRow + 20
                If Cells(lngRow, 7) = "Date" Then
                    lngFirstRow = lngRow + 1
                    Exit For
                End If
            Next
            lngLastRow = Range("G65536").End(xlUp).row
            For lngRow = lngFirstRow To lngLastRow
                If Cells(lngRow, 8) <> 0 And Cells(lngRow, 8) <> 0 Then
                    Open gstrFilePath For Append As #intF
                  ' strTmpCSV = RangeToCSV(ws.Range())
                    Print #intF, strTmpCSV
                    Close #intF
                End If
            Next

        End If
    Next
    
     
            
          
          
    
    
    gstrSavedWorkbookPath = Environ$("temp") & "\Timesheets.xlsm"
    
    ThisWorkbook.SaveCopyAs FileName:=gstrSavedWorkbookPath
  
        frmEmailDialog.Show
    
    
PROC_EXIT:
    Application.EnableEvents = True
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.GenerateOutputFile"
    Resume PROC_EXIT
    

Open in new window

ASKER
Juan Velasquez

Hello Martin,

I've modified the code as shown below.  It looks like the range is being passed to RangeToCSV but I need to modify that function slightly as I am not populating strTmp

Private Sub GenerateOutputFile()
    ' Comments:
    ' Params  :
    ' Created : 08/13/13 14:42 JV
    ' Modified:
    
    On Error GoTo PROC_ERR
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strFilter As String
    Dim rng As Range
    Dim OutputRange As Range
    Dim strJobType As String
    Dim strTmpCSV As String 'string to hold the CSV info
    Dim intF As Integer
    Dim cell As Range
   
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
 
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    KillProperly (gstrFilePath)
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    
    strJobType = wb.Worksheets("Summary").Range("JobType").value
    
    
    For Each ws In wb.Worksheets
        ws.Unprotect
        If Left(ws.Name, 3) = "WTS" Then
            
            'intF = FreeFile
             
           
            
            gstrEmployeeId = ws.Range("EmployeeID").value
            Application.EnableEvents = False
            
            ws.Unprotect
            Set rng = ws.Range("WorkDate")
            For Each cell In rng.Cells
                If strJobType = "Construction" Then
                    cell.Offset(0, 14).value = cell.Offset(0, 8).value  'Batch code
                ElseIf strJobType = "Service" Then
                    cell.Offset(0, 14).value = gstrEmployeeId           'Batch code
                End If
                cell.Offset(0, 15).value = gstrEmployeeId           'Employee Code
                cell.Offset(0, 16).value = cell.Offset(0, 11).value 'Department
                cell.Offset(0, 17).value = cell.Offset(0, 7).value  'PayType
                cell.Offset(0, 18).value = cell.Offset(0, 6).value  'Hours
                cell.Offset(0, 19).value = cell.Offset(0, 8).value  'Job
                cell.Offset(0, 20).value = cell.Offset(0, 9).value  'Phase
                cell.Offset(0, 21).value = cell.Offset(0, 10).value 'Cost Type
                cell.Offset(0, 22).value = cell.Offset(0, 0).value  'Date
                cell.Offset(0, 23).value = cell.Offset(0, 13).value 'Message
                cell.Offset(0, 24).value = "'"                      'Payrate Level
                cell.Offset(0, 25).value = "'"                      'WageCode
                cell.Offset(0, 26).value = "'"                      'UnionCode
                cell.Offset(0, 27).value = "'"                      'WorkersComp
                cell.Offset(0, 28).value = "'"                      'StateTaxCode
                cell.Offset(0, 29).value = "'"                      'CountyTaxCode
                cell.Offset(0, 30).value = "'"                      'LocalTaxCode
                cell.Offset(0, 31).value = "'"                      'EquipmentCode
                cell.Offset(0, 32).value = "'"                      'EquipmentHours
                cell.Offset(0, 33).value = "'"                      'Qty
                cell.Offset(0, 34).value = "'"                      'CompanyCode
                cell.Offset(0, 35).value = "'"                      'PayRate
                cell.Offset(0, 36).value = "'"                      'EquipmentCostCategory
                cell.Offset(0, 37).value = "'"                      'CrewNumber
                cell.Offset(0, 38).value = cell.Offset(0, 12).value 'Cost Center
                cell.Offset(0, 39).value = cell.Offset(0, 2).value  'WorkOrder
                cell.Offset(0, 40).value = "'"                      'SiteEquipment
                cell.Offset(0, 41).value = "'"                      'SiteComponent
                cell.Offset(0, 42).value = "'"                      'Contract
                cell.Offset(0, 43).value = "'"                      'EquipmentWorkOrder
            Next
          
           
            Application.EnableEvents = True
           
            
            
        End If
       
    Next
    
    
    Dim lngLastRow As Long
    Dim lngFirstRow As Long
    Dim lngRow As Long
    
    For Each ws In Worksheets
        If Left(ws.Name, 3) = "WTS" Then
            intF = FreeFile
             
            'Open gstrFilePath For Append As #intF
            ' The 1st section
            'lngLastRow = Range("WorkDate").End(xlUp).row
            lngLastRow = ws.Range("WorkDate").Cells(1, 1).row + ws.Range("WorkDate").Rows.Count - 1
            lngFirstRow = 15
            For lngRow = lngFirstRow To lngLastRow
                If Cells(lngRow, 7).value <> 0 And Cells(lngRow, 7).value <> 0 Then
                    Open gstrFilePath For Append As #intF
                    
                    Set rng = Range(Cells(lngRow, 1), Cells(lngRow, 43))
                   
                    strTmpCSV = RangeToCSV(rng)
                    Print #intF, strTmpCSV
                    Close #intF
                End If
            Next
            ' the second section - only needed if you actually get any info for the csv from here
'            For lngRow = lngLastRow To lngLastRow + 20
'                If Cells(lngRow, 7) = "Date" Then
'                    lngFirstRow = lngRow + 1
'                    Exit For
'                End If
'            Next
'            lngLastRow = Range("G65536").End(xlUp).row
'            For lngRow = lngFirstRow To lngLastRow
'                If Cells(lngRow, 8) <> 0 And Cells(lngRow, 8) <> 0 Then
'                    Open gstrFilePath For Append As #intF
'                  ' strTmpCSV = RangeToCSV(ws.Range())
'                    Print #intF, strTmpCSV
'                    Close #intF
'                End If
'            Next

        End If
    Next
    
     
            
          
          
    
    
    gstrSavedWorkbookPath = Environ$("temp") & "\Timesheets.xlsm"
    
    ThisWorkbook.SaveCopyAs FileName:=gstrSavedWorkbookPath
  
        frmEmailDialog.Show
    
    
PROC_EXIT:
    Application.EnableEvents = True
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.GenerateOutputFile"
    Resume PROC_EXIT
    
End Sub


Public Function RangeToCSV(list As Range) As String
    ' Comments:
    ' Params  : list
    ' Returns : String
    ' Modified:

    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Dim strTmp As String
    Dim lngCurrentRow As Long
    Dim lngCurrentColumn As Long
    Dim rng As Range
    Dim xlsRange As Range


    If TypeName(list) = "Range" Then
        lngCurrentRow = 15
            For Each rng In list.Cells
                If Range(rng.Address).Column > 14 Then
                    If rng.row = lngCurrentRow Then
                        If strTmp = vbNullString Then
                            strTmp = rng.value
                        Else
                            strTmp = strTmp & "," & rng.value
                        End If
                    Else
                        lngCurrentRow = lngCurrentRow + 1
                        If strTmp = vbNullString Then
                            strTmp = rng.value
                        Else
                            strTmp = strTmp & Chr(10) & rng.value
                        End If
                    End If
                End If
             
            Next
    End If

    RangeToCSV = strTmp

    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Application.EnableEvents = True
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.RangeToCSV"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

Open in new window

ASKER
Juan Velasquez

I've requested that this question be closed as follows:

Accepted answer: 0 points for chtullu135's comment #a39451427

for the following reason:

Thanks for all your help
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Juan Velasquez

I mean't to assign the point to martin
ASKER
Juan Velasquez

I meant to assign the points to Martin
ASKER
Juan Velasquez

Thanks again for your help
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013