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\CompanyDi
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,
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\CompanyDi
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,
Martin Liss
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.
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:
If the file name is coming from a filesystemobject, there are other ways of parsing the filename.
String result:
mid(ret,instrrev(ret,".")-8,8)CDate(mid(ret,instrrev(ret,".")-8,8))If the file name is coming from a filesystemobject, there are other ways of parsing the filename.
ASKER
When I try InvDate = Right(Left(Ret, Find(""."", Ret, 20) - 1), 8)
I get a syntax error and the entire line is highlighted.
I get a syntax error and the entire line is highlighted.
ASKER
Aikimark, there are two periods in the Ret filename which is why I used the Find and started at 20.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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