Exporting access data to excel 3

[farmera]

I have an Sequel db with an Access front end. I have created a master query with all data and also created custom queries in access for each tab that I need to fill in the spreadsheet. Each tab is based on a "Site" and I need to export by date range.

I would like to have this process as automated as possible but I just keep hitting road blocks. My ideal situation would be to set up a form that the users could select the criteria and push a button to export the data to the correct tab within the spreadsheet.

Any suggestions/ideas/directions would be greatly appreciated.

 

[bernie10]

Farmera,
You say you've been hitting roadblocks- have you written any code yet? If so can you post a little of whatever code you've written up to this point? It might make it easier for someone to push you in the right direction.

Collen

 

[farmera]

Bernie10, thank you for responding! I've "developed", if you can call it that, a few Access db's but used very little code besides the basic SELECT, FROM, WHERE etc. I've tried several approaches: MS Query in Excel, cross-tab queries, exporting to Excel from Access.

The company that developed the database made this suggestion:

set up queries for each page in your spreadsheet and once you determine that the queries return all data correctly then set them up with a custom form which will export the query into Excel format.

They are not very helpful since they want to do the customization. Of course my employer wants to try it in house.

 

[bernie10]

I see... well the only way I can think of automating it would be with VB, which would be tough if you don't have any experience programming. Perhaps some of the Access experts out there will be able to come up with an easier solution.

Collen

 

[SuicidED]

Hi

Put a command button on your access form than in the on click event press the little black dots to the right select code builder and paste this...
Dim objExcel As New Excel.Application '********* REMEMBER TO REFERENCE THE EXCEL LIBRARY BY SELECTING "TOOLS MENU, REFERENCES..." AND CHECKING "Microsoft Excel 9.0 Object Library"

With objExcel
.Workbooks.Add 'add a new workbook
.DisplayAlerts = False

.Visible = True 'show Excel - you can remove this if you don't want the user to see whats happening

'remove all of the sheets
While .Sheets.Count > 1
.Sheets(1).Delete
Wend


'create sheets of data

AddSheet objExcel, "Account Sub", "Account Sub Account", "Account Sub", .Sheets(1)
AddSheet objExcel, "Accounts", "Accounts", "Accounts"
AddSheet objExcel, "Customer", "Customer", "Customer"
AddSheet objExcel, "Products", "Products", "Products"
AddSheet objExcel, "RATES TABLE", "RATES", "RATES TABLE"
AddSheet objExcel, "INTERNAL CUST", "INTERNAL CUST", "INTERNAL CUST"
AddSheet objExcel, "BRANCHES", "BRANCHES-The account owner attests to the majority of the ORG, the attestation tables in GL:M can be queried for a full listing", "BRANCHES"
AddSheet objExcel, "PROJECTS", "PROJECTS", "PROJECTS"
AddSheet objExcel, "ACTIVE ORGS", "ACTIVE ORGS", "ACTIVE ORGS"
AddSheet objExcel, "INACTIVE CLOSED ORGS", "INACTIVE CLOSED ORGS", "INACTIVE CLOSED ORGS"
'save the excel sheet
.ActiveWorkbook.SaveAs "c:\temp\liftmap.xls"


'close the workbook
.ActiveWorkbook.Close

.DisplayAlerts = True
.Quit 'quit Excel
End With

'release the object
Set objExcel = Nothing



MsgBox "COMPLETE !!! "


Private Function AddSheet(ByRef objExcel As Excel.Application, ByVal sSheetName As String, ByVal sTitle As String, ByVal sSQL As String, Optional ByRef objSheet As Excel.Worksheet)

Dim objSht As Excel.Worksheet
Dim objRecordset As Recordset
Dim nCount As Integer

If objSheet Is Nothing Then
Set objSht = objExcel.Sheets.Add
Else
Set objSht = objSheet
End If

'get the data to put in the sheet
Set objRecordset = CodeDb.OpenRecordset(sSQL)

With objSht
.Cells(4, 1).CopyFromRecordset objRecordset 'paste the data into the sheet

'add the field names
For nCount = 0 To objRecordset.Fields.Count - 1
.Cells(3, 1 + nCount).Formula = objRecordset.Fields(nCount).NAME
.Cells(3, 1 + nCount).Font.Bold = True
Next nCount

.Columns.AutoFit 'autofit the data

'add the title
.Cells(1, 1).Formula = sTitle
.Cells(1, 1).Font.Bold = True
.Cells(1, 1).Font.Size = 14

'set the sheet name
.NAME = sSheetName
End With

Change the names after AddSheet objExcel to the names of your queries.


Set objRecordset = Nothing
End Function

 

[SuicidED]

Whoops this bit should "Change the names after AddSheet objExcel to the names of your queries." was a comment to tell you what to rename things to and should not be included in the code.

 

[farmera]

Thanks SuicidEd! It works perfectly!

