Prev: C++ Excel 2007 SDK
Next: Hyperlink events
From: ker_01 on 9 Feb 2010 14:46

I have a master data workbook, and will be pulling in data from source XL
files from various locations.

At the moment, I'm trying to create a function that will allow me to pass a
location (workbook to pull), and specify which sheet to grab from the source
workbook, and which sheet in the master data workbook to past that sheet into.

I'm having some trouble (error: does not support this property or method)
specifying the source sheet in the newly opened source workbook- can anyone
give me some pointers? (I replaced path/filename to shorten the code line
below in the test sub, but the path is valid and it does open the correct
file)

Thank you!!
Keith

Sub test
zz= PullAllRawData("C:\path\filename.xlsx",Sheet1,Sheet13)
end sub

Function PullAllRawData(MyFullFilePath As String, _
SourceSheet As Worksheet, _
DestSheet As Worksheet)

Dim i As Integer
Dim owb As Workbook 'original/main
Dim twb As Workbook 'temp/data file
Dim ows As Worksheet
Dim tws As Worksheet

DestSheet.Activate
Set owb = ActiveWorkbook
Set ows = ActiveWorkbook.ActiveSheet
'clear the destination sheet to make sure there isn't leftover old data
ows.Cells.Clear

Application.StatusBar = "Opening File " & MyFullFilePath

'Open source workbook
Set twb = Workbooks.Open(FileName:=MyFullFilePath)
twb.Activate
twb.SourceSheet.Activate '<<<< this is where it errors out
'grab the data
twb.SourceSheet.Cells.Select
Selection.Copy
ows.Activate
ows.Range("A1").Select
ActiveSheet.Paste

'Select/copy a single cell to avoid clipboard warnings
ActiveSheet.Range("A1").Copy

'close the workbook to get it out of the way
Application.DisplayAlerts = False 'just in case the clipboard trick doesn't
work
twb.Close SaveChanges:=False
Application.DisplayAlerts = True

End Function

From: John Bundy on 9 Feb 2010 16:26
Try setting the sheet name as a string and then call it like this

mysheet = "Sheet3"
Sheets(mysheet).Select

 | 
Pages: 1
Prev: C++ Excel 2007 SDK
Next: Hyperlink events