mikeoly
12-14-2015, 10:51 AM
Hi Everyone,
I have this code here that seems to be working great. I'm using it to output some data from a model, but I noticed that this code didn't catch all the populated rows. Any idea why the rows wouldn't be counted correctly? The .CSV file gets populated with data from A5:AW17, but what I really want is A5:AW19.
Also, please feel free to offer suggestions to improve my code in other ways!
Thanks!
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Msg As String, Ans As Variant
Dim wline As Variant
Dim r As Integer
Dim lcol As Integer
Dim strDir As String
If Sheets("Sheet1").OLEObjects("CheckBox1").Object.Value = True Then
wline = ""
With Worksheets("Output")
For r = 5 To .Cells(.Rows.Count, "A").End(xlUp).Row
lcol = .Cells(r, 256).End(xlToLeft).Column
On Error Resume Next
wline = wline & Join(Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol))), ",") & vbNewLine
Next r
End With
'If current quarter folder doesn't exist, create it
DataAsOf = Sheets("Output").Range("A2").Value
strDir = Trim('\\yadadada" & DataAsOf)
If Dir(strDir, vbDirectory) = NullString Then
MkDir (strDir)
Else: End If
Open "file://yadadada" & DataAsOf & "\" & Sheets("Output").Range("C5").Value & " " & DataAsOf & " (" & Sheets("Output").Range("E5").Value & ")" & ".csv" For Output As #1 'Replaces existing file
Print #1, wline
Close #1
Else
Exit Sub
End If
Quit:
End Sub
I have this code here that seems to be working great. I'm using it to output some data from a model, but I noticed that this code didn't catch all the populated rows. Any idea why the rows wouldn't be counted correctly? The .CSV file gets populated with data from A5:AW17, but what I really want is A5:AW19.
Also, please feel free to offer suggestions to improve my code in other ways!
Thanks!
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Msg As String, Ans As Variant
Dim wline As Variant
Dim r As Integer
Dim lcol As Integer
Dim strDir As String
If Sheets("Sheet1").OLEObjects("CheckBox1").Object.Value = True Then
wline = ""
With Worksheets("Output")
For r = 5 To .Cells(.Rows.Count, "A").End(xlUp).Row
lcol = .Cells(r, 256).End(xlToLeft).Column
On Error Resume Next
wline = wline & Join(Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol))), ",") & vbNewLine
Next r
End With
'If current quarter folder doesn't exist, create it
DataAsOf = Sheets("Output").Range("A2").Value
strDir = Trim('\\yadadada" & DataAsOf)
If Dir(strDir, vbDirectory) = NullString Then
MkDir (strDir)
Else: End If
Open "file://yadadada" & DataAsOf & "\" & Sheets("Output").Range("C5").Value & " " & DataAsOf & " (" & Sheets("Output").Range("E5").Value & ")" & ".csv" For Output As #1 'Replaces existing file
Print #1, wline
Close #1
Else
Exit Sub
End If
Quit:
End Sub