Send data to excel from access

[Johnny]

Hello,

I sent the results of an acces query to excel using code and
everything worked fine.
The problem is when I tried to add code to insert a formula on a
specific cell I got an error message:
Run-time error "1004"
Application-define or obect-defined error

the code that generate the error in access vba is:
..Range("C162").Formula = "=COUNTIF(C2:C157;" & "Normal" & ")"

Any ideas how to add a COUNTIF in a excel cell from access vba code??
Tks in advance

 

[Guest]

Hi Johnny,

I was able to insert a CountIf formula into an Excel spreadsheet, using
automation from Access, by making a few tweeks to your formula:

Replace the semicolon with a comma.
Double up the quotes, to pass the string "Normal", which is apparently a
criteria.
Use a variable set as a workbook, ie.

Dim wrk As Workbook (Early binding, for now)
Set wkb = XLApp.Workbooks.Open(FileName:=gblTCPOutputFile)

where gblTCPOutputFile is a variable that includes the full path to your
spreadsheet, or hard code it like this:

Set wkb = XLApp.Workbooks.Open(FileName:="C:\Temp\YourFile.xls")

and then use it like this:

wkb.ActiveSheet.Range("C162").Formula _
= "=COUNTIF(C2:C157," & """Normal""" & ")"


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Johnny]

Hi Tom

Thanks very much it worked in perfection

johnny

 

[Guest]

You're very welcome. Good luck on your project.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________