Macro to Enter formula into Cell

[nuver]

Hello
I am attempting to have a macro enter the formulas below into cells "B1" and "B2" of the active worksheet but I am receiving a compile error with the statement "Expected end of statement". Not sure what I am doing wrong but any help would be appreciated.

Range("b1").Formula = "=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)"

Range("b2").Formula = "=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)"

What I am attempting to do is to convert hundreds of inherited files that have been saved under the format Excel 5.0/95 to the standard Excel workbook format. All files are linked to external spreadsheets and I simply need the information not the formulas. I have attached a macro to my personal macro workbook that executes from a toolbar to value out each spreadsheet.

I may be way off base and if there is an easier solution please let me know. I beleive if I were to create a macro to insert a worksheet and copy the above mentioned formulas into the dsignated cells of the new sheet and then value the information out, I would have the current filename. My next step would be to save the file to my desktop as "delete.xls". I should them be able to save the file as the old filename,(referencing the values from the formulas), in the Excel Workbook format and overriide the older format. Last step of the macro would be to delete the added spreadsheet.

I think this should work if I could just get the code to enter the formulas above.

Thank for any help or direction
Ed

 

[acw]

Ed

Try

Range("b1").Formula = "=LEFT(CELL(""filename"",A1),FIND(""["",CELL(""filename"",A1),1)-1)"

Range("b2").Formula = "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1,SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-1)"

Tony

 

[smi123]

Use . value not .formula
Mine always work with .value when using cell references and not Row Column References.

Also, you may have to double up on your quotation marks where you need quotation marks within the formula. For example if testing for a blank you would need """" for the formula to work

Range("b1").value = "=LEFT(CELL("filename",A1),FIND(""["",CELL("filename",A1),1)-1)"

I do not understand the "Filename" part or what you have in A1, so trust this helps

 

[Nimrod]

Public Sub DEMO()

    ' Get Path
    Range("B1").Value = ThisWorkbook.Path
    
    ' Get File Name
    Range("B2").Value = ThisWorkbook.Name

End Sub