Exporting large queries to excel?
- Thread starter JvM
- Start date
As you know Excel can only take 65,000 rows per sheet and unfortunately there is no built in method of splitting out your data accross seperate sheets.
So you need to do it yourself!!!!
You'll probably have to create 2 or more recordset objects that break down your data into easier to use chunks, then export each one to the spreadsheet seperatly. Also try and use Excels copy from recordset function instead of Transferspreadsheet or OutputTo... its much faster!
Good luck
PS If you need it I have a generic function that exports to Excel I can post
So you need to do it yourself!!!!
You'll probably have to create 2 or more recordset objects that break down your data into easier to use chunks, then export each one to the spreadsheet seperatly. Also try and use Excels copy from recordset function instead of Transferspreadsheet or OutputTo... its much faster!
Good luck
PS If you need it I have a generic function that exports to Excel I can post
Ok heres the code for ya...
There are 2 functions..
fExportToExcel - This creates an Excel export
fOpenApplication - This opens an instance of an application
To use copy and paste the 2 functions into your db.
Then create a recordset object that contains the data you wish to export.
The call the function and pass in the recordset plus your options...
fExportToExcel rsData, "C:\", "Bigdata.xls", "Big Data", true, false
Explanation of the options....
rsExport = Recordset object containing data to export
strFilePath = The path of the export
strFileName = The file name to export
strSheetName = the name of the sheet in created excel file
blnCreateHeaders = Export field names? True or false
blnShowExcel = Show Excel when exporting? True or false
blnCloseAfterExport = Close Excel after export? True or false
blnNewWorkbook = Is this a new workbook or adding to an existing? True or false
blnDispMsg = Display a message to confirm export?True or false
Hope this helps you!
### Code ###
Function fExportToExcel(rsExport As DAO.Recordset, strFilePath As String, strFileName As String, _
Optional strSheetName As String = "Export", _
Optional blnCreateHeaders As Boolean = True, _
Optional blnShowExcel As Boolean = False, _
Optional blnCloseAfterExport As Boolean = True, _
Optional blnNewWorkbook As Boolean = True, _
Optional blnDispMsg As Boolean = True)
'============================================================
' Purpose: Exports contents of a recordset
' Programmer: Richard Jervis
' Date: 30/09/2002
'============================================================
On Error GoTo fExportToExcel_Err
Dim strErrMsg As String 'For Error Handling
Dim objExcel As Object
Dim objWorkbook As Object
Dim lngSheetCount As Long
Dim lngDeleteCount As Long
Dim lngFieldCount As Long
Dim blnSheetFound As Boolean
Dim varField As Variant
Set objExcel = fOpenApplication("Excel", blnShowExcel, True)
blnSheetFound = False
With objExcel.workbooks
.Application.displayalerts = False
If blnNewWorkbook = True Then
'Create new workbook
Set objWorkbook = .Add
Else
'Use an existing workbook
If Dir(strFilePath & strFileName) = strFileName Then
'File exists
Set objWorkbook = .Open(strFilePath & strFileName, False)
Else
'#### File does not exist ####
blnNewWorkbook = True
Set objWorkbook = .Add
End If
End If
End With
With objWorkbook
lngSheetCount = .sheets.Count
If blnNewWorkbook = True Then
'New workbook
If lngSheetCount > 1 Then
'If their is more than one, delete it.
For lngDeleteCount = lngSheetCount To 2
.sheets(lngDeleteCount).Delete
Next
Else
End If
.sheets(1).Name = strSheetName
Else
'Existing workbook
'Try to find worksheet
For lngSheetCount = 1 To .sheets.Count
If LCase(.sheets(lngSheetCount)) = LCase(strSheetName) Then
blnSheetFound = True
Exit For
End If
Next
If blnSheetFound = True Then
'Found sheet, do nothing
Else
'No sheet found lets create one
.sheets.Add after:=.sheets(.sheets.Count)
'Now we need to rename it
.sheets(.sheets.Count) = strSheetName
End If
End If
'Clear out the sheet
.sheets(strSheetName).cells(1, 1).clearcontents
If blnCreateHeaders = True Then
lngFieldCount = 1
For Each varField In rsExport.Fields
.sheets(strSheetName).cells(1, lngFieldCount).Value = varField.Name
lngFieldCount = lngFieldCount + 1
Next
.sheets(strSheetName).cells(2, 1).copyfromrecordset rsExport
Else
.sheets(strSheetName).cells(1, 1).copyfromrecordset rsExport
End If
.sheets(strSheetName).usedrange.entirecolumn.autofit
.Saveas strFilePath & strFileName
If blnCloseAfterExport = True Then
.Application.Quit
Else
If blnShowExcel = False Then
'Excel is not visible we must close it
.Application.Quit
Else
End If
End If
End With
If blnDispMsg = True Then
MsgBox "Excel file export created!", vbInformation
End If
fExportToExcel_Exit:
On Error Resume Next
objExcel.Application.displayalerts = True
Set objExcel = Nothing
Exit Function
fExportToExcel_Err:
Select Case Err
Case Else
'File is open
strErrMsg = vbNullString
strErrMsg = "There has been a problem exporting the file. " & vbCrLf & _
"This may be due to someone being in the file."
MsgBox strErrMsg, vbCritical, "Output Error!"
End Select
End Function
Function fOpenApplication(strApplication As String, Optional blnVisible As Boolean = False, _
Optional blnForceNewApp As Boolean = False) As Object
'============================================================
' Purpose: Opens an instance of a selected application
' Programmer: Richard Jervis
' Date: 05/02/02
'============================================================
On Error GoTo fOpenApplication_Err
Dim strErrMsg As String 'For Error Handling
Dim objApplication As Variant
On Error Resume Next
Set objApplication = GetObject(, strApplication & ".Application")
If Err.Number <> 0 Or blnForceNewApp = True Then
'Excel Not Open
Err.Clear
'Turn error handling back on
On Error GoTo fOpenApplication_Err
Set objApplication = CreateObject(strApplication & ".Application")
Else
On Error GoTo fOpenApplication_Err
End If
'Do we want to see the application?
If blnVisible = True Then
objApplication.Visible = True
End If
Set fOpenApplication = objApplication
fOpenApplication_Exit:
Set objApplication = Nothing
Exit Function
fOpenApplication_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf & vbCrLf
strErrMsg = strErrMsg & "Error Description: " & Err.Description & vbCrLf
MsgBox strErrMsg, vbInformation, "Error in fOpenApplication procedure"
Resume fOpenApplication_Exit
End Select
End Function
There are 2 functions..
fExportToExcel - This creates an Excel export
fOpenApplication - This opens an instance of an application
To use copy and paste the 2 functions into your db.
Then create a recordset object that contains the data you wish to export.
The call the function and pass in the recordset plus your options...
fExportToExcel rsData, "C:\", "Bigdata.xls", "Big Data", true, false
Explanation of the options....
rsExport = Recordset object containing data to export
strFilePath = The path of the export
strFileName = The file name to export
strSheetName = the name of the sheet in created excel file
blnCreateHeaders = Export field names? True or false
blnShowExcel = Show Excel when exporting? True or false
blnCloseAfterExport = Close Excel after export? True or false
blnNewWorkbook = Is this a new workbook or adding to an existing? True or false
blnDispMsg = Display a message to confirm export?True or false
Hope this helps you!
### Code ###
Function fExportToExcel(rsExport As DAO.Recordset, strFilePath As String, strFileName As String, _
Optional strSheetName As String = "Export", _
Optional blnCreateHeaders As Boolean = True, _
Optional blnShowExcel As Boolean = False, _
Optional blnCloseAfterExport As Boolean = True, _
Optional blnNewWorkbook As Boolean = True, _
Optional blnDispMsg As Boolean = True)
'============================================================
' Purpose: Exports contents of a recordset
' Programmer: Richard Jervis
' Date: 30/09/2002
'============================================================
On Error GoTo fExportToExcel_Err
Dim strErrMsg As String 'For Error Handling
Dim objExcel As Object
Dim objWorkbook As Object
Dim lngSheetCount As Long
Dim lngDeleteCount As Long
Dim lngFieldCount As Long
Dim blnSheetFound As Boolean
Dim varField As Variant
Set objExcel = fOpenApplication("Excel", blnShowExcel, True)
blnSheetFound = False
With objExcel.workbooks
.Application.displayalerts = False
If blnNewWorkbook = True Then
'Create new workbook
Set objWorkbook = .Add
Else
'Use an existing workbook
If Dir(strFilePath & strFileName) = strFileName Then
'File exists
Set objWorkbook = .Open(strFilePath & strFileName, False)
Else
'#### File does not exist ####
blnNewWorkbook = True
Set objWorkbook = .Add
End If
End If
End With
With objWorkbook
lngSheetCount = .sheets.Count
If blnNewWorkbook = True Then
'New workbook
If lngSheetCount > 1 Then
'If their is more than one, delete it.
For lngDeleteCount = lngSheetCount To 2
.sheets(lngDeleteCount).Delete
Next
Else
End If
.sheets(1).Name = strSheetName
Else
'Existing workbook
'Try to find worksheet
For lngSheetCount = 1 To .sheets.Count
If LCase(.sheets(lngSheetCount)) = LCase(strSheetName) Then
blnSheetFound = True
Exit For
End If
Next
If blnSheetFound = True Then
'Found sheet, do nothing
Else
'No sheet found lets create one
.sheets.Add after:=.sheets(.sheets.Count)
'Now we need to rename it
.sheets(.sheets.Count) = strSheetName
End If
End If
'Clear out the sheet
.sheets(strSheetName).cells(1, 1).clearcontents
If blnCreateHeaders = True Then
lngFieldCount = 1
For Each varField In rsExport.Fields
.sheets(strSheetName).cells(1, lngFieldCount).Value = varField.Name
lngFieldCount = lngFieldCount + 1
Next
.sheets(strSheetName).cells(2, 1).copyfromrecordset rsExport
Else
.sheets(strSheetName).cells(1, 1).copyfromrecordset rsExport
End If
.sheets(strSheetName).usedrange.entirecolumn.autofit
.Saveas strFilePath & strFileName
If blnCloseAfterExport = True Then
.Application.Quit
Else
If blnShowExcel = False Then
'Excel is not visible we must close it
.Application.Quit
Else
End If
End If
End With
If blnDispMsg = True Then
MsgBox "Excel file export created!", vbInformation
End If
fExportToExcel_Exit:
On Error Resume Next
objExcel.Application.displayalerts = True
Set objExcel = Nothing
Exit Function
fExportToExcel_Err:
Select Case Err
Case Else
'File is open
strErrMsg = vbNullString
strErrMsg = "There has been a problem exporting the file. " & vbCrLf & _
"This may be due to someone being in the file."
MsgBox strErrMsg, vbCritical, "Output Error!"
End Select
End Function
Function fOpenApplication(strApplication As String, Optional blnVisible As Boolean = False, _
Optional blnForceNewApp As Boolean = False) As Object
'============================================================
' Purpose: Opens an instance of a selected application
' Programmer: Richard Jervis
' Date: 05/02/02
'============================================================
On Error GoTo fOpenApplication_Err
Dim strErrMsg As String 'For Error Handling
Dim objApplication As Variant
On Error Resume Next
Set objApplication = GetObject(, strApplication & ".Application")
If Err.Number <> 0 Or blnForceNewApp = True Then
'Excel Not Open
Err.Clear
'Turn error handling back on
On Error GoTo fOpenApplication_Err
Set objApplication = CreateObject(strApplication & ".Application")
Else
On Error GoTo fOpenApplication_Err
End If
'Do we want to see the application?
If blnVisible = True Then
objApplication.Visible = True
End If
Set fOpenApplication = objApplication
fOpenApplication_Exit:
Set objApplication = Nothing
Exit Function
fOpenApplication_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf & vbCrLf
strErrMsg = strErrMsg & "Error Description: " & Err.Description & vbCrLf
MsgBox strErrMsg, vbInformation, "Error in fOpenApplication procedure"
Resume fOpenApplication_Exit
End Select
End Function
Thankx very much for the code..
I'm trying to understand what it reads.. And building it into my application..
One more question.. I have ten queries with export data. Should I build the funtion 10 times?
Again thank you very much for taking the effort.
Greetings,
Jan
I'm trying to understand what it reads.. And building it into my application..
One more question.. I have ten queries with export data. Should I build the funtion 10 times?
Again thank you very much for taking the effort.
Greetings,
Jan
If you are going to export 10 times then you should probably keep Excel open until the last export to save time. You can do this by using the blnCloseAfterExport option and only setting this to true after you last export.
But you would have to create 10 different recordsets and call the function 10 times.
the function probably needs to be updated slightly to perhaps also accept an array or Recordsets to process, but I have not had the time.
If you need any more help let me know
But you would have to create 10 different recordsets and call the function 10 times.
the function probably needs to be updated slightly to perhaps also accept an array or Recordsets to process, but I have not had the time.
If you need any more help let me know
Maybey you miss understood me..
I have a form from wich a user can make a choice in several command buttons (queries) these should be exported to excel. Then the user can work with them..
Should I include the code you send me with every command button?
Greetings,
Jan
I have a form from wich a user can make a choice in several command buttons (queries) these should be exported to excel. Then the user can work with them..
Should I include the code you send me with every command button?
Greetings,
Jan
Sorry, suppose the code is a bit much if your starting out!!!!
Ok Ive created a quick example databas for you..
Open the database and then open the form. There are a number of controls on there that basically just control all the options that the function has available.
Ive added a combo box, that lists all of the tables in the database, if you want to add some of your own tables the form will allow you to choose those as well. Pick a table from the list, select your options for the Excel export and then click the button.
Have a play around and see what the different options do.
Please remember this has been put together quickly this morning so needs some error handling in the form!!!!
Have fun!
Ok Ive created a quick example databas for you..
Open the database and then open the form. There are a number of controls on there that basically just control all the options that the function has available.
Ive added a combo box, that lists all of the tables in the database, if you want to add some of your own tables the form will allow you to choose those as well. Pick a table from the list, select your options for the Excel export and then click the button.
Have a play around and see what the different options do.
Please remember this has been put together quickly this morning so needs some error handling in the form!!!!
Have fun!
I have a problem converting the dbase.
Acces I don't have the good user credentials. I don't belong to the users that are allowed to converting or changing.
I have full control on the folder options..
Haven't seen this warning before. Did you put a security on it?
Jan
Acces I don't have the good user credentials. I don't belong to the users that are allowed to converting or changing.
I have full control on the folder options..
Haven't seen this warning before. Did you put a security on it?
Jan
H
hiranthi
Guest
Hi Richard,
I was tryingto use ur code as well. But when I try to use it with an existing file t gives the following error "There has been a problem exporting the file. This may be due to someone being in the file". Nobody is accessing the file. The code itself is opening the file. Can u pls advice how I could resolve this.
Actually my objective is, I am writing the access table to a .csv file using the transfertext method. Once I do that I need to put a report heading on top of the column headings. Looks like ur piece of code works. If I can solve the above problem.
BTW that is a good piece oof code. Well Done.
Thanks
Deshika
I was tryingto use ur code as well. But when I try to use it with an existing file t gives the following error "There has been a problem exporting the file. This may be due to someone being in the file". Nobody is accessing the file. The code itself is opening the file. Can u pls advice how I could resolve this.
Actually my objective is, I am writing the access table to a .csv file using the transfertext method. Once I do that I need to put a report heading on top of the column headings. Looks like ur piece of code works. If I can solve the above problem.
BTW that is a good piece oof code. Well Done.
Thanks
Deshika
