Need Help Manipulating Excel 2000 from Access 2000 using VBA 
Author Message
 Need Help Manipulating Excel 2000 from Access 2000 using VBA

I'm having trouble manipulating data in Excel 2000 from Access 2000
using VBA.

The code I have takes data from a table and throws it into a
spreadsheet.

A reference is already set for Excel.

when I try something like MsgBox x.ActiveSheet.Name, I get the following
error message:

       "Subscript out of range"

Just above the code I have the following code:

Dim x as Workbook
Set x = Workbooks("test.xls")
x.Activate

What else do I need to do? or What am I doing incorrectly?

Thnaks.

*** Sent via Developersdex http://www.***.com/ ***
Don't just participate in USENET...get rewarded for it!



Sun, 21 Nov 2004 00:36:14 GMT
 Need Help Manipulating Excel 2000 from Access 2000 using VBA

Here is a procedure I use to get a reference to an excel object and open an
existing workbook. This code will start with version 10 of excel and work
back through the versions trying to get a valid excel object.  Once it does
it opens the workbook DOCTemplate.xls

xlApp is defined in the declarations section of my form as type object.

Note I'm using late  binding because I don't know which version of excel I
will be opening on different client machines.

Sub SetExcelObject()
    'Checking this checkbox will open an excell application object so the
    'footage report can be printed on it.
    Dim i As Integer

    On Error GoTo TryPreviousVersion
    i = 10
    If xlApp Is Nothing Then
        'MsgBox "i is " & i
tryagain:
        Set xlApp = CreateObject("excel.application." & i)
        xlApp.workbooks.open ("Test.xls") 'you will want to include the full
path here
        xlApp.application.Visible = True 'Will make the excel window
visible. If you don't want it visible don't do this
    End If
    On Error GoTo 0
    Exit Sub
TryPreviousVersion:
    'MsgBox Err & " " & Err.Description, vbExclamation + vbOKOnly
    If Err = 429 Then
        If i = 4 Then
            MsgBox "Error you do not have a version of excel high enough to
run this report.", vbExclamation + vbOKOnly, "ERROR ACTIVATING EXCEL"
            Exit Sub
        End If
        i = i - 1
        Err.Clear
        Set xlApp = Nothing
        Resume tryagain
    Else
        MsgBox Err & " " & Err.Description, vbExclamation + vbOKOnly
    End If
End Sub


Quote:
> I'm having trouble manipulating data in Excel 2000 from Access 2000
> using VBA.

> The code I have takes data from a table and throws it into a
> spreadsheet.

> A reference is already set for Excel.

> when I try something like MsgBox x.ActiveSheet.Name, I get the following
> error message:

>        "Subscript out of range"

> Just above the code I have the following code:

> Dim x as Workbook
> Set x = Workbooks("test.xls")
> x.Activate

> What else do I need to do? or What am I doing incorrectly?

> Thnaks.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Sun, 21 Nov 2004 04:38:03 GMT
 Need Help Manipulating Excel 2000 from Access 2000 using VBA

Thanks!

I've been able to figure out how to create a sheet in an Excel
application and export data to it.

However, the problem I am still facing is manipulating various objects
in Excel from Access.

For example, how would I do something simple like setting the value of
cell A1 to "x" on the sheet that I created, which for this example will
be kept as Sheet1?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 21 Nov 2004 06:14:21 GMT
 Need Help Manipulating Excel 2000 from Access 2000 using VBA
Hi Bruce, try this with Sheet1 selected:
oExcel.Range("A$1").Value = "X"

Hope that helps.

Pan


Quote:

> Thanks!

> I've been able to figure out how to create a sheet in an Excel
> application and export data to it.

> However, the problem I am still facing is manipulating various objects
> in Excel from Access.

> For example, how would I do something simple like setting the value of
> cell A1 to "x" on the sheet that I created, which for this example will
> be kept as Sheet1?

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Sun, 21 Nov 2004 22:58:17 GMT
 Need Help Manipulating Excel 2000 from Access 2000 using VBA
You have to make sure you use a full reference.

I refer to items like this

xlApp.workbooks("test.xls").worksheets("Sheet1").range("A1") = "X"

you can also use a with structure like

with xlApp.workbooks("text.xls").worksheets("Sheet1")

    .name = "MySheet"
    .range("A1") = "X"

end with

Once you have your reference to excel you should refer to the excel
programming groups such as

microsoft.public.excel.programming

for further tips on excel syntax.

Some things don't work outside of excel. Specifically

application.displayalerts = false

doesn't work because it is only a valid statement directly within a native
excel procedure.

Hope this helps

When you add a worksheet to the workbook it automatically becomes the active
sheet so it is easy to rename it right after you add it.


Quote:
> Here is a procedure I use to get a reference to an excel object and open
an
> existing workbook. This code will start with version 10 of excel and work
> back through the versions trying to get a valid excel object.  Once it
does
> it opens the workbook DOCTemplate.xls

> xlApp is defined in the declarations section of my form as type object.

> Note I'm using late  binding because I don't know which version of excel I
> will be opening on different client machines.

> Sub SetExcelObject()
>     'Checking this checkbox will open an excell application object so the
>     'footage report can be printed on it.
>     Dim i As Integer

>     On Error GoTo TryPreviousVersion
>     i = 10
>     If xlApp Is Nothing Then
>         'MsgBox "i is " & i
> tryagain:
>         Set xlApp = CreateObject("excel.application." & i)
>         xlApp.workbooks.open ("Test.xls") 'you will want to include the
full
> path here
>         xlApp.application.Visible = True 'Will make the excel window
> visible. If you don't want it visible don't do this
>     End If
>     On Error GoTo 0
>     Exit Sub
> TryPreviousVersion:
>     'MsgBox Err & " " & Err.Description, vbExclamation + vbOKOnly
>     If Err = 429 Then
>         If i = 4 Then
>             MsgBox "Error you do not have a version of excel high enough
to
> run this report.", vbExclamation + vbOKOnly, "ERROR ACTIVATING EXCEL"
>             Exit Sub
>         End If
>         i = i - 1
>         Err.Clear
>         Set xlApp = Nothing
>         Resume tryagain
>     Else
>         MsgBox Err & " " & Err.Description, vbExclamation + vbOKOnly
>     End If
> End Sub



> > I'm having trouble manipulating data in Excel 2000 from Access 2000
> > using VBA.

> > The code I have takes data from a table and throws it into a
> > spreadsheet.

> > A reference is already set for Excel.

> > when I try something like MsgBox x.ActiveSheet.Name, I get the following
> > error message:

> >        "Subscript out of range"

> > Just above the code I have the following code:

> > Dim x as Workbook
> > Set x = Workbooks("test.xls")
> > x.Activate

> > What else do I need to do? or What am I doing incorrectly?

> > Thnaks.

> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!



Mon, 22 Nov 2004 01:44:04 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Excel - VBA / query from Access (2000)

2. Can't update SQL 7.0 Tables using VBA (access 2000)

3. Using Microsoft Query 2000 with Excel 2000

4. Need help to Update an ACCESS 2000 Database using ADO and VB

5. Need help using Access 2000 and VB 6

6. need help opening a two field index with VB6 using access 2000

7. Using Microsoft Query 2000 with Excel 2000

8. Using Microsoft Query 2000 with Excel 2000

9. Using ADO Copy Data from Excel to Access (2000)

10. Using ADO with MS-Access and Excel 2000

11. Importing Excel Spreadsheet into an Access 2000 Table using VB

12. Need Help with VBA reading Excel using ADO


 
Powered by phpBB® Forum Software