My last attempt

[Markus]

Here is my function. I need to import date from a closed
books without opening the closed books. I'm getting
desperate so if anyone wants to reply with email my
address is (e-mail address removed) I can send the
files if anyone wants to get a clear picture of what i've
done so far. I will make sure this is my last post on this
subject.

This function is set specifically to do what I need to do.
Except when I call the function I get data type mismatch
on the set wba line. I do not want to use "workbooks.open"
Is there a procedure or anything that I can ad to allow
this function to pull data from closed workbooks?

Public Function WriteValues(TheCase As Integer)

Dim wba As Workbook

Dim ls_Rangestringa As String

Dim ll_Rownumbera As Long

ll_Rownumbera = 1 + (TheCase * 40)

ls_Rangestringa = "D" + CStr(ll_Rownumbera)

Set wba = ("H:\My Documents\WorkInProgress\QARP2004
\ProgramReports\PG1Rep2004")


With ThisWorkbook.Worksheets("SR")
..Range("A8").Value = wba.Worksheets
("EvaluationCalculator").Range(ls_Rangestringa).Value

End With
End Function

 

[Bob Phillips]

Why must you not open the workbook? It is far simpler to turn screenupdating
off, open the workbook, copy the data, and then close it - no-one is any the
wiser.

--

HTH

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

 

[Tom Ogilvy]

Workbook is a reference to an open workbook. You are setting it to a
string. Thus the type mismatch. There is no provision in VBA to access
information in an open workbook. If you want to use this as a worksheet
function, then your options are extremely limited. I believe using ADO
would be about the only one (if you don't want to open the workbook).

If you are willing to change your design a bit, you could create custom
document property that refers to the cell you want to get information from
and possibly grab the information from the custom document property -
however, you seem to want to work with a changing cell, so this probably
won't work either (also I haven't tested it with a UDF).

 

[Markus]

Because the function in the end will refernce to open
about 10 workbooks. I was afraid that opening all these
workbooks would be time consuming, eat up memory. I tried
it just using 2 workbooks and it seemed to slow down a bit
even with screen updating off.

 

[Bob Phillips]

Okay,

it is either that or use ADO. If you know ADO it is quite simple, if not, do
you want to take the time to learn it, or get a working version up as
quickly as possible?

--

HTH

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

 

[Markus]

I'd actually like to do both. I'm always about trying to
get better with this. I'm currently trying to use the open
method in my file though. It works so...I'm hoping that If
I set the other workbook open actions after each workbook
close action then it might not be too bad. Opena -Copy-
close-openb - copy - close- openc -copy-close etc. Would
this make a difference? Where can I go to learn and
understand more about ADO?

 

[Bob Phillips]

Here is some code to read a worksheet

Public Sub Query()
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sFile As String

sFile = "C:\myTest\volker1.xls"

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0;"

' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1

' Check to make sure we received data.
If Not oRS.EOF Then
Worksheets("Sheet1").Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub

--

HTH

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

 

[Mika]

Hi Markus,

You want that the Set statement creates a reference to, in your case,
a workbook. However, what you are doing is actually referencing a
string (it doesn´t matter that the string is the full path of a
workbook), therefore, you get the error message.

How to reference a workbook and not an string ? Look at John
Walkenbach's site:

http://j-walk.com/ss/excel/tips/tip82.htm

Mika