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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top