Mass export to Excel with Loop (1 Viewer)

mrtn · Sep 22, 2011

Hi

I am trying to figure out the way to export results of my query to Excel. The trick is that in the ideal world there would be a new Excel file created every time a value changes in one of the columns.

My query is pretty simple:

Code:

SELECT [Scheduler Trades].ClientCode, [Scheduler Trades].Quantity, [Scheduler Trades].SEDOL, [TP Data Fund list].Fund
FROM [Scheduler Trades] LEFT JOIN [TP Data Fund list] ON [Scheduler Trades].SEDOL=[TP Data Fund list].SEDOL
WHERE ((([Scheduler Trades].Quantity)<0));

The idea is to achieve following:

ClientCode | Quantity| SEDOL | Fund

1 | 123 | A | X
2 | 234 | A | Z
3 | 456 | B | Y
4 | 678 | B | U
5 | 789 | C | V

Files exported to Excel:

File caled A.xls would contain clients 1 & 2 and the corresponding Quantity, SEDOL and Fund

File called B.xls for clients 3 & 4

and the file C.xls for client no 5.

Any helps would be much appreciated.

vbaInet · Sep 22, 2011

So, have you managed to export each SEDOL group into separate xls files?

With regards your question, are the spreadsheets read-only?

mrtn · Sep 22, 2011

Hi

No, I have only begun thinking about it. Have no code yet....

vbaInet · Sep 22, 2011

So, are the files going to be read-only after they've been exported?

mrtn said:
The trick is that in the ideal world there would be a new Excel file created every time a value changes in one of the columns.

At what stage should this happen? Please elaborate on this.

mrtn · Sep 22, 2011

Hi

To be fair it doesn't really matter if the files are read-only or not. If I had to choose, I would probably go for read-write.

OK, the procedure would be :

1. The query is run and shows the data as in my 1st post.
2. The code is run which will look for changes in the SEDOL column and after the client no. 2 exports the excel file containing details for client 1 & 2 which share the same SEDOL.
3. After that, all clients with the same SEDOL B are selected and exported in the excel file called B.xls
And so on.

I'm not sure whether it's easier or not, but as an alternative to creating seperate excel files, exports could happen in one workbook on different worksheets.

Thanks a lot

vbaInet · Sep 22, 2011

mrtn said:
To be fair it doesn't really matter if the files are read-only or not. If I had to choose, I would probably go for read-write.

mrtn said:
The trick is that in the ideal world there would be a new Excel file created every time a value changes in one of the columns.

That's the reason why I asked because you didn't mention whether it was during the export or every so often (for example).

How are you proposing to compare your Access table against an Excel spreadsheet?

mrtn · Sep 22, 2011

Sorry I wasn't clear anough. I would like to be able to run a macro that would firtly open the query and then export excel file(s) based on results in this query.

bparkinson · Sep 22, 2011

Have you looked at the Excel CopyFromRecordset function? It is hyper fast, much, much faster than looping through a recordset. Just make your recordset(s) in VBA, instantiate Excel, and call the function.

vbaInet · Sep 23, 2011

To get the different groups and export them into separate sheets in your spreadsheet, here's what you do:

1. Write this code in a global Module, i.e. not a form or report module or a class module:

Code:

Option Compare Database
Option Explicit

Public pubSedol As String

Public Function GetPublicSedol() As String
    GetPublicSedol = pubSedol
End Function

2. In your query put this in the criteria row under Sedol:

Code:

GetPublicSedol()

3. Here's the code you will use to save the queries into separate sheets grouped by SEDOL:

Code:

dim rst as dao.recordset

set rst = currentdb.openrecordset("SELECT Sedol FROM [COLOR=Blue]QueryName[/COLOR] GROUP BY Sedol;")

