view one worksheet from within another workbook

[Wendy]

Hi

Is there anyway I can view the contents of one worksheet of one workbook in
a seperate worksheet in a different workbook.

Thanks

Wendy

 

[michael.beckinsale]

Hi Wendy,

I suspect that this relates to your earlier post and that what you
really want to do is to prompt the user to select a row / record to
import from a 'source' workbook into the 'active' workbook.

The only way l can think of achieving this is to use the 'Window'
'Arrange' options, perhaps say placing the 'active' workbook on the
left and the 'source' workbook on the right with the window sized
smaller. This would achieve the visual impact required and allow the
row / record selection in the 'source' workbook.

Whilst writing l am thinking perhaps using the 'Window' 'Arrange'
'Cascade' option and resizing the 'source' window smaller may be a
visually better option. The 'source' window could open in the middle
of the 'active' window following a prompt to select the row / record.

If l am wrong in my original assumption and you only want to take a
'snapshot' of a particular sheet or sheet range look at the 'camera'
function. This will not provide any interactivity.

HTH

Regards

Michael

 

[paul.robinson]

Hi
From earlier post you wanted to view the data in a workbook called
Post.xls.
The code below assumes post is in D:\Temp and has a worksheet on it
called Data. Change these as required. Attach the
DisplayIt macro to a button in your other workbook. Put the dispayIt
macro and the function below into a code module.
The Post.xls can be open or closed.
Is this what you want to see?

Sub DisplayIt()
Dim PostWorkbook As Workbook
If IsFileOpen("Post.xls") Then
Workbooks("Post").Worksheets("Data").Activate
Else
Set PostWorkbook = Workbooks.Open(filename:="D:\Temp\Post.xls")
ActiveWorkbook.Worksheets("Data").Activate
End If
ThisWorkbook.Activate
Windows.CompareSideBySideWith "post"
End Sub

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
Err.Clear
On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum
' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False
' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True
' Another error occurred.
Case Else
IsFileOpen = False
End Select
End Function


regards
Paul

 

[Michael Beckinsale]

Hi Wendy,

I suspect that this relates to your earlier post and that what you
really want to do is to prompt the user to select a row / record to
import from a 'source' workbook into the 'active' workbook.

The only way l can think of achieving this is to use the 'Window'
'Arrange' options, perhaps say placing the 'active' workbook on the
left and the 'source' workbook on the right with the window sized
smaller. This would achieve the visual impact required and allow the
row / record selection in the 'source' workbook.

Whilst writing l am thinking perhaps using the 'Window' 'Arrange'
'Cascade' option and resizing the 'source' window smaller may be a
visually better option. The 'source' window could open in the middle
of the 'active' window following a prompt to select the row / record.

If l am wrong in my original assumption and you only want to take a
'snapshot' of a particular sheet or sheet range look at the 'camera'
function. This will not provide any interactivity.

HTH

Regards

Michael

 

[michael.beckinsale]

Hi Paul,

It seems we were both thinking along the same lines!

Regards

Michael

 

[Wendy]

Hi

Thanks for this, I'm thinking more along the lines of showing the data in
text box, list box or listview rather than displaying the whole
spreadsheet. I only need to see data in columns A,B & C.

Wendy

Hi
From earlier post you wanted to view the data in a workbook called
Post.xls.
The code below assumes post is in D:\Temp and has a worksheet on it
called Data. Change these as required. Attach the
DisplayIt macro to a button in your other workbook. Put the dispayIt
macro and the function below into a code module.
The Post.xls can be open or closed.
Is this what you want to see?

Sub DisplayIt()
Dim PostWorkbook As Workbook
If IsFileOpen("Post.xls") Then
Workbooks("Post").Worksheets("Data").Activate
Else
Set PostWorkbook = Workbooks.Open(filename:="D:\Temp\Post.xls")
ActiveWorkbook.Worksheets("Data").Activate
End If
ThisWorkbook.Activate
Windows.CompareSideBySideWith "post"
End Sub

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
Err.Clear
On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum
' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False
' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True
' Another error occurred.
Case Else
IsFileOpen = False
End Select
End Function


regards
Paul

 

[michael.beckinsale]

Hi Wendy,

If you want to use the control box 'ListBox' form then simply apply
something like this to its properties:

ListFillRange: '[Post]Data'!$A$1:$C$3
ColumnCount: 3

This will display the contents of all 3 columns in a tabular format.

Apply other properties / code as required.

Regards

Michael

 

[paul.robinson]

Hi Wendy
Try to say what it is you really want, as I suggested in your earlier
posts.
regards
Paul