enter formula into excel from access

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Thanks a lot, Ðлех. It's working like a song.

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

Best Regards,

Alex

Alex Dybenko said:
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


Alex said:
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
Click to expand...
Click to expand...
 
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

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

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

Best Regards,

Alex

Alex Dybenko said:
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


Alex said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top