CODEWITHSUNDEEP
excelvba
srt
srt

Reputation: 541

How to convert string to Workbook name in VBA

Sub openwb()

Dim x260path As String

x260path = "E:\sarath\PTMetrics\20131002\D8 L538-L550 16MY\D8 L538-L550_16MY_Powertrain Metrics_" & Format(Date, "YYYYMMDD") - 1

Workbooks("x260path").Activate

ActiveWorkbook.SaveAs ["E:\sarath\PTMetrics\20131002\D8 L538-L550 16MY\D8 L538-L550_16MY_Powertrain Metrics_" & Format(Date, "YYYYMMDD")]

Debug.Print x260path

End Sub

Here, when i execute, an error says "subscript out of Range". And it appears on 4th line.when i use 'workbook' to declare 'x260path' instead of string, It shows another error saying "Object variable or with block variable not set" on line 3. Can u help?Why is this happening?

Upvotes: 1

Views: 1565

Answers (1)

user2140173
user2140173

Reputation:

In VBA the equivalent function to =Today() is Date() (*or Date)

x260path = CONCATENATE("..." & Date)

Alternatively, use Now() function ( that includes a time stamp as well though )

x260path = CONCATENATE("..." & Now)

debug.print Date

02/10/2013

and 

debug.print Now

02/10/2013 08:39:20

some of the spreadsheet functions are available to use with the WorksheetFunction class. For example

Sub Main()

    Dim sum As Double
    sum = WorksheetFunction.sum(10, 20)
    MsgBox sum

End Sub

Note: when you type the sum = WorksheetFunction. as soon as you type in the . you should get the VBA's Intelli-sense help. It is a list of all available functions you can use with the WorksheetFunction class.

enter image description here

In your case the =Concatenate function is equivalent to & operator in VBA. Therefore the easiest way would be to join two string using the & 

x260path = "C:\..." & date

If a function you are trying to use doesn't exist in the Intelli-sense you can create your own UDF or you can do some online research on how the function works and how to overwrite it.

Upvotes: 1

Related Questions