I have another problem now and I've searched the forums but nothing specific to what I'm doing. I am using a form to allow users to select a date range for the export.

I'm exporting crosstab queries to excel and everything worked great until I added parameters to the main query for Forms!frmBCDESCustom!txtStartDate and Forms!frmBCDESCustom!txtEndDate.

Now when I run the export I get an error: Too few parameters. Expected 2.

Everything works fine in Access. Any suggestions?

Stumped again, Angie

 

[SuicidED]

Not sure if this will fix it...guessing here...in the paremeters of your query define them as date/time

 

[PHV]

In the crosstab query sql window add the following as the 1st line:
PARAMETERS [Forms]![frmBCDESCustom]![txtStartDate] DateTime, [Forms]![frmBCDESCustom]![txtEndDate] DateTime;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[farmera]

Posted to the wrong post originally!

Thanks all for the help! The GetFormValue worked for the arguments error.

I'm having another issue since I included all of the queries in the code: "Object variable or With block variable not set."

It happens while posting the second page to the spreadsheet. The exact point is in bold below:


CODE
Option Compare Database

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim objExcel As New Excel.Application
With objExcel
.Workbooks.Add 'adds a new workbook
.DisplayAlerts = False
.Visible = True 'Can remove if users don't need to see what is happening

While .Sheets.Count > 1 'Remove all of the sheets in the workbook
.Sheets(1).Delete
Wend

'Create workbook sheets to match queries to be exported
AddSheet objExcel, "BCDES_LesInfluent", "BCDES_LesInfluent", "BCDES_LesInfluent", .Sheets(1)
AddSheet objExcel, "BCDES_LesEffluent", "BCDES_LesEffluent", "BCDES_LesEffluent"
AddSheet objExcel, "BCDES_LesDownstream", "BCDES_LesDownstream", "BCDES_LesDownstream"
AddSheet objExcel, "BCDES_LesUpstream", "BCDES_LesUpstream", "BCDES_LesUpstream"
AddSheet objExcel, "BCDES_LesSolidsCake", "BCDES_LesSolidsCake", "BCDES_LesSolidsCake"
AddSheet objExcel, "BCDES_LesSolidsDig1", "BCDES_LesSolidsDig1", "BCDES_LesSolidsDig1"
AddSheet objExcel, "BCDES_LesSolidsDig2", "BCDES_LesSolidsDig2", "BCDES_LesSolidsDig2"
AddSheet objExcel, "BCDES_LesSolidsDig3", "BCDES_LesSolidsDig3", "BCDES_LesSolidsDig3"
AddSheet objExcel, "BCDES_LesSolidsDig4", "BCDES_LesSolidsDig4", "BCDES_LesSolidsDig4"
AddSheet objExcel, "BCDES_LesSolidsSBT", "BCDES_LesSolidsSBT", "BCDES_LesSolidsSBT"
AddSheet objExcel, "BCDES_LesSolids2MGD", "BCDES_LesSolids2MGD", "BCDES_LesSolids2MGD"
AddSheet objExcel, "BCDES_LesSolids6MGD", "BCDES_LesSolids6MGD", "BCDES_LesSolids6MGD"
AddSheet objExcel, "BCDES_LesMonthlyCake", "BCDES_LesMonthlyCake", "BCDES_LesMonthlyCake"
AddSheet objExcel, "BCDES_UMCInfluent", "BCDES_UMCInfluent", "BCDES_UMCInfluent"
AddSheet objExcel, "BCDES_UMCEffluent", "BCDES_UMCEffluent", "BCDES_UMCEffluent"
AddSheet objExcel, "BCDES_UMCDownstream", "BCDES_UMCDownstream", "BCDES_UMCDownstream"
AddSheet objExcel, "BCDES_UMCUpstream", "BCDES_UMCUpstream", "BCDES_UMCUpstream"
AddSheet objExcel, "BCDES_UMCSolidsCake", "BCDES_UMCSolidsCake", "BCDES_UMCSolidsCake"
AddSheet objExcel, "BCDES_UMCSolidsDig1", "BCDES_UMCSolidsDig1", "BCDES_UMCSolidsDig1"
AddSheet objExcel, "BCDES_UMCSolidsDig2", "BCDES_UMCSolidsDig2", "BCDES_UMCSolidsDig2"
AddSheet objExcel, "BCDES_UMCSolidsDig3", "BCDES_UMCSolidsDig3", "BCDES_UMCSolidsDig3"
AddSheet objExcel, "BCDES_UMCSolidsDig4", "BCDES_UMCSolidsDig4", "BCDES_UMCSolidsDig4"
AddSheet objExcel, "BCDES_UMCSolidsDitch1", "BCDES_UMCSolidsDitch1", "BCDES_UMCSolidsDitch1"
AddSheet objExcel, "BCDES_UMCSolidsDitch2", "BCDES_UMCSolidsDitch2", "BCDES_UMCSolidsDitch2"
AddSheet objExcel, "BCDES_UMCMonthlyCake", "BCDES_UMCMonthlyCake", "BCDES_UMCMonthlyCake"
AddSheet objExcel, "BCDES_AlamoInfluent", "BCDES_AlamoInfluent", "BCDES_AlamoInfluent"
AddSheet objExcel, "BCDES_AlamoEffluent", "BCDES_AlamoEffluent", "BCDES_AlamoEffluent"
AddSheet objExcel, "BCDES_AlamoSolidsDownstream", "BCDES_AlamoSolidsDownstream", "BCDES_AlamoSolidsDownstream"
AddSheet objExcel, "BCDES_AlamoSolidsUpstream", "BCDES_AlamoSolidsUpstream", "BCDES_AlamoSolidsUpstream"
AddSheet objExcel, "BCDES_QueenAcresInfluent", "BCDES_QueenAcresInfluent", "BCDES_QueenAcresInfluent"
AddSheet objExcel, "BCDES_QueenAcresEffluent", "BCDES_QueenAcresEffluent", "BCDES_QueenAcresEffluent"
AddSheet objExcel, "BCDES_QueenAcresDownstream", "BCDES_QueenAcresDownstream", "BCDES_QueenAcresDownstream"
AddSheet objExcel, "BCDES_QueenAcresUpstream", "BCDES_QueenAcresUpstream", "BCDES_QueenAcresUpstream"
AddSheet objExcel, "BCDES_WadeMillInfluent", "BCDES_WadeMillInfluent", "BCDES_WadeMillInfluent"
AddSheet objExcel, "BCDES_WadeMillEffluent", "BCDES_WadeMillEffluent", "BCDES_WadeMillEffluent"
AddSheet objExcel, "BCDES_WadeMillDownstream", "BCDES_WadeMillDownstream", "BCDES_WadeMillDownstream"
AddSheet objExcel, "BCDES_WadeMillUpstream", "BCDES_WadeMillUpstream", "BCDES_WadeMillUpstream"
AddSheet objExcel, "BCDES_UMCSolidsCake", "BCDES_UMCSolidsCake", "BCDES_UMCSolidsCake"
AddSheet objExcel, "BCDES_NewMiamiVillage", "BCDES_NewMiamiVillage", "BCDES_NewMiamiVillage"
AddSheet objExcel, "BCDES_NewMiamiEffluent", "BCDES_NewMiamiEffluent", "BCDES_NewMiamiEffluent"

