VBA - Save worksheet only into a specific folder - name file based on cell value

[bored622]

I'm trying to make a macro that when the user presses a button, it saves the worksheet based on a specific cell value and folder. Right now I got it to work so that it saves the entire workbook into the folder but I want the user to have the option to just save the worksheet itself.

My code with saving the entire workbook.

Sub SavePTSFile()

Dim FileName As String
Dim Path As String
Application.DisplayAlerts = False
Path = "J:\Control_Center\PTS Distro Wave Files\"
FileName = Range("X3").Value & ".xlsx"
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
Application.DisplayAlerts = True


End Sub

 

[HaHoBe]

Hi bored622,

how about

Sub SavePTSFile_mod()

Dim strFileName As String
Dim strPath As String

strPath = "J:\Control_Center\PTS Distro Wave Files\"
strFileName = Range("X3").Value & ".xlsx"

'check for file and delete
If Len(Dir(strPath & strFileName)) > 0 Then
  Kill strPath & strFileName
End If

ActiveSheet.Copy
ActiveWorkbook.SaveAs strPath & strFileName, xlOpenXMLWorkbook
'close workbook with save
'ActiveWorkbook.Close True

End Sub

Ciao,
Holger

 

[bored622]

Hi bored622,

how about

Sub SavePTSFile_mod()

Dim strFileName As String
Dim strPath As String

strPath = "J:\Control_Center\PTS Distro Wave Files\"
strFileName = Range("X3").Value & ".xlsx"

'check for file and delete
If Len(Dir(strPath & strFileName)) > 0 Then
  Kill strPath & strFileName
End If

ActiveSheet.Copy
ActiveWorkbook.SaveAs strPath & strFileName, xlOpenXMLWorkbook
'close workbook with save
'ActiveWorkbook.Close True

End Sub

Ciao,
Holger

Sorry for the late response, but what does the code do exactly? Just curious because i see a comment that says 'check for file and delete'.

 

[HaHoBe]

Hi bored622,

please find the code with comments:

Sub SavePTSFile_mod()

Dim strFileName As String
Dim strPath As String

'/// drive and path to Folder for saving
strPath = "J:\Control_Center\PTS Distro Wave Files\"
'/// get the filename from ActiveSheet Cell X3 and append dot and extension for saving
strFileName = Range("X3").Value & ".xlsx"

'/// check for any existing file at the given ülace using Dir
If Len(Dir(strPath & strFileName)) > 0 Then
  '/// delete the existing file without putting it into the RecycleBin
  Kill strPath & strFileName
End If

'/// copy the ActiveSheet as a new workbook
ActiveSheet.Copy
'/// save the ActiveWorkbook at folder and with name as indicated as macrofree workbook
ActiveWorkbook.SaveAs strPath & strFileName, xlOpenXMLWorkbook
'/// as the workbook stays open you may close it with saving (True) or without (False)
'close workbook with save
'ActiveWorkbook.Close True

End Sub

Normally you will get a notification if you try and save a file to a folder where a file of that name already exists (same goes for deleting sheets or closing a workbook without saving where you altered values or use a volatile function). You may turn the notifications off by using Application.DisplayAlerts = False but you should turn them on again at the end of the code with Application.DisplayAlerts = True. The code above checks for the existence of a file and if so will delete it permanently (there will be no information from Excel).

HTH,
Holger

 

[bored622]

Hi bored622,

please find the code with comments:

Sub SavePTSFile_mod()

Dim strFileName As String
Dim strPath As String

'/// drive and path to Folder for saving
strPath = "J:\Control_Center\PTS Distro Wave Files\"
'/// get the filename from ActiveSheet Cell X3 and append dot and extension for saving
strFileName = Range("X3").Value & ".xlsx"

'/// check for any existing file at the given ülace using Dir
If Len(Dir(strPath & strFileName)) > 0 Then
  '/// delete the existing file without putting it into the RecycleBin
  Kill strPath & strFileName
End If

'/// copy the ActiveSheet as a new workbook
ActiveSheet.Copy
'/// save the ActiveWorkbook at folder and with name as indicated as macrofree workbook
ActiveWorkbook.SaveAs strPath & strFileName, xlOpenXMLWorkbook
'/// as the workbook stays open you may close it with saving (True) or without (False)
'close workbook with save
'ActiveWorkbook.Close True

End Sub

Normally you will get a notification if you try and save a file to a folder where a file of that name already exists (same goes for deleting sheets or closing a workbook without saving where you altered values or use a volatile function). You may turn the notifications off by using Application.DisplayAlerts = False but you should turn them on again at the end of the code with Application.DisplayAlerts = True. The code above checks for the existence of a file and if so will delete it permanently (there will be no information from Excel).

HTH,
Holger

Will this keep the workbook I originally had open when I run this macro, or will it close it out after I have typed something in cell X3?

 

[bored622]

Hi bored622,

please find the code with comments:

Sub SavePTSFile_mod()

Dim strFileName As String
Dim strPath As String

'/// drive and path to Folder for saving
strPath = "J:\Control_Center\PTS Distro Wave Files\"
'/// get the filename from ActiveSheet Cell X3 and append dot and extension for saving
strFileName = Range("X3").Value & ".xlsx"

'/// check for any existing file at the given ülace using Dir
If Len(Dir(strPath & strFileName)) > 0 Then
  '/// delete the existing file without putting it into the RecycleBin
  Kill strPath & strFileName
End If

'/// copy the ActiveSheet as a new workbook
ActiveSheet.Copy
'/// save the ActiveWorkbook at folder and with name as indicated as macrofree workbook
ActiveWorkbook.SaveAs strPath & strFileName, xlOpenXMLWorkbook
'/// as the workbook stays open you may close it with saving (True) or without (False)
'close workbook with save
'ActiveWorkbook.Close True

End Sub

Normally you will get a notification if you try and save a file to a folder where a file of that name already exists (same goes for deleting sheets or closing a workbook without saving where you altered values or use a volatile function). You may turn the notifications off by using Application.DisplayAlerts = False but you should turn them on again at the end of the code with Application.DisplayAlerts = True. The code above checks for the existence of a file and if so will delete it permanently (there will be no information from Excel).

HTH,
Holger

Just tested it and answered my own question (haha)! Thank you so much for the help. It worked perfectly!