Opening Excel File from value in active cell

[Launchnet]

I do need some help . . . please. Hope someone can help.

'HERE IS WHAT I NEED AS PART OF MY MACRO . . . . . . . I HAVE AN OPEN
WORKBOOK.
'The name of the active workbook is PacificWindsRentRoll.
xls
'The macro selects . . .
Range("AD100").Select
'In cell AD100, the activecell, is a numeric value . . .
LET'S USE 10 AS OUR EXAMPLE
'I need to open a workbook from folder MyExcel and the
file name is 10.xls
'After the file has been opened, the program needs to re-
activate PacificWindsRentRoll.xls
'Next, the range ("ab100:ao100") needs to be selected and
copied
'The file ( 10.xls ) that we just opened needs to be re-
activated.
'The copied range is then pasted into cell A2 of 10.xls.
'This file 10.xls is then saved and closed.

'The next time this is run, a different file name would be
opened, as the value in cell AD100
'changes.
'My Macro already has a do loop that works and it knows when
to stop.

 

[ytayta555]

I do need some help . . . please.  Hope someone can help.

HI ! Try this

Range("AD100").Select
If Range("AD100") = 10 Then Workbooks.Open ("D:\MyExcel\10.xls ")
' [the path is for example[
Windows("PacificWindsRentRoll.xls ").Activate
Range("AB100:AO100").Select
Selection.Copy
Windows("10.xls").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks("10.xls").Close SaveChanges:=True

{if you want to paste only values you use ,, xlPasteValues"
if not ,you are formula there
only ,,Paste,,without ,,:=xlPasteValues"}

'The next time this is run, a different file name would be
opened, as the value in cell AD100
'changes.
'My Macro already has a do loop that works and it knows when
to stop

I'm shure this is not the full resolve for your problem ,
if next time if value of range AD100 is different the
macro must open another workbook , then there
must use an else , elseif ...something like that ,
maybe somebody else shall help you ;

.
Respectfully

 

[Launchnet via OfficeKB.com]

Thanks for your suggestion

Here is what I used and it works great

Sub WorkWithWorkbook()
Dim sFname As String
Dim sPath As String
Dim wsA As Worksheet

sPath = "c:\MyExcel\"

Set wsA = ThisWorkbook.ActiveSheet

sFname = wsA.Range("ad100").Value & ".xls"

Workbooks.Open sPath & sFname

wsA.Range("ab100:ao100").Copy Destination:=Range("a2")
ActiveWorkbook.Close SaveChanges:=True

Set wsA = Nothing
End Sub

I do need some help . . . please. Hope someone can help.

'HERE IS WHAT I NEED AS PART OF MY MACRO . . . . . . . I HAVE AN OPEN
WORKBOOK.
'The name of the active workbook is PacificWindsRentRoll.
xls
'The macro selects . . .
Range("AD100").Select
'In cell AD100, the activecell, is a numeric value . . .
LET'S USE 10 AS OUR EXAMPLE
'I need to open a workbook from folder MyExcel and the
file name is 10.xls
'After the file has been opened, the program needs to re-
activate PacificWindsRentRoll.xls
'Next, the range ("ab100:ao100") needs to be selected and
copied
'The file ( 10.xls ) that we just opened needs to be re-
activated.
'The copied range is then pasted into cell A2 of 10.xls.
'This file 10.xls is then saved and closed.

'The next time this is run, a different file name would be
opened, as the value in cell AD100
'changes.
'My Macro already has a do loop that works and it knows when
to stop.

--
Please take a look at www.openoursite.com Click on: "Keywords" and then
Click on "Matt's Story" and if you are a man, you should be very happy that
you read my story. God Bless for everyones help.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200803/1