Open a file and manipluate cells

[ckaren000]

Hi people,
i am writing a simple macro in excel but i don't know what i am doin wrong.
here is the thing i wanted to do,
open a file (temp.xls) and manipluate some cell data. i created a button in a file and inputed following code
Private Sub CBopen_Click()
Workbooks.Open Filename:="C:\temp.xls"
Workbooks("temp.xls&quot.Activate
Worksheets("sheet1&quot.Activate
Range("E10&quot.Select
ActiveCell.FormulaR1C1 = "=RC[-3]-5"
Range("F10&quot.Select
ActiveCell.FormulaR1C1 = "=RC[-3]*100"
Range("E10:F10&quot.Select
End Sub
but it gives error saying "select method of Range class failed" ( its giving error at "Range("E10&quot.Select &quot.
i think i am not able to point excel to right workbook.
thanks in advance for the help.
regards
karen

 

[Bowers74]

You problem was that the Sheet1, where the ranges E10:F10 were to be selected was Active, but it wasn't Selected. You don't need to do all of that anyway:

Here is a better way to do what you wanted:

Private Sub CBopen_Click()
Workbooks.Open Filename:="C:\temp.xls"
Workbooks("temp.xls").Sheet1.[E10].FormulaR1C1 = "=RC[-3]-5"
Workbooks("temp.xls").Sheet1.[F10].FormulaR1C1 = "=RC[-3]*100"
With Workbooks("temp.xls")
    .Select
    .Sheet1.select
End With
End Sub

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

I also noticed that this was your first post in Tek-Tips.

Welcome!!



I hope that your first experience was a good one!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ckaren000]

thanks Mike. but i tried it, still its not working. it gives this error 'object dosent' support this property or method' it gave error at Workbooks("temp.xls&quot.Sheet1.[E10].FormulaR1C1 = "=RC[-3]-5". i rightclicked on it and clicked the definition then it gave a message saying
' cannot jump to Workbooks because it is hidden' is it because of this code is not working. i don't know how to unhide it.

but when i use the code which i used earlier(the one on top) it dosent give error at workbook.
thanks
Karen

 

[Bowers74]

I'm sorry about that, I don't know what I was thinking.

Private Sub CBopen2_Click()
Workbooks.Open FileName:="C:\temp.xls"
Workbooks("temp.xls").Sheets("Sheet1").Range("E10").FormulaR1C1 = "=RC[-3]-5"
Workbooks("temp.xls").Sheets("Sheet1").Range("F10").FormulaR1C1 = "=RC[-3]*100"
Workbooks("temp.xls").Activate
End Sub

I hope this helps!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ckaren000]

thanks Mike. it worked

 

[Bowers74]

ckaren000,

I'm glad it worked, and I'm glad I could help (even though it took two tries )

I know that you're new to the forums, so I figured that I would inform you that the traditional way to thank someone in the forums is to award them a star if you found their solution helpful.

To do this click the link Mark this post as a helpful/expert post! at the bottom left hand side of the post that helped you.

You don't have to be the one who asked the question to award a star either, if you do a keyword search in the formus and find a post that is helpful to you, you can award that member a star for his solution.

Thanks! And once again, WELCOME!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!