Re-post: Please Help!!! Link Problem 8(

  • Thread starter Thread starter Andy Sandford
  • Start date Start date
A

Andy Sandford

Hi,

I need to use data in my source workbook in another, destination, workbook -
however, I don't want the data to automatically update in the destination
workbook when data in the source workbook is changed. I thought I could link
to it, and then configure Excel to only update the info when I select it
manually.

When I copy the data, then paste it as a link, then go to EDIT-LINKS on the
toolbar, the manual tab isn't available.

Unfortunately, after trying for ages I finally found the following in the
Help file!

"Note You can't paste worksheet cells or a chart as a linked or embedded
object in a Microsoft Excel workbook. However, you can use the Copy Picture
command (hold down SHIFT and click the Edit menu) to create a link to cells
on this or another sheet or workbook."

Obviously, if I use the picture option I can't manipulate the data - only
stare at it for hours, helplessly!

Is there a way I can get round this - either by tackling it a different way
in Excel, or by the use of VBA.

Cheers

Andy
 
Andy,

Maybe this will help.

Copy and paste the following subroutine in a standard module in the VB
editor ( start up by Alt F11 and then from the menu bar Insert / Module )
This example copies a range (A1:A10) from Book2 to the range (B1:B10) from
Book1 and an individual cell (D1) from Book2 to the cell (E1) in Book1.
(I did change the cell reference on purpose to show that the ranges don't
need to be exactly the same in the 2 books).

You can run the macro whenever you want to update Book1 on the basis of the
figures contained in Book2


The routine assumes both files are "open" and that only Sheet1 of both files
are concerned.
You will have to adapt the routine for different sheetnames ( as well as for
the cellranges, the sheetnames for source (book2) and destination (book1)
can also be different), filenames and eventually for opening one of the
workbooks.

I hope however that it gives you an idea how to cope with your problem
further.

NB
1) The routine can be inserted in either of the two files.
2) The routine handles a fixed set of copy - paste (values only). It's not
very suited if your range of cells you have to update changes very often,
since that would mean that you have to edit the routine each time as well.


Succes.

Sub UpDateBook2ToBook1()

Workbooks("Book2").Worksheets("Sheet1").Range("A1:A10").Copy
Workbooks("Book1").Worksheets("Sheet1").Range("B1:B10").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Workbooks("Book2").Worksheets("Sheet1").Range("D1").Copy
Workbooks("Book1").Worksheets("Sheet1").Range("E1").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi

Thanks for the code, but I keep getting a syntax error in this section!

Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_:=False, Transpose:=False

Is it me??

Regards

Andy
 
Hi Andy
these two lines should be in one line (or at least the character '_'
should be at the end of the first line).
 
Frank, Andy,

Frank. Thanks for answering Andy allready. I didn't notice up to now.

Andy. Just to make sure : In VB you can break one statement in two or
more "lines" if you end each line with a space followed bij an underscore.
On the next line you than just continue with the statement.

(If you decide to put the whole statement on one line you will have to
remove that underscore).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi Frank

I tried both ways...

Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

AND

Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False,
Transpose:=False

....but I still get errors - If I try the second option, I get the error
"Expected Expression"

Thanks in Advance!!

Andy
 
Hi, me again!!

I now have the following as a single line of code...

Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