do while not rst.eof
    pubSedol = rst![COLOR=Blue]Sedol[/COLOR]
    docmd.TransferSpreadsheet  acExport,[COLOR=Blue]acSpreadsheetTypeExcel9[/COLOR],"[COLOR=Blue]QueryName[/COLOR]","[COLOR=Blue]Path to excel file  directory[/COLOR]" & "\" & pubSedol & ".xls",[COLOR=Blue]true[/COLOR],pubSedol & "[COLOR=Red]![/COLOR]"
    DoEvents
    rst.movenext    
loop

pubSedol = vbNullString
rst.close
set rst = nothing

Ensure that the exclamation mark stays or else it won't work. You may amend the bits in blue.

bparkinson · Sep 23, 2011

Why would you loop through a recordset when it's inefficient, resource-consuming and slow, instead of using a native Excel function that's fast and set-based?

vbaInet · Sep 23, 2011

How do you propose accessing each group and copying them into separate sheets - which is what the recordset is only used for?

mrtn · Sep 23, 2011

vbaInet, many thanks for your help.

I have done the following and can't seem to get it to work...

1. In Visual Basic I chose Insert->Module and pasted the first part of the code

2. Updated query's criteria with the "GetPublicSedol()" (in SEDOL's criteria)

3. I have created a button on one of the forms with the OnClick event as follows:

Code:

Private Sub Command22_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT Sedol FROM Export GROUP BY Sedol;")
Do While Not rst.EOF
    pubSedol = rst!SEDOL
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Export", "G:\Martin\test" & "\" & pubSedol & ".xls", True, pubSedol & "!"
    DoEvents
    rst.MoveNext
Loop
pubSedol = vbNullString
rst.Close
Set rst = Nothing
 
End Sub

vbaInet · Sep 23, 2011

What exactly is the problem? Do you get an error? What's the error message?

mrtn · Sep 23, 2011

No, I do not get any errors. Simply nothing happens when I click on the button.

I am not sure if that may be the cause but the query with a GetPublicSedol() criteria does not display any records when I open it, therefore there is nothing to export. Does it make sense?

I will try to upload the db file within the next few mins so you could take a look.

vbaInet · Sep 23, 2011

It doesn't even create a spreadsheet?

No, it's not a problem. The query will display no records if pubSedol has not been set. pubSedol only gets set inside the loop.

Don't call your query Export. Call it qryExport and remember to amend the code.

mrtn · Sep 23, 2011

Ok, I have amended name of the query as well as changed the destination folder, but still no luck.

I have atatched a copy of the DB for your reference. I would appreciate if you could take a look.

vbaInet · Sep 23, 2011

Change this line:

Code:

Set rst = CurrentDb.OpenRecordset("SELECT SEDOL FROM [[COLOR=Red]Scheduler Trades[/COLOR]] GROUP BY SEDOL;")

The red bit should have been table name but I wrote query name in my post in error. I told you it was aircode

mrtn · Sep 23, 2011

vbaInet said:
Change this line:

Code:

Set rst = CurrentDb.OpenRecordset("SELECT SEDOL FROM [[COLOR=red]Scheduler Trades[/COLOR]] GROUP BY SEDOL;")

The red bit should have been table name but I wrote query name in my post in error. I told you it was aircode

Gald you've found the error

I get "something" happening when I press the button. However it comes up with a file naming error. I have attached a screenshot.

vbaInet · Sep 23, 2011

Oops... slight mistake in code again:

Code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExport", "[COLOR=Red]Path to xls file[/COLOR]", True, pubSedol

Since you want just one xls file with several sheets, replace the red bit with the path to just one xls file. Also I noticed the Range parameter is different when exporting and as a result the exclamation mark is not needed.

vbaInet · Sep 23, 2011

And you should use the [# Scheduler SALES] in the SELECT statement instead of the table because I just noticed that your query is based on that query and the query has criteria.

Mass export to Excel with Loop (1 Viewer)

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

Attachments

AWF VIP

AWF VIP

Similar threads

Users who are viewing this thread