Rename files with VBA macro wildcard

[doriannjeshi]

Hello,

I need to rename a couple of files in a folder with a macro that use variable and constants in their names:

2508202300 rpt_shijet_OH_SL_IT_st_30_d.xls
250820601 SL ON HAN PER U.xls

Basically I want to give them a constant name to be able to use them with another macro
to rename them without the numbers that get generated daily, result:

rpt_shijet_OH_SL_IT_st_30_d.xls
SL ON HAN PER U.xls

If the filenames are present what options do I have , can I delete the old files and replace them with new files ?

 

[Trebor76]

Hi doriannjeshi,

Instead of renaming the files (which I dare say will cause issues as the file names will be duplicated in the folder once the date is removed) why not just exclude the date in your other macro like so:

Debug.Print Split("2508202300 rpt_shijet_OH_SL_IT_st_30_d.xls", " ")(1)

Regards,

Robert

 

[doriannjeshi]

Hi Robert,
How does this command work ?
does it remove the numbers of the date to the space delimiter ?

 

[Trebor76]

It splits a string by a given delimiter (a space in this case). As the function uses a zero based index the (1) tells it to return everything after the first space which is the date in this case.

Click here for a great overview of the SPLIT (which is a VBA only) function.

 

[doriannjeshi]

How would i use this command here?

Windows("280820241028 rpt_M2_OH_SL_IT_Last_30_days.xls").Activate
Sheets("sheet2").Select
Columns("A:A").Select
Selection.Copy

 

[Trebor76]

I'm not sure what you're trying to do

If the workbook is open and you want to copy Col. A from Sheet2 here's one way (I have also included the file name without the date in a message box):

Option Explicit
Sub Macro1()

    Dim wb As Workbook
    
    Set wb = Workbooks("280820241028 rpt_M2_OH_SL_IT_Last_30_days.xls")
    
    MsgBox Split(wb.Name, " ")(1)
    
    wb.Sheets("Sheet2").Range("A:A").Copy

End Sub

 

[doriannjeshi]

Hi Trebor,
Can I use this macro as part of a larger macro ?
which adds copying from another workbook with date to be removed as this one ?
And how do I open this file automatically and then copy paste the content?

 

[doriannjeshi]

I'm not sure what you're trying to do

If the workbook is open and you want to copy Col. A from Sheet2 here's one way (I have also included the file name without the date in a message box):

Option Explicit
Sub Macro1()

    Dim wb As Workbook
   
    Set wb = Workbooks("280820241028 rpt_M2_OH_SL_IT_Last_30_days.xls")
   
    MsgBox Split(wb.Name, " ")(1)
   
    wb.Sheets("Sheet2").Range("A:A").Copy

End Sub

this table is showing ( changed the report name to a lighter file)

 

[doriannjeshi]

how to avoid this table

 

[Trebor76]

Sorry but I'm lost I'm sure someone on the forum will be able to help