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

[jlal1990]

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]

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...

? Dir(MyPath & MyFileName, vbNormal)

Does it return the filename? If not, it's not a valid path and filename.

 

[jlal1990]

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...

? 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]

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)...

? 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?

 

[Domenic]

Also, you said that you wanted to add a time stamp as well, so maybe something like this...

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

 

[jlal1990]

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)...

? 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]

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]

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.