mikeoly
09-23-2015, 08:24 AM
Hi All,
I'm running into a Type Mismatch error that's driving me bonkers :doh:-- line 11 ("wline=wline & Join....). I realize that this may not be the cleanest code as well, so feel free to offer any additional comments/suggestions.
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("IBNRPack").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
wline = wline & Join(Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol))), ",") & vbNewLine
Next r
End With
DataAsOf = Sheets("Output").Range("A2").Value
strDir = Trim(\\yadayada\Output\"]\\yadayada\Output\"]\\yadayada\Output\ (file://\\yadayada\Output\)& DataAsOf)
If Dir(strDir, vbDirectory) = NullString Then
MkDir (strDir)
Else: End If
Open "\\yadayada\Output\" & 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
Thank you so much!
I'm running into a Type Mismatch error that's driving me bonkers :doh:-- line 11 ("wline=wline & Join....). I realize that this may not be the cleanest code as well, so feel free to offer any additional comments/suggestions.
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("IBNRPack").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
wline = wline & Join(Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol))), ",") & vbNewLine
Next r
End With
DataAsOf = Sheets("Output").Range("A2").Value
strDir = Trim(\\yadayada\Output\"]\\yadayada\Output\"]\\yadayada\Output\ (file://\\yadayada\Output\)& DataAsOf)
If Dir(strDir, vbDirectory) = NullString Then
MkDir (strDir)
Else: End If
Open "\\yadayada\Output\" & 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
Thank you so much!