Link to home
Start Free TrialLog in
Avatar of EdLB

asked on

I am trying to get information out of a file name and assign it to a variable using VBA, formula works. Can't get variable assignment to work

Dim InvDate As String
    Dim Ret As Variant

Ret is the result of an object that gets a file name.

Returned filename in Ret is: C:\Users\John Q. Public\Documents\CompanyDir\Name\LowVoltageProducts\Client\ComplianceMonitor\Cmdata\Inventory15-08-03.xlsx

The following formula gets the file date from the file name when using it in Excel. I want to save the date in a variable and copy name to another cell in a different worksheet.

Code crashes here:
InvDate.Formula = "= Right(Left(Ret, Find(""."", Ret, 20) - 1), 8)"

Error msg is "Invalid Qualifier" and InvDate is highlighted,
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Define invDate as "Range".
You've defined invdate as a string  so the .formula doesn't mean anything to VBA at that point

try InvDate  = Right(Left(Ret, Find(""."", Ret, 20) - 1), 8) - if you are trying to assign invdate as a string or define invdate as a range and give it a cell address if you are trying to put the formula into a sheet.
You can also these expressions:
String result:

Open in new window

Datetime result:

Open in new window

If the file name is coming from a filesystemobject, there are other ways of parsing the filename.
Avatar of EdLB


When I try  InvDate = Right(Left(Ret, Find(""."", Ret, 20) - 1), 8)

I get a syntax error and the entire line is highlighted.
Avatar of EdLB


Aikimark, there are two periods in the Ret filename which is why I used the Find and started at 20.
Avatar of regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EdLB


That did it. Thanks! Is there some legend somewhere that lists what EXCEL functions work in VBA or what the VBA equivalents of EXCEL functions are?
That is why I used InstrRev() -- to search starting from the end of the string