'Save the workbook
.ActiveWorkbook.SaveAs "C:\temp\Results.xls"

'Close the workbook
.ActiveWorkbook.Close
.DisplayAlerts = True
.Quit 'Quit Excel
End With

'Release the object
Set objExcel = Nothing

MsgBox "Completed Export - File saved to C:\temp\Results"
Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub
Private Function AddSheet(ByRef objExcel As Excel.Application, _
ByVal sSheetName As String, ByVal sTitle As String, ByVal sSQL As String, _
Optional ByRef objSheet As Excel.Worksheet)

Dim objSht As Excel.Worksheet
Dim objRecordset As Recordset
Dim nCount As Integer

If objSheet Is Nothing Then
Set objSheet = objExcel.Sheets.Add
Else
Set objSht = objSheet
End If

'Getting the data to import into the worksheet
Set objRecordset = CodeDb.OpenRecordset(sSQL)

'Paste the data into the sheet
With objSht
.Cells(4, 1).CopyFromRecordset objRecordset

'Add the Field names
For nCount = 0 To objRecordset.Fields.Count - 1
.Cells(3, 1 + nCount).Formula = objRecordset.Fields(nCount).Name
.Cells(3, 1 + nCount).Font.Bold = True
Next nCount

'Automatically adjust column width for data
.Columns.AutoFit

'Add the Title
.Cells(1, 1).Formula = sTitle
.Cells(1, 1).Font.Bold = True
.Cells(1, 1).Font.Size = 14

'Set the sheet name
.Name = sSheetName

End With

Set objRecordset = Nothing
End Function

Please forgive this newbie for being a pest!

Angie

 

[RoyVidar]

Since you're using objSht in the routine, I wouldn't be surprised if this:

[tt] If objSheet Is Nothing Then
Set objSheet = objExcel.Sheets.Add
Else
Set objSht = objSheet
End If[/tt]

should be replaced with

[tt] If objSheet Is Nothing Then
Set objSht = objExcel.Sheets.Add
Else
Set objSht = objSheet
End If[/tt]

To enter bold text, type [/ignore]bold[/ignore], more hints can be found through clicking the Process TGML link at the bottom of the reply window.

Roy-Vidar

 

[RoyVidar]

Oups premature submit ... To enter bold text, type [ignore]bold[/ignore]

Roy-Vidar

 

[farmera]

Thanks Roy-Vidar! That worked.