Function calling

[accessjpm]

Function Calling

I have a form w/ command button. The button opens excel and then reads data from excel and inputs it into a new record. In my call the the get/set record function it stops working. I think this has to do with passing the variable "wb" of object type. I need to pass wb because it is set as the active workbook and in the get/set function I use it to set "ws" as the active worksheet to read data from.

Any ideas? or should I try to explain more clearly.

Matt

 

[accessjpm]

Urgent, Deliverable on friday

If anyone can offer some help on this right away that would be great. I have to have this whole DB done by friday. Thanks

 

[mohammadagul]

how are u using this module....i mean how are u calling the module.

 

[accessjpm]

The code is below, it bombs out when I call "CreateNwRec (wb)" saying something about object does not support property or method.


Function Opnxcel()
On Error GoTo Err_Opnxcel_Click

Dim oApp As Object
Dim wb As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
Set wb = oApp.Workbooks.Open("C:\Ginzberg\derivupdt.xls")

CreateNwRec (wb)


Exit_Opnxcel_Click:
Exit Function

Err_Opnxcel_Click:
MsgBox Err.Description
Resume Exit_Opnxcel_Click

End Function
Function CreateNwRec(wb)
Dim i As Integer
Dim clet As String
Dim ws As Object

Set ws = wb.ActiveSheet
i = 2
clet = "A"

While IsBlank(i, clet, wb) = False
DoCmd.GoToRecord acDataForm, , acNewRec
Getrec (wb)
i = i + 1
Wend

End Function
Function Getrec(wb)
Dim ws As Object
Dim cpname, instyp, doctyp, colat, csachk, dwngrd, nav, csaapr, offmem, imagmt, incfmt, nego, cdgcom As String
Dim sentdt As Date

Set ws = wb.ActiveSheet

cpname = ws.Range("A2").Text


Set ws = wb.ActiveSheet


End Function

 

[mohammadagul]

with me it works perfectly... i only add the following Mircrosoft Excel 9.0 object library..

See if you have the reference checked or not.

 

[accessjpm]

Sorry...

Reference checked? Not exactly sure what you mean. And which access library did you mean?

Also, I am working on the ancient Access 97, which I think may have something to do with it. Let me know if you have any further ideas. Thank you

 

[mohammadagul]

I am usin Access 2.0

About Reference do the following

Tools
Macros ..... Visual Basic
Tools
Reference
Microsoft Excel 9.0 object library

 

[accessjpm]

I went to reference, and included the Excel 8.0 Object Library, there was no 9.0. It still does not seem to work.

The librarys I am currently including are

Visual Basic for Apps
MS Access 8.0 Obj Lib
MS DAO 3.51 Obj Lib
MS Excel 8.0 Obj Lib

 

[mohammadagul]

is it possible yiou can send me an email of of your DB.. I will look into it and let you know..
mohammadagul@hotmail.com