exporting to a specific sheet in a workbook

[dj rivum]

Hi
Im trying to export the data from a table (tbl_A) into a specific sheet in an excel workbook. The sheet name and workbook name will stay the same. I presume the way of doing this will be as a macro in Access, but all i can get it to do so far is to put table as the only sheet in the workbook which overwrites the data held in the other sheets!

Can anybody help on this one

thanks

 

[Norie]

Why don't you import from Access to Excel via Data>Get External Data in Excel?

 

[Tanis]

If you want to do it with automation, try this code. I would use a query to gather the data. Change the query name, table name and path name to suit.

Private Sub Command112_Click()
On Error GoTo Err_Command112_Click


Dim stDocName As String
DoCmd.SetWarnings False

stDocName = "qryTableName"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.SetWarnings True

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conSHT_NAME = "DataSheet"
Const conWKB_NAME = "U:\XL\TestSheet.xls"
Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("qryTableName", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Range("A1").CopyFromRecordset rs
End With
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing


Exit_Command112_Click:
Exit Sub

Err_Command112_Click:
MsgBox Err.Description
Resume Exit_Command112_Click


End Sub

 

[dj rivum]

thanks guys, i think i had got so confused with everything i needed to step away from it and get a second opinoin. so everything sorted now - cheers

 

[drmojo418]

i think this thread is closed but i need to re open it. i am having the same problem as above, and i tried the code listed and changed what i needed to change when i click the button the query comes up fine but nothing is happening in excel. and also i need to be able to pick what dates i want the query to run for. here is the code i am using:

Private Sub Command2_Click()
On Error GoTo Err_Command112_Click


Dim stDocName As String
DoCmd.SetWarnings False

stDocName = "qrysd_sales"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.SetWarnings True

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conSHT_NAME = "SD_Sales"
Const conWKB_NAME = "c:\my documents\SD_dailySales.xls"
Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("qrysd_sales", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Range("A1").CopyFromRecordset rs
End With
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing


Exit_Command112_Click:
Exit Sub

Err_Command112_Click:
MsgBox Err.Description
Resume Exit_Command112_Click
End Sub

thanks in advance
chad

 

[Legacy 15162]

If you have this attached to a form you can have the user input a begin date range and end date range. instead of a stored query, write the sql string and pass those values to the where statement. if you cut and paste the current SQL in your stored query and post, I can help you out ASAP!

 

[drmojo418]

here is my sql string from my query:

SELECT DateValue([CRCallDateTime]) AS mydate, dbo_InventoryFairfield.ArrivlDate, dbo_InventoryFairfield.CRCallResultCode, dbo_InventoryFairfield.Office, dbo_InventoryFairfield.FirstName, dbo_InventoryFairfield.MiddleInt, dbo_InventoryFairfield.LastName, dbo_InventoryFairfield.SpouseName, dbo_InventoryFairfield.Address, dbo_InventoryFairfield.City, dbo_InventoryFairfield.State, dbo_InventoryFairfield.Zip, dbo_InventoryFairfield.CRAreaCode, dbo_InventoryFairfield.CRExchange, dbo_InventoryFairfield.CRNumber, dbo_InventoryFairfield.BusPhone, dbo_InventoryFairfield.Rate, dbo_InventoryFairfield.VfrCamp, dbo_InventoryFairfield.NoAdults, dbo_InventoryFairfield.NoKids, dbo_InventoryFairfield.Comments1, dbo_InventoryFairfield.CcAmt1, dbo_InventoryFairfield.CcAmt2, dbo_InventoryFairfield.CcAuthorize1, dbo_InventoryFairfield.CcAuthorize2, dbo_InventoryFairfield.CrsID2, dbo_InventoryFairfield.Email, dbo_InventoryFairfield.CRAgentID, dbo_InventoryFairfield.VfrID
FROM dbo_InventoryFairfield
WHERE (((DateValue([CRCallDateTime])) Between DateValue([Forms]![SingleSite_Tracking]![txtStartDt]) And DateValue([Forms]![SingleSite_Tracking]![txtEndDt])) AND ((dbo_InventoryFairfield.CRCallResultCode) Like "S*") AND ((dbo_InventoryFairfield.Office) Like "SD"));


thanks
chad

 

[drmojo418]

bump

 

[drmojo418]

bump2