Create multiple worksheets from Access database.

[Alan72]

Hi,

I'm trying to create separate excel files using access as a front end. So far I have the following code, activated from a command button on a userform.

Private Sub Command78_Click()


Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\Users\Guest\Desktop\VBA apps\TI.xlsx")
objXLApp.Application.Visible = True

' insert data from record

objXLBook.activesheet.Range("A5") = Me.[Job Title]
objXLBook.activesheet.Range("A8") = Me.[SPS No]
objXLBook.activesheet.Range("B9") = Me.[Prg Date]
objXLBook.activesheet.Range("B10") = Me.PM
objXLBook.activesheet.Range("B12") = Me.Team
objXLBook.activesheet.Range("B14") = Me.SAP
objXLBook.activesheet.Range("H9") = Me.[Issue Date]
objXLBook.activesheet.Range("G10") = Me.[PM Tel]
objXLBook.activesheet.Range("G12") = Me.[Team Tel]
objXLBook.activesheet.shapes("titxttask").textframe.characters.Text = Me.Task
objXLBook.activesheet.shapes("titxtnotes").textframe.characters.Text = Me.Notes

'save file as job title with trade identifier

objXLBook.SaveAs ("C:\Users\Guest\Desktop\VBA apps\" & Me.[Job Title] & Me.Trade & ".xlsx")

End Sub


This takes the contents from the fields and inserts them into cells and textboxes in an excel sheet called TI. The workbook is then saved as

[Job Title] Trade.xlsx (for example, Church Road1.xlsx)

This seems to work with one record but I'm not sure how to loop it through several records.

Does anyone know who to write this for more than one record so that the worksheets are created, populated with the data from the records and then saved as separate workbooks in a folder called Task Instructions?



I'm new to this game and have only been using access for about a week so please be gentle with me.

Thanks in advance.

 

[alansidman]

Why go through this exercise when you can write a query in Access and export it directly to Excel with the data and fields you want.

Alan

 

[Alan72]

The Project Managers within our company are the only staff members who have Access on their computers. The engineers, who will use these 'task instructions' have Excel, hence the requirement for excel files.

There are up to four different types of engineer (1 - 4) who use these instructions and they all need separate instructions. I would like to enter these instructions, changing the trade each time and then output this data (usually four records) to excel.

I've discovered in Excel, how to split a table of data into separate worksheets and also into separate workbooks, but I would like to combine this and save the task instructions, splitting them into separate single sheet workbooks in their own folder. I would also like to be able to print these newly created sheets.

I'm also trying to learn VBA. Any help would be greatly appreciated.

 

[alansidman]

I am confused by your explanation. I think perhaps that you misunderstood my question. Will the engineers be generating the reports using Excel to query Access or will the programmers be generating the reports in Access and exporting them to Excel for the engineers.

 

[Alan72]

Sorry Alan, great name by the way . The programmers fill out an access form with work location, engineer type, task, various dates, etc. The engineer types are 1.Civils, 2. Deliveries, 3.Jointers, 4.Linesmen.

Once this form has been completed, they click a button which fires this record to Excel which populates a worksheet in specified cells creating a single xlsx sheet. This sheet is saved as the location and trade type.

This sheet is then either printed off and included in a paper workpack or emailed to the engineer to carry out the work. The engineers don't have Access on their machines, only Excel.


The engineers don't generate the reports using access or excel, these are generated by programmers.

At the moment, as a programmer, I can navigate from record to record and produce one 'task instruction' at a time. I'd like, if possible to select any number of records, send these to excel, but create a new worksheet for each individual record.

Once that has completed, I could do the rest from excel, either splitting the workbook into separate files, or sending the workbook as a whole.

Sorry to make this painful, I read my last post and confused myself. Not sure if there is a way to post what I have so far, in case this would make it clearer.

Thanks for your patience so far.

Alan.

 

[alansidman]

Look at this link to get started. It might help.

Export data to Excel - Access - Office.com

 

[xenou]

How many records are you dealing with? It can be convenient to have a listbox where you can select the ones that you want to generate - if there aren't too many, anyway.

 

[Alan72]

Cheers Alan - I've already glanced through that and it doesn't really explain to me about exporting to an excel template.

Xenou - I probably generate 4 maybe 5 records per job. I'd like to send all these to Excel at once if possible. No need for a listbox. Once this has been done, the form and table could be reset to use on another project. The excel files are saved or printed and issued.

 

[xenou]

What's the record source for the form? Is it a query? What's the query?

 

[xenou]

This is a simple test of concept:


Basic idea is:

• Loop through records
• When we hit the end break out of the loop on an error catch and close up shop

I used savecopyas so we keep the "master" open and unchanged and just re-use it each time.

You have to be careful to be on the first record as it only goes forward from where you are. In real life I'd probably do this differently but it should work. I think if you don't have a lot of records I'd really prefer not to do this with code. Have your button just advance to the next record for you (for convenience) and hit the button for each record to print it off - you get the advantage of a pair of eyes looking over the information, if only briefly.

Private Sub Command1_Click()
Dim objXLApp As Object
Dim objXLBook As Object
Dim FSO As FileSystemObject
Dim s As String

    On Error GoTo ErrHandler:
    
    Set objXLApp = CreateObject("Excel.Application")
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set objXLBook = objXLApp.Workbooks.Open("C:\myTemp\Book1.xlsx")
    
    Do
        objXLBook.ActiveSheet.Range("A1") = Me.[ID]
        
        s = "C:\myTemp\" & Me.[ID] & ".xlsx"
        If FSO.FileExists(s) Then
            FSO.DeleteFile s
        End If
        objXLBook.SaveCopyAs s
        
        DoCmd.GoToRecord , , acNext
    Loop

My_Exit:
On Error Resume Next
If Not objXLBook Is Nothing Then
    objXLBook.Close False
End If
If Not objXLApp Is Nothing Then
    objXLApp.Quit
End If
Exit Sub

ErrHandler:
Resume My_Exit

End Sub