External Workbook Help!

[ianripping]

I have this code:-

Sub Macro3()
Dim val
Dim rng1
val = Range("R1").Value
Set rng1 = "H:\[Book1.xls]Sheet1".Range(val)
rng1.Select
Selection.Copy
Sheets("Mar").Select
Range("R4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:
_
False, Transpose:=False
End Sub

At cuttent it doesn't work because of line :-
Set rng1 = "H:\[Book1.xls]Sheet1".Range(val)

The value I want to grab is in H:\Book1\Sheet1 - Cells B1:B5
In R1, I have the range B1:B5 as a value.

Can anyone help

 

[Frank Kabel]

Hi
you have to open the other workbook to make this work.

 

[ianripping]

OK no problem. thanks

 

[Bob Phillips]

and you set a range object like so

Set rng1 = Workbooks("Book1.xls").Worksheet("Sheet1").Range(Val)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi
you have to open the other workbook to make this work.

--
Regards
Frank Kabel
Frankfurt, Germany

I have this code:-

Sub Macro3()
Dim val
Dim rng1
val = Range("R1").Value
Set rng1 = "H:\[Book1.xls]Sheet1".Range(val)
rng1.Select
Selection.Copy
Sheets("Mar").Select
Range("R4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
End Sub

At cuttent it doesn't work because of line :-
Set rng1 = "H:\[Book1.xls]Sheet1".Range(val)

The value I want to grab is in H:\Book1\Sheet1 - Cells B1:B5
In R1, I have the range B1:B5 as a value.

Can anyone help?

 

[ianripping]

Ok I have come up with a solution.....

Sub Macro9()
Dim val
Dim rng As Range
Range("J2").Select
ActiveCell.FormulaR1C1 = "='[Amend.xls]Referal Types'!R2C9"
Range("J3").Select
ActiveCell.FormulaR1C1 = "='[Amend.xls]Referal Types'!R[-1]C[-9]"
Range("K3").Select
ActiveCell.FormulaR1C1 = _
"=""j4:j""&(RIGHT(R[-1]C[-1],((LEN(R[-1]C[-1]))-4)))"
val = ActiveSheet.Range("k3").Value
Set rng = Range(val)
Range("J3").Select
Selection.Copy
rng.Select
ActiveSheet.Paste
End Sub

This gets the range to be copied, then sets out on the active workshee
where to paste a formula down a sheet.

Works great

 

[Toby Erkson]

I needed to do the same but, being a beginner, found your code confusing.
Hopefully this will help others when they do a search. If one needs to
further use the created workbook then be sure to prefix (as exampled below)
commands to that workbook with "oXL.Application.".
-----------------------------------
'Simple example of getting data from a closed, remote Excel workbook and
copying
'it into the currently opened workbook

Dim oXL As Object
Dim DataDir as String, DataFile as String, DataPath as String

DataDir = "\\server\foo\"
DataFile = "RawData.xls"
DataPath = DataDir & DataFile

'Create an Excel workbook and Open a specific workbook in it
Set oXL = CreateObject("Excel.Application").Workbooks.Open(DataPath)

'That's it! The rest is an example showing how to manipulate the object

oXL.Application.Visible = True 'Makes the workbook visible on-screen (not
necessary)
oXL.Application.Range("A1").Select 'my created workbook only has one sheet
oXL.Application.Selection.Copy 'Copy the contents of the cell in the created
workbook
'Paste data in current workbook in a sheet labeled "Raw Data"
Sheets("Raw Data").Select
ActiveSheet.Paste 'Now add new data

'The last stuff to do when you're done using the object
oXL.Application.Quit 'Exits the Excel application object that was created...
Set oXL = Nothing '...and frees up the memory it used.