Call VBA event from VB script

[Autoeng]

I am using Data Access Pages for interface with an Access 2K backend. On of the things that I need to do is to export the backend data from a query to an Excel spreadsheet.

Tried displaying the information on the DAP in both an Excel Spreadsheet window and in a pivot table but when I welect the button to export to Excel I get unpredictable results (not all data available).

I can create VBA to do this but need to know how to call VBA from VB script. Does anyone know how to do this?

Autoeng

 

[crosmill]

I'm not sure that you can call the vba from there, but you can put the vba code in to your dap (I think), it might need a little modification though. Done any ASP......???

I've built a website that exports filtered recordsets into an csv file. so if you want to go down that route I can give you a hand.

hth

 

[Autoeng]

Thanks for the reply Crosmill,

Please send me some info on how you accomplished your export as I would like to pursue it.

Thanks,
Autoeng

 

[crosmill]

You asked for it........

<%
strSQL = Request.Querystring("q")

Set sqlsearch = Server.CreateObject("ADODB.Recordset")
sqlsearch.Open strSQL, strConnect, adOpenDynamic, adLockOptimistic, adCmdText



strFileName = server.MapPath("csv\Export.csv")

dim objLogFileFSO
Set objLogFileFSO = CreateObject("Scripting.FileSystemObject")

dim objLogFileTS
If objLogFileFSO.FileExists(strLogFileName) Then
Set objLogFileTS = objLogFileFSO.OpenTextFile(strFileName, ForAppending)
Else
Set objLogFileTS = objLogFileFSO.CreateTextFile(strFileName)
End If

objLogFileTS.WriteLine("""CompanyID"",""AddressID"",""ConatctID"",""Title"",""First Name"",""Surname"",""Jobtitle"",""Department"",""Company"",""Address 1""" & _
",""Address 2"",""Address 3"",""Town"",""County"",""Country"",""Postcode"",""RepSARE"",""RepA"",""NonUK"",""Closed"",""Post Region""," & _
"""Flexible Pipe1"",""Push Fit1"",""Above Ground/Gutter1"",""Waste1"",""Soil1"",""Traps1""," & _
"""Flexible Pipe2"",""Push Fit2"",""Above Ground/Gutter2"",""Waste2"",""Soil2"",""Traps2""," & _
"""Flexible Pipe3"",""Push Fit3"",""Above Ground/Gutter3"",""Waste3"",""Soil3"",""Traps3""," & _
"""www"",""Contact Type"",""Tel STD"",""Tel Number"",""Fax STD"",""Fax Number"",""email"",""Source"",""CMORBY"",""CMODEL""")

Do While NOT sqlsearch.EOF
objLogFileTS.WriteLine("""" & sqlsearch("CompanyID") & """,""" & sqlsearch("AddressID") & """,""" & sqlsearch("ContactID") & _
""",""" & sqlsearch("title") & """,""" & sqlsearch("firstname") & """,""" & sqlsearch("secondname") & """,""" & sqlsearch("jobtitle") & _
""",""" & sqlsearch("department") & """,""" & sqlsearch("companyname") & """,""" & sqlsearch("add1") & """,""" & sqlsearch("add2") & _
""",""" & sqlsearch("add3") & """,""" & sqlsearch("town") & """,""" & sqlsearch("county") & """,""" & sqlsearch("country") & _
""",""" & sqlsearch("postcode") & """,""" & sqlsearch("repsare") & """,""" & sqlsearch("repa") & """,""" & sqlsearch("nonuk") & _
""",""" & sqlsearch("closed") & """,""" & sqlsearch("postregion") & _
""",""" & sqlsearch("flexiblepipe") & """,""" & sqlsearch("pushfit") & """,""" & sqlsearch("aboveground") & """,""" & sqlsearch("waste") & """,""" & sqlsearch("soil") & """,""" & sqlsearch("traps") & _
""",""" & sqlsearch("flexiblepipe1") & """,""" & sqlsearch("pushfit1") & """,""" & sqlsearch("aboveground1") & """,""" & sqlsearch("waste1") & """,""" & sqlsearch("soil1") & """,""" & sqlsearch("traps1") & _
""",""" & sqlsearch("flexiblepipe2") & """,""" & sqlsearch("pushfit2") & """,""" & sqlsearch("aboveground2") & """,""" & sqlsearch("waste2") & """,""" & sqlsearch("soil2") & """,""" & sqlsearch("traps2") & _
""",""" & sqlsearch("www") & """,""" & sqlsearch("contacttype") & """,""" & sqlsearch("telstd") & """,""" & sqlsearch("telno") & """,""" & sqlsearch("faxstd")& _
""",""" & sqlsearch("faxno") & """,""" & sqlsearch("email") & """,""" & sqlsearch("source") & """,""" & sqlsearch("CMORBY") & """,""" & sqlsearch("CMODEL") & """")
sqlsearch.MoveNext
Loop

url = Date & Timer
Response.Redirect "csv/Export.csv?url=" & url & ".csv"
%>

and now for a little explanation.

If you've done a fair bit of VBA it shouldn't look too unfamiliar (hopefully)


This bit opens the recordset, but dap should already have done this for you

Set sqlsearch = Server.CreateObject("ADODB.Recordset")
sqlsearch.Open strSQL, strConnect, adOpenDynamic, adLockOptimistic, adCmdText

The next bit opens the fileSystemObject which does all the work

objLogFileTS.WriteLine() Writes the first line of the file, which is field headings

and Do While NOT sqlsearch.EOF
objLogFileTS.WriteLine(.....

Loops all the data and writes it tot he file.

All these bits & """,""" & are doing is inserting commas and qutoes so the file is in csv format (if you open csv as a text file you'll see them)


OK so I'm not sure how much of this you understand so post back let me know if it makes sense.

If you post the code for your dap I'll be able to pick out the recordset for you.

 

[Autoeng]

Thanks again Crosmill:

Let me chew on this for awhile. I'm sure I'll have more questions though.

Autoeng