Create multiple Excel workbooks and sheets

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

Guest

I need to create excel workbooks for each RespId in table tblResp. Within
this workbook, sheets must be created for each account record in tblGLData
that has a link to tblResp. On these sheets, account and account balance data
would be inserted into each sheet.

I have successfully used ADO and automation to create and do this type of
thing for one sheet, but cant determine how to create the routine to create a
workbook, insert sheets, then loop and create another workbook with sheets
for the next group. The SQL statement I use to assign the record source is
for the entire group. I tried to create two ADO record sources under one ADO
connection, but returned errors when I tried to open the 2nd recordset.

How can I do this? Below is a representation of the tables I am working with

Table: tblResp

RespId RespDescription
1 Payroll Clerk
2 AP Clerk
3 AR Clerk


Table: tblGLdata

Account AcctBalance RespId
111000 100.23 1
111001 200.34 1
112000 900.34 1
200000 1000 2
300000 50.34 3
400000 500.55 3
 
Further to...This is what i have written. It fails on trying to open the 2nd
recordset

Public Sub CreateWorkBooks()
'-----------------------------------------------------------------
'Create MS Excel Workbooks with account sheets
'
'-----------------------------------------------------------------
'Declare connection
Dim cnn1 As ADODB.Connection

'Declare recordsets
Dim rsResp As ADODB.Recordset
Dim rsAcctData As ADODB.Recordset

'Declare SQL strings
Dim strRespSQL As String, strAcctDataSQL As String

'Declare Period titles
Dim strPer1 As String, strPer2 As String
Dim strPer3 As String, strPer4 As String

'Declare Excel objects
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

'Declare other
Dim strDate As Date, strResp As String

On Error GoTo ErrorHandler

Set cnn1 = CurrentProject.Connection
Set rsResp = New ADODB.Recordset
Set rsAcctData = New ADODB.Recordset

'Assign Period Titles
strPer1 = DLookup("[Period1]", "tblPeriodTitles", "[id] = 1")
strPer2 = DLookup("[Period2]", "tblPeriodTitles", "[id] = 1")
strPer3 = DLookup("[Period3]", "tblPeriodTitles", "[id] = 1")
strPer4 = DLookup("[Period4]", "tblPeriodTitles", "[id] = 1")

'Create Excel application
Set xlApp = New Excel.Application
'Make Excel visible
xlApp.Visible = True

'Assign Date
strDate = Forms!frmCreateWorksheets!txtBalDate

strRespSQL = "SELECT tblResp.RespId, tblResp.RespDescr,
tblResp.RespActive, " _
& "tblResp.RespFileName " _
& "FROM tblResp " _
& "WHERE (((tblResp.RespActive)=True)); "

'Open rsResp recordset
With rsResp
.Open strRespSQL, cnn1
.CursorType = adOpenKeyset
End With

strResp = rsResp.Fields(0).Value

strAcctDataSQL = "SELECT tblSections.Section, tblSections.SectionDescr,
" _
& "tblAccounts.Account, tblAccounts.AcctDescr,
tblAccounts.AcctResp, tblGLData.Period1, " _
& "tblGLData.Period2, tblGLData.Period3,
tblGLData.Period4 " _
& "FROM (tblAccounts INNER JOIN tblSections ON
tblAccounts.Section = tblSections.Section) INNER JOIN tblGLData ON
tblAccounts.Account = tblGLData.Account " _
& "WHERE (((tblAccounts.AcctResp)=strResp));"

'Open rsAcctData recordset (FAILS WHEN TRYING TO OPEN THIS)
With rsAcctData
.Open strAcctDataSQL, cnn1
.CursorType = adOpenKeyset
End With

'Loop through rsResp recordset, creating workbooks, sheets and saving
Do Until rsResp.EOF
' Debug.Print rsResp.Fields(1).Value
'Open template
Set xlWorkbook = xlApp.Workbooks.Open("c:\path\bsrtemplate.xls")
rsAcctData.Requery
Do Until rsAcctData.EOF
xlApp.ActiveSheet.Name = rsAcctData.Fields(2).Value
xlApp.Range("C4").Value = rsAcctData.Fields(3)

Loop
Loop

'Clean up objects
rsResp.Close
cnn1.Close
Set rsResp = Nothing
Set cnn1 = Nothing

Exit Sub

ErrorHandler:
' Display error information.
MsgBox "Error number " & Err.Number & ": " & Err.Description
' Resume with statement following occurrence of error.
Resume Next
End Sub
 
Good call John, but I am still receiving the error "Operation is not allowed
when the object is open" message. I think it has something to do with the
2nd record set. Not sure?
 
Actually... after making that change, I get " Error number -2147467259:
Method 'Open' of object '_Recordset' failed.
 
In your code you have this assignment
strResp = rsResp.Fields(0).Value
Click to expand...

which sets strResp to the value of the first field in the first record
in rsResp.

The SQL statement for strRespSQL shows that the first field of rsResp is
tblResp.RespId. In your first message, this field appears to contain
numeric values, but you're assigning it to a string variable. Is that
what you intend?

If there's a possibility of there being more than one record in tblResp
for which RespActive is True, you should include an ORDER BY clause in
strRespSQL to ensure that the records are always in a known order.

Also, you've included the field RespFileName in the SQL statement but
don't reference it later on.
 
I tend to avoid automation unless a) the user REALLY can't put things in
bold for themselves and b) they have some decent blackmail material that
they are threatening to send to my wife. (Simplistic, but generally true.)

Why not just a couple of loops with a TransferSpreadsheet in the middle?
 
Rob,

I fully agree, but this will solve some problems with ranposition errors etc
we have been experiencing.

Just another question: when opening an ado recordset defined by an sql
statement, is it possible that the statement can be too complicated?

strDataSQL = "SELECT tblSections.Section, tblSections.SectionDescr, " _
& "tblAccounts.Account, tblAccounts.AcctDescr,
tblAccounts.AcctResp, tblGLData.Period1, " _
& "tblGLData.Period2, tblGLData.Period3,
tblGLData.Period4 " _
& "FROM (tblAccounts INNER JOIN tblSections ON
tblAccounts.Section = tblSections.Section) INNER JOIN tblGLData ON
tblAccounts.Account = tblGLData.Account " _
& "WHERE (((tblAccounts.AcctResp)=2)) " _
& "ORDER BY tblAccounts.AcctResp; "

'Open detail recordset
rsData.Open strDataSQL, cnn1 'Fails here everytime (swapped out the
sql with table name and it worked, which leads me to believe that the problem
is the sql statement??

Any suggestions?
 
Does that SQL statement work as expected when pasted into SQL view of
the query designer?
 
1) I agree with John.
2) When I first looked at this post, I ignored it as it uses ADO and I
neither like ADO (no hate mail please) nor do I know much about the
details of ADO, nor do I have easy access to Access (no pun intended) to
quickly try things out. BUT, my first thought was - do it in DAO!

Once the queries have been set up in query design view, copy the SQL
into the code, add any necessary coded criteria and everything should
work as intended.

Regards,
Andreas
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel, assign cells in multiple worksheets from one sheet 1
Macro to copy portion of each sheet and paste into the first sheet asa picture 1
Excel crashes and restarts when accessing newly created worksheet 2
copy chart between multiple sheets in workbook 4
creating multiple sheets, then individualized workbooks for each sheet 3
Macro to create new workbook and sheets 1
combo box multiple workbooks / sheets 3
Copy sheets to New Workbook - Excel 2007 1

Back
Top