Was after some input that I have so far had trouble figuring out on my own...

1. If I wanted the location (i.e. C:\Users\SB\Documents\CSV Uploads) to be stored in another sheet (LOOKUP DATA), in cell "C13" (Defined Name: FOLDERLOCATION) and used instead of having it in the code, can this be done?

2. While the below works to export the sheet to a CSV file to the folder I have specified, the file ends up being a lot larger than I expected. The file ends up being over 9mb! The weird thing is if I open, then save the file again and close, it drops down to around 38kb. Any ideas what I am doing wrong here?

Thanks in advance, I look forward to seeing what you experts think!

Sub EXPORTCSV()

Dim Path As String
Dim filename As String

    Sheets("UPLOAD").Visible = True
    Sheets("UPLOAD").Copy
    ActiveWorkbook.SaveAs ("C:\Users\SB\Documents\CSV Uploads\UPLOAD - IB " & Format(Now(), "YYYYMMDD - hh_mm_ss AMPM") & ".csv") _
        , FileFormat:=xlCSV, CreateBackup:=False
 
    ActiveWorkbook.Close

End Sub

CodePudding user response：

With regards to your point 1, yes, you can use a cell to store the root path. I have rewritten some of your code for clarity, but if you want to keep the same structure that you already have, just replace the ActiveWorkbook.SaveAs ("C:\Users\SB\Documents\CSV Uploads\UPLOAD - IB " & Format(Now(), "YYYYMMDD - hh_mm_ss AMPM") & ".csv"), FileFormat:=xlCSV, CreateBackup:=False

with

ActiveWorkbook.SaveAs (ActiveWorkbook.Sheets("UPLOAD").Range("C13").Value & Format(Now(), "YYYYMMDD - hh_mm_ss AMPM") & ".csv"), FileFormat:=xlCSV, CreateBackup:=False

A few other notes:

• Using ThisWorkbook rather than ActiveWorkbook is safer because it will always refer to the workbook that the VBA code is residing in rather than whichever workbook happens to be active at the time.
• Be careful with the Workbook.Close method, especially since there is no confirmation to close. You could easily lose your work, and since CSV files don't save VBA code, it would be even worse.

Private Sub EXPORTCSV_MOD()

    ' Parameters of the file path
    Dim Path As String, Filename As String, Extension As String
    
    Path = ThisWorkbook.Sheets("UPLOAD").Range("C13").Value
    Filename = Format(Now(), "YYYYMMDD - hh_mm_ss AMPM")
    Extension = ".csv"
    
    ' Assemble the full file path
    Dim FullPath As String
    FullPath = Path & Application.PathSeparator & Filename & Extension

    ' Save and close the workbook
    ThisWorkbook.SaveAs Filename:=FullPath, FileFormat:=xlCSV, CreateBackup:=False
 
    ThisWorkbook.Close

End Sub

CodePudding user response：

Thx @TehDrunkSailor, with a slight tweak using your logic, this resolved my code, you legend!! The reason I am using Active and not This is because I am saving the copied sheet into a new workbook, not the workbook I have been working in.

Sub EXPORTCSV()

Dim Path As String
Dim filename As String
'The UPLOAD sheet was very hidden
    Sheets("UPLOAD").Visible = True
'Copy to a new workbook
    Sheets("UPLOAD").Copy
'Save the new workbook using data stored in the original workbook
    ActiveWorkbook.SaveAs (ThisWorkbook.Sheets("LOOKUP DATA").Range("C13").Value & "UPLOAD - IB  " _
        & Format(Now(), "YYYYMMDD - hh_mm_ss AMPM") & ".csv"), FileFormat:=xlCSV, CreateBackup:=False

'Close the new workbook
    ActiveWorkbook.Close

End Sub