Create .csv file from a workbook sheet with new filename and timestamp

jlal1990

New Member
Joined
Jul 13, 2019
Messages
7
Hi all,

Im not great at VBA by any means but since moving to mac I cant seem to do anything i used to do with ease.

I am simply trying to create a macro that copies a sheet in my active workbook to a new .csv file with that sheet name as the title and todays date and time in the newly created filename.

The code below is something i found on this very forum after many hours of searching but it seems to get stuck as 'SaveAs FileName' and excel spits out this error message:

"GROWBOTSSS130719.csv" cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only."


Sub SAVECSV()
'
Dim MyPath As String
Dim MyFileName As String
'The path and file names:
MyPath = "/Users/joshualally/Documents/SALES DATA/CSV Files/Growbots"
MyFileName = "GROWBOTSSS" & Format(Date, "ddmmyy")
'Makes sure the path name ends with "":
If Not Right(MyPath, 1) = "/" Then MyPath = MyPath & "/"
'Makes sure the filename ends with ".csv"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
'Copies the sheet to a new workbook:
Sheets("Growbots Ingestion").Copy
'The new workbook becomes Activeworkbook:
With ActiveWorkbook
.SaveAs FileName:= _
MyPath & MyFileName, _
FileFormat:=xlCSV, _
CreateBackup:=False

End With
End Sub


Any help would be really appreciated!

Thanks, Josh
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
First, since your path doesn't specify a drive, it defaults to the current one, which may or may not be what you want. So, if the specified path and filename isn't located in the current drive, you'll get an error. I would suggest that you specify the drive to eliminate any possibility of an error.

If the above isn't an issue, maybe the path and filename is invalid within the current drive. Try running the code again. This time, though, when the error occurs, click on Debug. When it takes you to the line causing the error, enter the following code in the Immediate Window (Ctrl+G), and press ENTER...

Code:
? Dir(MyPath & MyFileName, vbNormal)
Does it return the filename? If not, it's not a valid path and filename.
 

jlal1990

New Member
Joined
Jul 13, 2019
Messages
7
First, since your path doesn't specify a drive, it defaults to the current one, which may or may not be what you want. So, if the specified path and filename isn't located in the current drive, you'll get an error. I would suggest that you specify the drive to eliminate any possibility of an error.

If the above isn't an issue, maybe the path and filename is invalid within the current drive. Try running the code again. This time, though, when the error occurs, click on Debug. When it takes you to the line causing the error, enter the following code in the Immediate Window (Ctrl+G), and press ENTER...

Code:
? Dir(MyPath & MyFileName, vbNormal)
Does it return the filename? If not, it's not a valid path and filename.
Thanks for the reply. It doesn't return the filename. It seems to work if i create a file with called 'GROWBOTSSS130719' (aka file name & todays date) - but that just replaces whats already there. I really just need code that creates a new file each time with
a new date and time.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
Sorry, I re-read your post and see now that you're dealing with a Mac version of Excel. Also, we should have been checking for a valid path, not a valid path and filename. Sorry, my mistake. It should have been (or the Mac equivalent)...

Code:
? Dir(MyPath, vbDirectory)
...which should return the folder name or a dot (.) in this case since the path ends in a frontslash (/). What does it return?
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
Also, you said that you wanted to add a time stamp as well, so maybe something like this...

Code:
MyFileName = "GROWBOTSSS" & Format(Now, "ddmmyy hh-mm-ss")
 

jlal1990

New Member
Joined
Jul 13, 2019
Messages
7
Sorry, I re-read your post and see now that you're dealing with a Mac version of Excel. Also, we should have been checking for a valid path, not a valid path and filename. Sorry, my mistake. It should have been (or the Mac equivalent)...

Code:
? Dir(MyPath, vbDirectory)
...which should return the folder name or a dot (.) in this case since the path ends in a frontslash (/). What does it return?
Thanks for your help! unfortunatly when i do this i i get this message: <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'; color: #000000 ; color: rgba(0, 0, 0, 0.85)}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'; color: #000000 ; color: rgba(0, 0, 0, 0.85); min-height: 12.0px}</style>
Run-time error '438':


Object doesn't support this property or method
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
As I mentioned, you should be using the Mac equivalent of the Dir function. Unfortunately, I don't use the Mac, so I can't be of any help to you. So, basically, you need to ensure that you're providing a valid path.
 

jlal1990

New Member
Joined
Jul 13, 2019
Messages
7
Thank you, it seems after much reseach this doesnt work with mac excel. the workaround is to use automator to rename the file once it is saved to a file.
 

Forum statistics

Threads
1,082,380
Messages
5,365,117
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top