enter formula into excel from access

[Guest]

I'm trying to populate data from access to excel sheet. Everything is fine
except the two errors (please see in the code below).
Could anybody help me with this?

Thanks

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Dim xlapp As Object
Dim xlbook As Object

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0

Set xlbook = xlapp.Workbooks.Add
With xlbook
..Worksheets(1).Range("A2").CopyFromRecordset rst

..Worksheets(1).Range("A1") = "Product Code"
..Worksheets(1).Range("A1").HorizontalAlignment = xlHAlignCenter ' error ???
..Worksheets(1).Range("A1").Font.Bold = True

.Worksheets(1).Range("E2:E" &
..Worksheets(1).Range("A65536").End(xlUp).Row).FormulaR1C1 = _
"=RC[-2] * RC[-1]" ' error ???
End With

 

[Alex Dybenko]

Hi,
as you are using late binding - you have to replace Excel constants with
corresponding values:
xlHAlignCenter with -4108
xlUp with -4162

etc

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Thanks a lot, Ðлех. It's working like a song.

ЗемелÑ, where I could find all those corresponding codes you're mentioning?

Best Regards,

Alex

Hi,
as you are using late binding - you have to replace Excel constants with
corresponding values:
xlHAlignCenter with -4108
xlUp with -4162

etc

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I'm trying to populate data from access to excel sheet. Everything is fine
except the two errors (please see in the code below).
Could anybody help me with this?

Thanks

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Dim xlapp As Object
Dim xlbook As Object

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0

Set xlbook = xlapp.Workbooks.Add
With xlbook
.Worksheets(1).Range("A2").CopyFromRecordset rst

.Worksheets(1).Range("A1") = "Product Code"
.Worksheets(1).Range("A1").HorizontalAlignment = xlHAlignCenter ' error
???
.Worksheets(1).Range("A1").Font.Bold = True

.Worksheets(1).Range("E2:E" &
.Worksheets(1).Range("A65536").End(xlUp).Row).FormulaR1C1 = _
"=RC[-2] * RC[-1]" ' error ???
End With

 

[Alex Dybenko]

Hi,
if you open Excel VBA, go to immediate window - you can type there constant
you need after question mark and will get its value

Also you can look in Object Browser (F2)


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Thanks a lot, Ðлех. It's working like a song.

ЗемелÑ, where I could find all those corresponding codes you're
mentioning?

Best Regards,

Alex

Hi,
as you are using late binding - you have to replace Excel constants with
corresponding values:
xlHAlignCenter with -4108
xlUp with -4162

etc

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I'm trying to populate data from access to excel sheet. Everything is
fine
except the two errors (please see in the code below).
Could anybody help me with this?

Thanks

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Dim xlapp As Object
Dim xlbook As Object

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0

Set xlbook = xlapp.Workbooks.Add
With xlbook
.Worksheets(1).Range("A2").CopyFromRecordset rst

.Worksheets(1).Range("A1") = "Product Code"
.Worksheets(1).Range("A1").HorizontalAlignment = xlHAlignCenter ' error
???
.Worksheets(1).Range("A1").Font.Bold = True

.Worksheets(1).Range("E2:E" &
.Worksheets(1).Range("A65536").End(xlUp).Row).FormulaR1C1 = _
"=RC[-2] * RC[-1]" ' error ???
End With