....still getting the Syntax Error 8o((

Andy
 
Andy,

Does the line in front of it ( ending with Paste Special) end with a space
and an underscore.
If not : add that space and underscore.

(While you are in the VB editor all your lines should be black; if a line is
red it contains an error (or isn't complete)

A green line is a line with comment and have no influence on the program.

(At least these colors are the default colors in the VBE;
you or someone else migth have changed them)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Andy,
As a small addtion to the previous note :

Everything from Workbooks("Book1").Worksheets .... up to Transpose := False
is one statement.
If typed on one line it should not contain any underscore ( _ ).
If type on mor lines , every line should be followed by that space and _ .

(This applies for both statements starting and ending with the words
mentioned)00.

Succes.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
YW Andy, thanks for the response (it's always nice to know that it works)
and succes with adapting it to fit your needs.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi again,

Is there any way to specify which worksheet the info is pasted into - I have
several worksheets in the target workbook that need to be populated with the
data - one at a time.

Can I modify the code to paste into the active worksheet only?

I currently run the macro from a check box on the worksheet.

Thanks

Andy
 
Andy,

You could by replacing (in the paste statement) Worksheets("Sheet1") by
ActiveSheet.

However this is only possible if all the cell references on the various
sheets are the same.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Worked a treat!

Is there any way I can test whether the source workbook is open, so I don't
get an error if it isn't?

Sorry to be a pain!!

Andy
 
Andy,

Following is a subroutine which call the function (FileOpenCheck).

You can add the "body" of the OpenFile subroutine in your macro

The function returns TRUE or FALSE on the basis of which the subroutine
decides whether the file has to be opened.


Sub OpenFile()
If Not FileOpenCheck("Book1") Then
Workbooks.Open "C:\Directory\TestFile.xls"
ActiveWorkbook.RunAutoMacros xlAutoOpen
End If
End Sub

Function FileOpenCheck(Filenaam) As Boolean
On Error Resume Next
A = Workbooks(Filenaam).Worksheets.Count
If Err.Number = 0 Then
FileOpenCheck = True
Else
FileOpenCheck = False
End If
On Error GoTo 0
End Function


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Andy,

Sorry, you can delete the statement : ActiveWorkbook.RunAutoMacros
xlAutoOpen
It probably doesn't any harm, but shouldn't be taken up as well since your
workbook most likely doesn't contain an AutoOpen macro (yet)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi AGAIN!!

The code you gave me works perfectly if the source workbook "Equipment Price
Comparison.xls" is already open, but runs very slowly, and crashes if it is
not...

- I get the error "Select Metod of Range Class Failed" in the final line.

I put this line in to return the cursor to the correct place. It works OK if
the source book is already open though.
- I guess that this is where the problem lies!!

FileOpenCheck(Filename) is saved in Module 2:

Function FileOpenCheck(Filename) As Boolean
On Error Resume Next
A = Workbooks(Filename).Worksheets.Count
If Err.Number = 0 Then
FileOpenCheck = True
Else
FileOpenCheck = False
End If
On Error GoTo 0
End Function


This is the main code as it is now (saved in Module 1):

Sub UpDatePriceComparisonToInstallationCost()
If Not FileOpenCheck("Equipment Price Comparison.xls") Then
Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\Equipment
Price Comparison.xls")
End If
'copy cells from Equipment Price Comparison.xls
Workbooks("Equipment Price Comparison.xls").Worksheets("System
Selection").Range("E12:E21").Copy
'paste cells to Installation Costing (new).xls
Workbooks("Installation Costing
(new).xls").ActiveSheet.Range("C97").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
'copy cells from Equipment Price Comparison.xls
Workbooks("Equipment Price Comparison.xls").Worksheets("System
Selection").Range("F12:F21").Copy
'paste cells to Installation Costing (new).xls
Workbooks("Installation Costing
(new).xls").ActiveSheet.Range("E97").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
'copy cells from Equipment Price Comparison.xls
Workbooks("Equipment Price Comparison.xls").Worksheets("System
Selection").Range("G12:G21").Copy
'paste cells to Installation Costing (new).xls
Workbooks("Installation Costing
(new).xls").ActiveSheet.Range("I97").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
'copy cells from Equipment Price Comparison.xls
Workbooks("Equipment Price Comparison.xls").Worksheets("System
Selection").Range("M12:M21").Copy
'paste cells to Installation Costing (new).xls
Workbooks("Installation Costing
(new).xls").ActiveSheet.Range("K97").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
'select cell in Installation Costing (new).xls
Workbooks("Installation Costing
(new).xls").ActiveSheet.Range("E16").Select
End Sub


HELP 8o)

Andy (VERY amateur VBA programmer!)
 
Andy,

The problem comes from opening the file if it wasn't already open.
By opening a file it becomes the active file as well and that - up to now -
wasn't changed.
The Select in your last line wasn't possible because you first have to
activate the file and only then you can select a cell in that file.

To solve it replace the IF .. END IF construct at the beginning of module 1
with this construct.
The file - if not allready open - is opened and immediatly thereafter
"Installation Costing(new).xls" is made the active file again.

If Not FileOpenCheck("Equipment Price Comparison.xls") Then
Workbooks.Open ("C:\Documents and
Settings\Administrator\Desktop\Equipment Price Comparison.xls")
Workbooks("Installation Costing(new).xls").Activate
End If

While I was busy with your macro, I also noticed that after your last
selection, the statusbar was mentioning that your are still in "copy mode"
(meaning you still have a message to select an area and paste, the area you
copied the last time in the Equipment Price file also is still surrounded by
the dotted line)
To suppress that copy mode add the statement Application.CutCopyMode =
False after your last statement.
(The one in which you select "E16" of Installation Costing(new).xls).

Succes.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Andy Sandford said:
Hi AGAIN!!

The code you gave me works perfectly if the source workbook "Equipment Price
Comparison.xls" is already open, but runs very slowly, and crashes if it is
not...

- I get the error "Select Metod of Range Class Failed" in the final line.

I put this line in to return the cursor to the correct place. It works OK if
the source book is already open though.
- I guess that this is where the problem lies!!

FileOpenCheck(Filename) is saved in Module 2:

Function FileOpenCheck(Filename) As Boolean
On Error Resume Next
A = Workbooks(Filename).Worksheets.Count
If Err.Number = 0 Then
FileOpenCheck = True
Else
FileOpenCheck = False
End If
On Error GoTo 0
End Function


This is the main code as it is now (saved in Module 1):

Sub UpDatePriceComparisonToInstallationCost()
If Not FileOpenCheck("Equipment Price Comparison.xls") Then
Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\Equipment
Price Comparison.xls")
End If
'copy cells from Equipment Price Comparison.xls
Workbooks("Equipment Price Comparison.xls").Worksheets("System
Selection").Range("E12:E21").Copy
'paste cells to Installation Costing (new).xls
Workbooks("Installation Costing
(new).xls").ActiveSheet.Range("C97").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
'copy cells from Equipment Price Comparison.xls
Workbooks("Equipment Price Comparison.xls").Worksheets("System
Selection").Range("F12:F21").Copy
'paste cells to Installation Costing (new).xls
Workbooks("Installation Costing
(new).xls").ActiveSheet.Range("E97").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
'copy cells from Equipment Price Comparison.xls
Workbooks("Equipment Price Comparison.xls").Worksheets("System
Selection").Range("G12:G21").Copy
'paste cells to Installation Costing (new).xls
Workbooks("Installation Costing
(new).xls").ActiveSheet.Range("I97").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
'copy cells from Equipment Price Comparison.xls
Workbooks("Equipment Price Comparison.xls").Worksheets("System
Selection").Range("M12:M21").Copy
'paste cells to Installation Costing (new).xls
Workbooks("Installation Costing
(new).xls").ActiveSheet.Range("K97").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
'select cell in Installation Costing (new).xls
Workbooks("Installation Costing
(new).xls").ActiveSheet.Range("E16").Select
End Sub


HELP 8o)

Andy (VERY amateur VBA programmer!)
Click to expand...
 
Back
Top