Opening Excel File from value in active cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Launchnet - 07 Mar 2008 03:56 GMT

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.

Signature

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.

Reply to this Message

ytayta555 - 07 Mar 2008 05:26 GMT

> 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

Reply to this Message

Launchnet - 17 Mar 2008 18:40 GMT

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.
>
[quoted text clipped - 22 lines]
> 'My Macro already has a do loop that works and it knows when
>to stop.

Signature

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.

Reply to this Message