Still can't connect access button to excell file.

08-06-2003, 12:28 PM

bob123

Centurion

Join Date: Jul 2003

Posts: 95

Still can't connect access button to excell file.

Code:

Private Sub Command0_Click()
Dim objXl As Object

Set objXl = GetObject("C:\Documents and Settings\fac_staff\Desktop\Copy of test2\xl.xls")

'MsgBox objXl.Worksheets.Count
objXl.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
objXl.UserControl = True

Exit_Command0_Click:
' ChDir "C:\Documents and Settings\fac_staff\Desktop"
'Workbooks.Open Filename:= _
' "C:\Documents and Settings\fac_staff\Desktop\Copy of test2.xls"
Exit Sub
End Sub

Quote:

--------------------------------------------------------------------------------

How do I get the Access button
1.Access this excel file (1b. In the backgroun)
2.Run a macro listed in this file
3.Return a value from a cell in the excel file.

Sorry about having you do practically the whole thing for me but this is the first time I'm using interfile and interprogram coding. Thank you in advance!

The responce I received was:

Personally i have not seen the dim object as file. off the top of my head...

i use dim xlapp as object
set xlapp = "excel.application"
and then open the file i want.(as per the helpfiles in access 97 - search help for xlapp.
i.e. xlapp.openworkbook("path")

then i'd do the xlapp.visible = false to run in the background

not sure how to call a macro... i usually import the macro code into the access button and add in xlapp. before each line of code.

you'll need to reference microsoft excel version.. to do this.

then
dim myvar as string
myvar = xlapp.range("a1").value

As per instructions I replaced some of my code with
...

Code:

Dim oApp As Object
Set oApp = "excel.application"
    oApp.openworkbook("C:\Documents and Settings\fac_staff\Desktop\test2", "Excel.Application")

...
However now vb editor expects me to balance the second line with a = or a . command. I tried looking up xlapp but it wasn't amongst my access help files. Can anyone help me?

08-07-2003, 12:36 AM

Sliekas_Satana Sliekas_Satana is offline

Newcomer

Join Date: Jun 2003

Location: Lithuania

Posts: 17

I think, you need this

:

Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objSheet As Excel.Worksheet

Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open("c:\blabla.xls") 'Open you document

objExcel.Visible = True ' excel visible (not required)
objExcel.Application.Run "Macro1" 'run your macro

Set objSheet = objWorkBook.Worksheets(1)

MsgBox objSheet.Range("B4").Value 'return value from cell where is result of macro

08-07-2003, 07:35 AM

davers55

Newcomer

Join Date: Aug 2003

Posts: 16

I was just reading this and I think that's a nifty bit of code. Now, is it possible to pass a variable (say a recordset) from access to excel using this code? I'd like to pass a recordset so I can create a pivot table. I have all the code on the excel end, but the query for the correct data relies on function in access.

Thanks,

Dave

Quote:
Originally Posted by Sliekas_Satana
I think, you need this :

Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objSheet As Excel.Worksheet

Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open("c:\blabla.xls") 'Open you document

objExcel.Visible = True ' excel visible (not required)
objExcel.Application.Run "Macro1" 'run your macro

Set objSheet = objWorkBook.Worksheets(1)

MsgBox objSheet.Range("B4").Value 'return value from cell where is result of macro

08-07-2003, 09:27 AM

bob123

Centurion

Join Date: Jul 2003

Posts: 95

So close I can taste it.

Thank you Sliekas_Satana. I've finally gotten a sub that has no precompile errors. However after I've attached this xact code to a button 02 I still get a compile error (User defined type not defined.) Can anyone tell me what is wrong?

Code:

Private Sub Command2_Click()
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objSheet As Excel.Worksheet

Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open("C:\Documents and Settings\fac_staff\Desktop\test2.xls") 'Open you document
objExcel.Visible = True
End Sub

08-07-2003, 09:42 AM

davers55

Newcomer

Join Date: Aug 2003

Posts: 16

Have you set the references to the Excel object library? You didn't need this before because you declared your object variables as "objects" now that you declare as Excel objects you need to set the references. Go to Tools-References and select MS Excel 9.0 object library.

Dave

Quote:
Originally Posted by bob123
Thank you Sliekas_Satana. I've finally gotten a sub that has no precompile errors. However after I've attached this xact code to a button 02 I still get a compile error (User defined type not defined.) Can anyone tell me what is wrong?

Code:

Private Sub Command2_Click() Dim objExcel As Excel.Application Dim objWorkBook As Excel.Workbook Dim objSheet As Excel.Worksheet Set objExcel = CreateObject("Excel.Application") Set objWorkBook = objExcel.Workbooks.Open("C:\Documents and Settings\fac_staff\Desktop\test2.xls") 'Open you document objExcel.Visible = True End Sub

08-07-2003, 10:23 AM

bob123

Centurion

Join Date: Jul 2003

Posts: 95

Thank

Of course, my problem was simply that I forgot to select an option in a hidden 500+ option menu. How could I have not known that.

Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!Thank you!

08-08-2003, 12:11 AM

Sliekas_Satana Sliekas_Satana is offline

Newcomer

Join Date: Jun 2003

Location: Lithuania

Posts: 17

devers55,
i dot't know it will be nifty bit of code, but
it works

I'm beginner in VB4app, so i don't know
subtlety of VB in office

So, there is this code:

Code:

Dim objExcel As Excel.Application
    Dim objWorkBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet

    Set objExcel = CreateObject("Excel.Application")
    Set objWorkBook = objExcel.Workbooks.Open("c:\blabla.xls")
    objExcel.Visible = True ' excel visible (not required)

    Set objSheet = objWorkBook.Worksheets(1)
    
    objSheet.Range("A1").Select
    Row = 1
    Set kodai = dbsCur.OpenRecordset("SELECT buh_pavad, buh_kodas FROM buh_kodai")
    
    Do While Not kodai.EOF
        objExcel.ActiveCell(Row, 1) = kodai.Fields("buh_pavad").Value
        objExcel.ActiveCell(Row, 2) = kodai.Fields("buh_kodas").Value
        Row = Row + 1
        kodai.MoveNext
    Loop
    kodai.Close

Quote:
Originally Posted by davers55
I was just reading this and I think that's a nifty bit of code. Now, is it possible to pass a variable (say a recordset) from access to excel using this code? I'd like to pass a recordset so I can create a pivot table. I have all the code on the excel end, but the query for the correct data relies on function in access.

Thanks,

Dave

08-08-2003, 06:11 AM

trental

Junior Contributor

Join Date: Oct 2002

Location: Uk

Posts: 279

yeah sorry i forgot the creat object bit didn't I.

did the help files assist? glad you got there in the end

__________________
Kind Regards
Trental

http://www.sudonim.com