PDA

View Full Version : [SOLVED] Exporting Data to .txt - Need Help Setting Directory!



mikeoly
04-01-2015, 12:00 PM
Hi All,

I can't get my code to output the text file to the correct directory...

It was working when the item that I've highlighted in blue was 'ThisWorkbook.Path', but unfortunately that doesn't suite my needs. The two pieces highlighted was my revision...


g_strAllModelsDir refers to "H:/Review/"

Thanks in advance!!


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Msg As String, Ans As Variant
Dim wline As String
Dim r As Integer
Dim lcol As Integer
Dim Lastrow As Integer

DataWarehouseDir = Dir(g_strAllModelsDir & "\_Data Warehouse")
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

Open DataWarehouseDir & "\" & Sheets("Output").Range("C5").Value & Sheets("Output").Range("E5").Value & " data.csv" For Output As #1 'Replaces existing file
Print #1, wline
Close #1


Quit:
End Sub