Help...VBA Module

[tdubs]

Hi Experts,

I am writting a module program using VBA in Access, and I have serious difficulties.
Objectives of the program:
-To do many queries from a database
- Export those queries in excel format. Some queries must be grouped in one files(i.e. putting the queries in different sheets of the same excel file)
- exporting those files using a template. It think this mite not be possible, but i was thinking in just making the template on the code... the template is only having a header and a footer.

So far:
What I have been able to do so far is to create queries directly and exporting them in to single excel files.
Also, I could run queries using recordset, but then don't know how to handle the data of the recordset (i.e. don't know how to get data out of the recordset and put it in a spreadsheet)

and one of the most important points, I am able to write in a excel file, in the first sheet, but dunno how to write on the second sheet and so on.

Just wondering if anyone can help me with any of my task. PLEASE...YOUR HELP IS MUCH APPREACIATTED .... THANKS IN ADVANCE

 

[MarkK]

Why use Excel at all? If all your data and queries are in Access, deliver your data to your consumers in Access.

 

[tdubs]

Why use Excel at all? If all your data and queries are in Access, deliver your data to your consumers in Access.

yea, i am actually using access, the main problem rite now would to export the data in to separate excel sheets in the same excel file

 

[MarkK]

yea, i am actually using access

My question is, if you are using Access what do you need Excel for?
In a typical business environment you have data producers and data consumers. When you say you want to put Access data into Excel spreadsheets you are...
1) probably presenting data to consumers,
2) probably new to Access.
Presenting data sourced by Access is way easier if you use Access. Have you tried making any Access reports?

 

[tdubs]

My question is, if you are using Access what do you need Excel for?
In a typical business environment you have data producers and data consumers. When you say you want to put Access data into Excel spreadsheets you are...
1) probably presenting data to consumers,
2) probably new to Access.
Presenting data sourced by Access is way easier if you use Access. Have you tried making any Access reports?

yeah both are rite, new to present data to customers... and new to access.
well i got most of it rite now.. my only problem would be writing data into different sheets of the same excel file...

well just that my supervisor told me to present data in excel format.. cuz i need to use that template.. well the template is a table header and footer.. i think i mite be able to do that in a report.. but seems complicated...

A collegue told me to use excel instead... he told that is way easier to do there.. is it true?

haha the thing is that i have done most of my program in access.. just that thing that i don't know how to do....

 

[tdubs]

When you use the TransferSpreadsheet Method/Action the TableName argument (which may be a query name), becomes the name of the sheet so if you output two sets of data with different names to the same workbook, they will end up on different sheets. The easiest way to control the name and contents of a sheet is to create a query. Don't forget to specifically order the exported data if you would like it to appear in a particular order in the spreadsheet.

Yea I tried that before, but I changed methods... Cuz What I am doing rite now, is openning a template and fill data on a table and then save.

Well, now I don't think i could use an excel template, gotta fill up the header and footer with vba access code, because each file I create wouldn't have the same number of pages... and also need to put the in separate files...

I found one code that should be able to write in different worksheets.
The following is the code:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Dim xlSheet2 As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
Set xlSheet2 = xlBook.Worksheets(2)


But with this code somehow no matter what object I use, It will write in the same sheet

 

[tdubs]

Here is my code:

Sub main()
'Declare Variables

Dim filename As String
Dim myarray As Variant
Dim tablestart(1) As Variant

'Make a Query and retrive array
myarray = GetQueryArray("'R230'", "306032")

'Define variables
tablestart(0) = 8
tablestart(1) = 1
filename = "test88"

Call PrintQ(filename, myarray, tablestart)

End Sub


Public Sub PrintQ(filenamestr As String, QueryArray As Variant, tablestart As Variant)

'Declare Variables
'Dim xlApp As Object
Dim templatepath
Dim mypath
Dim Rownum As Integer, Column As Integer
Dim I As Integer, J As Integer

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim objname1 As Excel.Worksheet
Dim objname2 As Excel.Worksheet

templatepath = "D:\"
Set xlApp = createobject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(templatepath & "templatetest.xls")
Set objname1 = xlBook.Worksheets("sheet1")
Set objname2 = xlBook.Worksheets("sheet2")

'Make Excel application Visible
xlApp.Application.Visible = True

'Define Variables
Column = UBound(QueryArray, 1) + 1
Rownum = UBound(QueryArray, 2) + 1
mypath = "D:"



'Print
objname1.Application.range("A3").Value = "Total Number of Rows Retrieved: " & Rownum

For I = 0 To Rownum - 1
For J = 0 To Column - 1
objname2.Application.cells(I + tablestart(0), J + tablestart(1)).Value = QueryArray(J, I)

Next J
Next I

'Save the sheet to C:\test.xls directory.
objname1.saveas mypath & "\" & filenamestr & ".XLS"
'Close Excel with the Quit method on the Application object.
xlApp.Application.Quit
' Release the object variable.
Set objname1 = Nothing
Set objname2 = Nothing

End Sub

Public Function GetQueryArray(unitval As String, cable_idval As String) As Variant

'Declare Variables
Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String


'Open connection to current Access database
Set db = CurrentDb()

' SQL String
LSQL = "SELECT SIS_EA.wire_tag, A_SIS.service, SIS_EA.signal_origin," _
& " SIS_EA.jb_cable_nm, SIS_EA.jbcable_in_mpname, SIS_EA.mp_tb_nm, SIS_EA.mp_tb_no," _
& " SIS_EA.cable_set_id FROM SIS_EA INNER JOIN A_SIS ON SIS_EA.ID = A_SIS.ID " _
& "WHERE SIS_EA.unit=" & unitval & "AND SIS_EA.wire_id=1 AND SIS_EA.cable_id= " & cable_idval

'LSQL = "SELECT sis_ea.id, sis_ea.wire_tag,SIS_EA.cable_set_id FROM SIS_EA where sis_ea.unit =" & unitval _
'& "AND SIS_EA.wire_id=1 AND SIS_EA.CABLE_ID=" & cable_idval
'Save query to a recordset

'Run Query
Set Lrs = db.OpenRecordset(LSQL)

'Return Array
GetQueryArray = Lrs.GetRows(1000)
End Function

NVM guys.. I got it... thanks anyways
to select the sheet would be

xlbook.Worksheet("sheetname").select