How to define a file name

[ThrottleWorks]

Hi, I have a problem regarding defining a file name.

I have a macro, this macro captures data from another file.

File names are as below.


Macro file - ABC_Volume_Macro.xlsm


File from which I am capturing data - Repository.xlsm


Whenever I refer to file, for example macro file, I write code such as


Workbooks.(“ ABC_Volume_Macro.xlsm”).worksheets (“Working”).range(“a1”).value = from_date


Is there any to shorten “Workbooks.(“ ABC_Volume_Macro.xlsm”)”.

Can I define this name at the start of the code & then just use the abbreviated name in the code.

Can anyone help me in this please.

 

[Debraj]

Hi sachinbizboy,


You can easily define name of any objects in EXCEL.

[pre]

Dim WBName As Workbook
Set WBName = Workbooks(“ABC_Volume_Macro.xlsm”)

[/pre]

Now you can access the same workbook with WBName


Regards,

Deb

 

[ThrottleWorks]

Hi Debraj Sir, thanks a lot for the reply, I am trying this, will definately share the results. Have a nice day ahead.

 

[nazmul_muneer]

Dear sachinbizboy

You can do this by copying the sheet from you want to export data. Try....


Sub Creating_new_file()

On Error GoTo errH

Sheets("Source_file").Select

Range("A1").Select

Sheets("Sheet1").Select

Sheets("Sheet1").Copy

Range("A1").Select


ActiveWorkbook.SaveAs Filename:="D:Folder_NameNew_file.xlsx", FileFormat:= _

xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _

, CreateBackup:=False

Range("A1").Select

Exit Sub

errH:

MsgBox Err.Description

End Sub

 

[ThrottleWorks]

@ Debraj Sir, it has worked ! thanks a lot.


@Nazmul Sir, thanks a lot for the reply, I am trying this, will definately share the results. Have a nice day ahead.