I have a sheet in a workbook I would like to export to a csv file that gets updated every week. So ideally, I want the VBA code to export whatever data is in the sheet and overwrite what was existing in the path. The range of data is from A to AJ. The path of the folder will be on "C:\Users\HS"
I tried to adapt @VBasic2008 code at the location below to no avail.
EXCEL-VBA How to export to a CSV... a custom range of columns?
The result displays/flashes on the screen when I run it but doesn't get saved at the location specified.
Here is exactly what I had:
Option Explicit
Sub ExportColumnsToCSV()
Const sfRow As Long = 1
Const sColsList As String = "A:AJ"
Const dFirst As String = "A1"
Dim sCols() As String: sCols = Split(sColsList, ",")
Dim sws As Worksheet: Set sws = ActiveSheet
Dim swb As Workbook: Set swb = sws.Parent
Dim srrg As Range
Dim slCell As Range
Dim srCount As Long
With sws.Rows(sfRow)
Set slCell = .Resize(.Worksheet.Rows.Count - .Row + 1) _
.Find("*", , xlFormulas, , xlByRows, xlPrevious)
If slCell Is Nothing Then
MsgBox "No data in worksheet.", vbCritical, "Export to CSV"
Exit Sub
End If
srCount = slCell.Row - .Row + 1
Set srrg = .Resize(srCount)
End With
Dim srg As Range
Dim n As Long
For n = 0 To UBound(sCols)
If srg Is Nothing Then
Set srg = Intersect(srrg, sws.Columns(sCols(n)))
Else
Set srg = Union(srg, Intersect(srrg, sws.Columns(sCols(n))))
End If
Next n
Dim dwb As Workbook: Set dwb = Application.Workbooks.Add
srg.Copy
dwb.Worksheets(1).Range(dFirst).PasteSpecial xlPasteValues
Dim dFolderPath As String: dFolderPath = swb.Path & "C:\Users\HS"
On Error Resume Next
MkDir dFolderPath
On Error GoTo 0
Dim dFilePath As String
dFilePath = dFolderPath _
& Left(swb.Name, InStrRev(swb.Name, ".") - 1) & ".csv"
' Optionally, out-comment previous line and uncomment next one
' to save with the current worksheet name.
'dFilePath = dFolderPath & sws.Name & ".csv"
Application.DisplayAlerts = False
dwb.SaveAs Filename:=dFilePath, FileFormat:=xlCSVUTF8, Local:=False
dwb.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub
Thank you HS
Hope this helps, I think with the modifications in the comments should work for your application.
Sub ExportColumnsToCSV()
Dim rngTheRangeYouWant As Range
Set rngTheRangeYouWant = Application.ActiveSheet.Range("$A:$AJ")
Dim lBottomRow As Long
On Error Resume Next
lBottomRow = rngTheRangeYouWant.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error GoTo 0
If lBottomRow <= 1 Then
' This assumes there is no data but still has coulmn headers.
MsgBox "there is only 1 row of data here"
Else
Set rngTheRangeYouWant = Application.ActiveSheet.Range("$A$1:$AJ$" & lBottomRow)
With rngTheRangeYouWant
Dim oNewWorkbook As Workbook
Set oNewWorkbook = Application.Workbooks.Add
rngTheRangeYouWant.Copy
With oNewWorkbook
.Worksheets(1).Range("A1").PasteSpecial xlPasteValues
Application.DisplayAlerts = False
'Change this to your path with extension like
.SaveAs Filename:="C:\Users\yourPath\ThisFile.csv", FileFormat:=xlCSVUTF8, Local:=False
.Close
Application.DisplayAlerts = True
End With
End With
End If
Set rngTheRangeYouWant = Nothing
Set oNewWorkbook = Nothing
End Sub
Please mark my response as the answer if it works for you, thanks! Good Luck!