export to excel from access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a command code that automatically exports a query to excel and
automatically opens and pastes the file into excel while maintaining the
format. When I upgraded from 97 to 2003, it will open excel but not paste
into excel



the code I am using is

Private Sub StoreListXpt_Click()
DoCmd.SetWarnings False
Dim Temp As Integer
DoCmd.OpenQuery "Store List Query"
DoCmd.RunCommand (acCmdSelectAllRecords)
DoCmd.RunCommand (acCmdCopy)
DoCmd.Close acQuery, "Store List Query"
DoCmd.SetWarnings True
Temp = Shell("C:\Program Files\Microsoft Office\Office11\EXCEL.EXE",
vbNormalNoFocus)
AppActivate Temp
SendKeys "%ep"
SendKeys "%owe{RIGHT}{TAB}{TAB}{TAB}{TAB}"
SendKeys " ~"
SendKeys "%oca"
End Sub
 
Do yourself a huge favour, and look up the TransferSpreadsheet method.

Using SendKeys is a disaster waiting to happen!
 
Aaaargh! SendKeys....don't do it!!
If another window gets focus somehow, the keys will be sent to that and
really mess things up!

As you have already been advised...use DoCmd.TransferSpreadsheet

Steve
 
Back
Top