Excel Saving file name

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
143
Office Version
  1. 2010
Hello Folks

I have a code that pulls data but I want to make it so I can save the file as the "20200305 - Employee Name",

Please see code below and any suggestions will be taken on board with the coding!

Also, if possible a string to save the file path to e.g "J:\SaveFileHere" which is editable?

VBA Code:
Option Explicit


Const FOLDER_PATH = "File_Path_Here"  'REMEMBER END BACKSLASH


Sub ImportWorksheets()
   '=============================================
   'Process all Excel files in specified folder
   '=============================================
   Dim sFile As String           'file to process
   Dim wsTarget As Worksheet
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim FileName As String
   Dim rowTarget As Long         'output row
   Dim MyDate
   Dim Month
   
   rowTarget = 2
   
   'check the folder exists
   If Not FileFolderExists(FOLDER_PATH) Then
      MsgBox "Specified folder does not exist, exiting!"
      Exit Sub
   End If
   
   'reset application settings in event of error
   On Error GoTo errHandler
   Application.ScreenUpdating = False
   
   'set up the target worksheet
   Set wsTarget = Sheets("Sheet1")
   
   'loop through the Excel files in the folder
   sFile = Dir(FOLDER_PATH & "*.xls*")
   Do Until sFile = ""
   
      Set wbSource = Workbooks.Open(FOLDER_PATH & sFile)
      Set wsSource = wbSource.Worksheets(1)
      
      'import the data
      With wsTarget
         .Range("A" & rowTarget).Value = wsSource.Range("B4:C4").Value 
         .Range("B" & rowTarget).Value = wsSource.Range("B5:C5").Value  
         .Range("C" & rowTarget).Value = wsSource.Range("C9:D9").Value 
         .Range("D" & rowTarget).Value = wsSource.Range("E4").Value  
         .Range("E" & rowTarget).Value = wsSource.Range("B33:C33").Value 
         .Range("F" & rowTarget).Value = wsSource.Range("B34:C34").Value    
         .Range("G" & rowTarget).Value = wsSource.Range("A26:E30").Value   
         .Range("I" & rowTarget).Value = sFile 'Source File
         
          FileName = Range("A" & rowTarget).Value = wsSource.Range("B4:C4").Value   'Payroll No
      End With

    MyDate = Format(Date, "yyyymmdd")
    Month = Format(Date, "mmmm")
    
    ActiveWorkbook.SaveAs ("File_path_Here" & Format(Now(), "yyyymmdd - ") & FileName & ".xlsx")
    Application.DisplayAlerts = False
    wbSource.Close
    rowTarget = rowTarget + 1
    sFile = Dir()
   Loop
   
errHandler:
   On Error Resume Next
   Application.ScreenUpdating = True

   Set wsSource = Nothing
   Set wbSource = Nothing
   Set wsTarget = Nothing
End Sub

Private Function FileFolderExists(strPath As String) As Boolean
    If Not Dir(strPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function

Thanks in advance
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
159
Office Version
  1. 2010
Platform
  1. Windows
Hello!
First you need to decide which files with which extension you want to save. At least because the designated xlsx format will not allow you to save macros.
Secondly, it’s better to declare all variables at the beginning of the code immediately. I mean "Const FOLDERPATH as String ="c:\My\Long\Path", e.g., and "Dim MyDate as String" (i don't see where the variable Month is used, maybe it's not needed).
Also where does employee names from?
But if you need date plus name default file only, and .xlsx is your choice, try
VBA Code:
ActiveWorkbook.SaveAs (FOLDERPATH & MyDate & " - " & FileName & ".xlsx")
 

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
143
Office Version
  1. 2010
Hello!
First you need to decide which files with which extension you want to save. At least because the designated xlsx format will not allow you to save macros.
Secondly, it’s better to declare all variables at the beginning of the code immediately. I mean "Const FOLDERPATH as String ="c:\My\Long\Path", e.g., and "Dim MyDate as String" (i don't see where the variable Month is used, maybe it's not needed).
Also where does employee names from?
But if you need date plus name default file only, and .xlsx is your choice, try
VBA Code:
ActiveWorkbook.SaveAs (FOLDERPATH & MyDate & " - " & FileName & ".xlsx")

Hi bug,

Thanks for your response.

The date as string is used to save the file in the month and employees name is from Cell A2 in each workbook.

When excel accesses the workbook and imports the data I just want to rename the file and save it in a different folder path which I'm hoping can be done as string

Hope this makes sense!
 

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
159
Office Version
  1. 2010
Platform
  1. Windows
Correct files path and try this.
VBA Code:
Option Explicit

Const FOLDERPATH As String = "C:\Users\My\Long\Path\"

Sub ImportWorksheets()
   '=============================================
   'Process all Excel files in specified folder
   '=============================================
   Dim sFile As String           'file to process
   Dim wsTarget As Worksheet
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim FileName As String
   Dim rowTarget As Long         'output row
   Dim MyDate As String, SumName As String  'formatted date and final file names with path and extension
  
   rowTarget = 2
  
   'check the folder exists
   If Not FileFolderExists(FOLDERPATH) Then
      MsgBox "Specified folder does not exist, exiting!"
      Exit Sub
   End If
  
   'reset application settings in event of error
   On Error GoTo errHandler
   Application.ScreenUpdating = False
  
   'set up the target worksheet
   Set wsTarget = Sheets("Sheet1")
  
   'loop through the Excel files in the folder
   sFile = Dir(FOLDERPATH + "*.xlsx")
   Do Until sFile = ""
  
      Set wbSource = Workbooks.Open(FOLDERPATH & sFile)
      Set wsSource = wbSource.Worksheets(1)
'
'      'import the data
      With wsTarget
         .Range("A" & rowTarget).Value = wsSource.Range("B4:C4").Value
         .Range("B" & rowTarget).Value = wsSource.Range("B5:C5").Value
         .Range("C" & rowTarget).Value = wsSource.Range("C9:D9").Value
         .Range("D" & rowTarget).Value = wsSource.Range("E4").Value
         .Range("E" & rowTarget).Value = wsSource.Range("B33:C33").Value
         .Range("F" & rowTarget).Value = wsSource.Range("B34:C34").Value
         .Range("G" & rowTarget).Value = wsSource.Range("A26:E30").Value
         .Range("I" & rowTarget).Value = sFile 'Source File
        
          FileName = Range("A" & rowTarget).Value '= wsSource.Range("B4:C4").Value    'Payroll No
      End With

    MyDate = Format(Date, "yyyymmdd")
'    Month = Format(Date, "mmmm")

    SumName = FOLDERPATH & MyDate & " - " & FileName & ".xlsx"
    ActiveWorkbook.SaveAs SumName
    
    Application.DisplayAlerts = False
    wbSource.Close
    rowTarget = rowTarget + 1
    sFile = Dir()
   Loop
  
errHandler:
   On Error Resume Next
   Application.ScreenUpdating = True

   Set wsSource = Nothing
   Set wbSource = Nothing
   Set wsTarget = Nothing
End Sub

Private Function FileFolderExists(strPath As String) As Boolean
    If Not Dir(strPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,130,167
Messages
5,640,540
Members
417,151
Latest member
ChickenTenderer

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
Top