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!
