Using Forms to Export Data from an Access Table to an Excel Spreadsheet

D

DoveArrow

This is a question that I've been asking myself for years. I've seen
some answers on various websites. However, I have yet to see one that
is: 1) Easy enough for novice programmers like myself to understand,
and 2) Works.

Below is a solution that I worked out for myself, using a button on a
form and a text box. Basically, it allows users to open the
spreadsheet on their own desktop using a Saved Export. I don't know if
it's the most elegant of solutions, but it seems to work pretty well.

Private Sub OK_Click()
Dim str As String

str = Me.TextBox 'Set string equal to text in TextBox

On Error GoTo Err_OK_Click

If Len(Trim(Nz(Me.TextBox))) = 0 Then 'If there is nothing in TextBox.

MsgBox "Please enter your name.
Me.TextBox.Set Focus

Else

DoCmd.SetWarnings False 'Turn warnings off.
DoCmd.OpenQuery "qdelTable" 'Delete all entries from Table.
DoCmd.OpenQuery "qappTable" 'Append all entries from your query to
Table.
DoCmd.SetWarnings True 'Turn warnings on.
DoCmd.OpenTable "Table" 'Open Table.
DoCmd.RunSavedImportExport str 'Run the saved export for the person
whose name was entered in TextBox.

End If

Close_OK_Click:
DoCmd.Close acForm, "Form" 'Close Form.

Exit Sub

Err_OK_Click:
Select Case Err.Number
Case 2302 'This error will pop up if the Excel Spreadsheet towards
which you are trying to export data is open.
MsgBox "Error 2302: Microsoft Access can't export the query you've
selected to Excel." & vbCr & _
"Please make sure that the Excel file towards which you are trying to
export data is closed."
Case 3349 'This error will pop up if someone has entered text in a
date or number field on the New Admits Excel Spreadsheet.
MsgBox "Error 3349: Data mismatch. Please check the New Admits Excel
Spreadsheet" & vbCr & _
"to make sure that text has not been entered into a number or date
field for your students."
Case 31602 'This error will pop up if there are no saved exports for
the APS name entered.
Select Case MsgBox("No export subroutines exist for " & str & ". Do
you wish to create one?", vbQuestion + vbYesNo)
Case vbYes
DoCmd.RunCommand acCmdExportExcel 'Runs the Export to Excel wizard.
GoTo Close_OK_Click
Case vbNo
MsgBox "You will need to create an export subroutine before you can
export data to Excel."
GoTo Close_OK_Click
End Select
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
End Select
End Sub
 
D

DoveArrow

This is a question that I've been asking myself for years. I've seen
some answers on various websites. However, I have yet to see one that
is: 1) Easy enough for novice programmers like myself to understand,
and 2) Works.

Below is a solution that I worked out for myself, using a button on a
form and a text box. Basically, it allows users to open the
spreadsheet on their own desktop using a Saved Export. I don't know if
it's the most elegant of solutions, but it seems to work pretty well.

Private Sub OK_Click()
Dim str As String

str = Me.TextBox 'Set string equal to text in TextBox

On Error GoTo Err_OK_Click

If Len(Trim(Nz(Me.TextBox))) = 0 Then 'If there is nothing in TextBox.

MsgBox "Please enter your name.
Me.TextBox.Set Focus

Else

DoCmd.SetWarnings False 'Turn warnings off.
DoCmd.OpenQuery "qdelTable" 'Delete all entries from Table.
DoCmd.OpenQuery "qappTable" 'Append all entries from your query to
Table.
DoCmd.SetWarnings True 'Turn warnings on.
DoCmd.OpenTable "Table" 'Open Table.
DoCmd.RunSavedImportExport str 'Run the saved export for the person
whose name was entered in TextBox.

End If

Close_OK_Click:
DoCmd.Close acForm, "Form" 'Close Form.

Exit Sub

Err_OK_Click:
Select Case Err.Number
Case 2302 'This error will pop up if the Excel Spreadsheet towards
which you are trying to export data is open.
MsgBox "Error 2302: Microsoft Access can't export the query you've
selected to Excel." & vbCr & _
"Please make sure that the Excel file towards which you are trying to
export data is closed."
Case 3349 'This error will pop up if someone has entered text in a
date or number field on the New Admits Excel Spreadsheet.
MsgBox "Error 3349: Data mismatch. Please check the New Admits Excel
Spreadsheet" & vbCr & _
"to make sure that text has not been entered into a number or date
field for your students."
Case 31602 'This error will pop up if there are no saved exports for
the APS name entered.
Select Case MsgBox("No export subroutines exist for " & str & ". Do
you wish to create one?", vbQuestion + vbYesNo)
Case vbYes
DoCmd.RunCommand acCmdExportExcel 'Runs the Export to Excel wizard.
GoTo Close_OK_Click
Case vbNo
MsgBox "You will need to create an export subroutine before you can
export data to Excel."
GoTo Close_OK_Click
End Select
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
End Select
End Sub

Oops. I almost had it right.

Private Sub OK_Click()
Dim str As String

On Error GoTo Err_OK_Click

If Len(Trim(Nz(Me.TextBox))) = 0 Then 'If there is nothing in TextBox.

MsgBox "Please enter your name.
Me.TextBox.Set Focus
Exit Sub

Else

str = Me.TextBox 'Set string equal to text in TextBox
DoCmd.SetWarnings False 'Turn warnings off.
DoCmd.OpenQuery "qdelTable" 'Delete all entries from Table.
DoCmd.OpenQuery "qappTable" 'Append all entries from your query to
Table.
DoCmd.SetWarnings True 'Turn warnings on.
DoCmd.OpenTable "Table" 'Open Table.
DoCmd.RunSavedImportExport str 'Run the saved export for the person
whose name was entered in TextBox.

End If

Close_OK_Click:
DoCmd.Close acForm, "Form" 'Close Form.

Exit Sub

Err_OK_Click:
Select Case Err.Number
Case 2302 'This error will pop up if the Excel Spreadsheet towards
which you are trying to export data is open.
MsgBox "Error 2302: Microsoft Access can't export the query you've
selected to Excel." & vbCr & _
"Please make sure that the Excel file towards which you are trying to
export data is closed."
Case 3349 'This error will pop up if someone has entered text in a
date or number field on the New Admits Excel Spreadsheet.
MsgBox "Error 3349: Data mismatch. Please check the New Admits Excel
Spreadsheet" & vbCr & _
"to make sure that text has not been entered into a number or date
field for your students."
Case 31602 'This error will pop up if there are no saved exports for
the APS name entered.
Select Case MsgBox("No export subroutines exist for " & str & ". Do
you wish to create one?", vbQuestion + vbYesNo)
Case vbYes
DoCmd.RunCommand acCmdExportExcel 'Runs the Export to Excel wizard.
GoTo Close_OK_Click
Case vbNo
MsgBox "You will need to create an export subroutine before you can
export data to Excel."
GoTo Close_OK_Click
End Select
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
End Select
End Sub
 

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

Top