By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,316 Members | 2,355 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,316 IT Pros & Developers. It's quick & easy.

Write to Excel Sheet using VBA code

P: 77
Hi,

Below is the VBA code I am using to accept Age and Year from a form and then execute a Make Table Query, QryGetAllCases to create the table FormSelectCases.
Everything is working fine.

Now I want to write the same result to an Excel Spreadsheet also after creating the table. I am sure lot of you might have done the same. So please shed some light.

Private Sub Request_List_Click()

Dim parm1 As Parameter
Dim parm2 As Parameter
Dim db As Database
Dim qd As QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs![QryGetAllCases]
Set parm1 = qd.Parameters![ParmYr]
Set parm2 = qd.Parameters![ParmAge]
qd.Parameters(0).Value = ParmYr.Value
qd.Parameters(1).Value = ParmAge.Value

For Each tdf In db.TableDefs
If tdf.Name = "FormSelectCases" Then
DoCmd.DeleteObject acTableDef, "FormSelectCases"
End If
Next tdf

If IsNull(ParmAge.Value) Then
MsgBox "Please Enter a Age"
Exit Sub
End If

If IsNull(ParmYr.Value) Then
MsgBox "Please Select a Year"
Exit Sub
End If

DoCmd.Hourglass True
qd.Execute

DoCmd.Hourglass False
ParmYr.Value = Nothing
ParmAge.Value = Nothing

Exit Sub

End Sub


Thanks
Jan 24 '08 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,679
Hi,

Below is the VBA code I am using to accept Age and Year from a form and then execute a Make Table Query, QryGetAllCases to create the table FormSelectCases.
Everything is working fine.

Now I want to write the same result to an Excel Spreadsheet also after creating the table. I am sure lot of you might have done the same. So please shed some light.

Private Sub Request_List_Click()

Dim parm1 As Parameter
Dim parm2 As Parameter
Dim db As Database
Dim qd As QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs![QryGetAllCases]
Set parm1 = qd.Parameters![ParmYr]
Set parm2 = qd.Parameters![ParmAge]
qd.Parameters(0).Value = ParmYr.Value
qd.Parameters(1).Value = ParmAge.Value

For Each tdf In db.TableDefs
If tdf.Name = "FormSelectCases" Then
DoCmd.DeleteObject acTableDef, "FormSelectCases"
End If
Next tdf

If IsNull(ParmAge.Value) Then
MsgBox "Please Enter a Age"
Exit Sub
End If

If IsNull(ParmYr.Value) Then
MsgBox "Please Select a Year"
Exit Sub
End If

DoCmd.Hourglass True
qd.Execute

DoCmd.Hourglass False
ParmYr.Value = Nothing
ParmAge.Value = Nothing

Exit Sub

End Sub


Thanks
Try inserting the following line of code after qd.Execute, substituting your Excel Version and Absolute File Path:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "FormSelectCases", "C:\Test\FormSelectCases.xls", True
Jan 25 '08 #2

P: 77
Try inserting the following line of code after qd.Execute, substituting your Excel Version and Absolute File Path:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "FormSelectCases", "C:\Test\FormSelectCases.xls", True

Thanks ADezii...It worked.

I would like to ask you one more help. Once the excel sheet is created is it possible to open the same, so that users don't have to hunt for it. Is there a command that I can use in my vba code to open this excel sheet.

Thanks,
Jan 25 '08 #3

ADezii
Expert 5K+
P: 8,679
Thanks ADezii...It worked.

I would like to ask you one more help. Once the excel sheet is created is it possible to open the same, so that users don't have to hunt for it. Is there a command that I can use in my vba code to open this excel sheet.

Thanks,
After you create the Excel Spreadsheet, you can Open via:
Expand|Select|Wrap|Line Numbers
  1. 'Adjust you Paths to Excel and your Spreadsheet
  2. Dim retVal
  3. retVal = Shell("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE C:\Test\FormSelectCases.xls", vbMaximizedFocus)
Jan 25 '08 #4

P: 77
After you create the Excel Spreadsheet, you can Open via:
Expand|Select|Wrap|Line Numbers
  1. 'Adjust you Paths to Excel and your Spreadsheet
  2. Dim retVal
  3. retVal = Shell("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE C:\Test\FormSelectCases.xls", vbMaximizedFocus)

Thanks again ADezii, your code worked, helped me a lot.
I was wondering, would it be possible to bring the Save As pop-up box after the spreadsheet is opended, so that users can save the spreadsheet any place they want to, instead of me saving it in a specific folder.

Thanks
Jan 28 '08 #5

ADezii
Expert 5K+
P: 8,679
Thanks again ADezii, your code worked, helped me a lot.
I was wondering, would it be possible to bring the Save As pop-up box after the spreadsheet is opended, so that users can save the spreadsheet any place they want to, instead of me saving it in a specific folder.

Thanks
Save As would simply Save the same Spreadsheet to another location and possibly under a different Name. Why the duplication?
Jan 28 '08 #6

P: 77
Save As would simply Save the same Spreadsheet to another location and possibly under a different Name. Why the duplication?
Actually the users want to save it to a location they want to. So I have to give them that option instead of saving to the location I specify.
Is it possible to bring up the Save File Dialog box through the VBA code?

Thanks
Jan 28 '08 #7

P: 77
Actually the users want to save it to a location they want to. So I have to give them that option instead of saving to the location I specify.
Is it possible to bring up the Save File Dialog box through the VBA code?

Thanks
Hi Dezii..

Please help me as this is very urgent, needs to be finished tomorrow.

Thanks a bunch
Jan 29 '08 #8

ADezii
Expert 5K+
P: 8,679
Hi Dezii..

Please help me as this is very urgent, needs to be finished tomorrow.

Thanks a bunch
I am showing you this approach only because it is the simplest, and because of the sense of urgency that you indicated. It is not advisable to approach this in this manner and it is not the solution which I would recommended. If you are proceeding along this line, you 'must' incorporate Error Checking wherever you place this code, because of the strong possibility of Path/File Errors.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnEnter_Click()
  2. On Error GoTo Err_btnEnter_Click
  3. Dim strPathToSave As String, strMsg As String
  4.  
  5. strMsg = "ENTER an Absolute Path/Filename for the Excel Spreadsheet, e.g. " & _
  6.          vbCrLf & vbCrLf & "(C:\Windows\System32\SomeSheet.xls)"
  7.  
  8. strPathToSave = InputBox$(strMsg, "Spreadsheet Path")
  9.  
  10. If Len(strPathToSave) > 0 Then
  11.   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "FormSelectCases", strPathToSave, True
  12. End If
  13.  
  14. Exit_btnEnter_Click:
  15.   Exit Sub
  16.  
  17. Err_btnEnter_Click:
  18.   MsgBox Err.Description, vbExclamation, "Error in btnEnter_Click()"
  19.   Resume Exit_btnEnter_Click
  20. End Sub
Jan 30 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.