Transferspreadsheet duplicate sheet issue on export to ms excel

I am exporting from access 2003 app running in access 2007. I am exporting several cross tab queries, which i am trying to send to specfic worksheets in a template excel 2003. The script works to a point in that data is transfered, but instead of the 1st worksheet tab "Other_costs" it is populating a new worksheet "Other_costs1". I have previously set up range names in the Other_costs worksheet a1:cz2000 and named the range Other_costs the same as the worksheet and the vba parameter.

By way of note im no vba programmer but an accountant who knows for my sins access but this is a bit beyond my skill set. so please be gentle with me. :-)
Sub Run_Create_Stream_Forecast_Spreadsheets_New()
On Error GoTo Err_Create_Stream_Forecast_New


    Dim lngRow As Long
    Dim lngTotTasks As Long
    Dim lngTotResources As Long
    Dim lngCountResources As Long
    Dim TargetRow As Long
    Dim strPath As String
    'Dim colrows As Range
    Dim shtTasks As Variant
    Dim dbstemp As DAO.Database
    Dim rstData As DAO.Recordset
    Dim rstGroup As DAO.Recordset
    Dim rstProject As Recordset
    Dim strSQL As String
    Dim intpos As Integer
    'Dim cThis As Range
    Dim firstAddress As Variant
    Dim x As String
    Dim ProjectStream As String
    Dim Project As String
    Dim EndDate As Date

       
    strPath = strFolderName
    
     Set dbstemp = CurrentDb()
     
        Set rstProject = dbstemp.OpenRecordset("01_Project_Req", dbOpenDynaset)
        
        Set rstGroup = dbstemp.OpenRecordset("01_Project_Stream_Resource_Chargeable_Hrs_Sel_Crosstab", dbOpenDynaset)
        strSQL = _
        "SELECT Project_ID, Stream " & _
        "FROM 01_Project_Stream_Resource_Chargeable_Hrs_Crosstab " & _
        "GROUP BY Project_ID, Stream"

        Set rstGroup = dbstemp.OpenRecordset(strSQL, dbOpenSnapshot)
        rstGroup.MoveFirst
        
Next_ProjectStream:
        
        
        If rstGroup.EOF = True Then
            GoTo Save_Last_Spreadsheet
        End If
        
        Project = rstGroup.Fields("Project_ID").Value
        ProjectStream = rstGroup.Fields("Stream").Value
        
        With rstProject
            .Edit
            !Project_Req = Project
            .Update
        End With


        
'***********   Create a Project Steam Forecast Workbook  **************************
    FileSystem.FileCopy "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output_Template.xls", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls"
   ' FileSystem.FileCopy "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output_Templatemr.xls", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Test1.xls"
'******************* Other Costs ***************************

   DoCmd.TransferSpreadsheet acExport, , "01_Project_Costs_Forecast_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "Other_costs"
 
    
    
    
'******************* Chargeable Hours ***************************

   DoCmd.TransferSpreadsheet acExport, , "01_Project_Stream_Resource_Chargeable_Hrs_Sel_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "Hours_Chargeable"
  
 '******************* Non Chargeable Hours ***************************

    DoCmd.TransferSpreadsheet acExport, , "01_Project_Stream_Resource_Non_Chargeable_Hrs_Sel_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "Hours_Non_Chargeable"
       
'******************* Expense ***************************

    DoCmd.TransferSpreadsheet acExport, , "01_Project_Stream_Resource_Expense_Sel_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "Expense"

   FileSystem.FileCopy "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Forecast to Distribute\" & Project & "_" & ProjectStream & "_Monthly_Forecast_Output.xls "
  
'******************* Run Excel Macro Set_Colour ***************************
    
SET_COLOUR:
   Dim xlsApp As Excel.Application
    Dim xlswkb As Excel.Workbook
    Set xlsApp = CreateObject("Excel.Application")

    Forecast_File = "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Forecast to Distribute\" & Project & "_" & ProjectStream & "_Monthly_Forecast_Output.xls"
        xlsApp.Workbooks.Open Forecast_File
    xlsApp.Run "Set_Colour"
    xlsApp.ActiveWorkbook.Save
    xlsApp.Workbooks.Close
    xlsApp.Quit
    
    rstGroup.MoveNext

   GoTo Next_ProjectStream:
    



'************** Save Workbook *****************************************
Save_Last_Spreadsheet:
        MsgBox ("FORECAST NOW CREATED")
Exit_Create_Stream_Forecast_New:
    
    Exit Sub

Err_Create_Stream_Forecast_New:
    MsgBox Err.Number & " - " & Err.Description & " - " & Err.Source
    
End Sub

Function Start_Create_Stream_Forecast_Spreadsheet_New()

Run_Create_Stream_Forecast_Spreadsheets_New

End Function

Open in new window

welsh_markAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Specify a complete range - as long as it is largger than you might need then it is OK.

DoCmd.TransferSpreadsheet acExport, , "01_Project_Stream_Resource_Chargeable_Hrs_Sel_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "Hours_Chargeable!A1:z5000"



0
welsh_markAuthor Commented:
Hi thanks for getting back, i have tried that but now get

error 3010 - table 'Other_costs$a1:az5000' already exists - db20

updated code attached

   DoCmd.TransferSpreadsheet acExport, , "01_Project_Costs_Forecast_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "Other_costs!a1:az5000"
 
    
    
    
'******************* Chargeable Hours ***************************

   DoCmd.TransferSpreadsheet acExport, , "01_Project_Stream_Resource_Chargeable_Hrs_Sel_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "Hours_Chargeable!a1:az5000"
  
 '******************* Non Chargeable Hours ***************************

    DoCmd.TransferSpreadsheet acExport, , "01_Project_Stream_Resource_Non_Chargeable_Hrs_Sel_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "Hours_Non_Chargeable!a1:az5000"
       
'******************* Expense ***************************

    DoCmd.TransferSpreadsheet acExport, , "01_Project_Stream_Resource_Expense_Sel_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "Expense!a1:az5000"

   FileSystem.FileCopy "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Forecast to Distribute\" & Project & "_" & ProjectStream & "_Monthly_Forecast_Output.xls "
  

Open in new window

0
Rey Obrero (Capricorn1)Commented:
welsh_mark,

are there any existing data in the worksheet { Other_costs }?

if this is a blank worksheet, and since you are starting at cell A1, delete the worksheet and use

   DoCmd.TransferSpreadsheet acExport, , "01_Project_Costs_Forecast_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "Other_costs"

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

welsh_markAuthor Commented:
hi capricorn,

Yes there are sheets where i want to place the data into from excel based on my query, what is happening is that it is not putting this data i require into Other_costs like i intedned it should but is creating Other_costs1 worksheet with data and named range. I need it in worksheet Other_costs as this data drives the rest of the formulas in other sheets.
0
Rey Obrero (Capricorn1)Commented:
welsh_mark,
do you have data in sheet  { Other_costs }? or is this a blank sheet
0
peter57rCommented:
The test I ran earlier was in A2003 and it worked fine.

I have done the same thing in A2007 and although it works in a way it is not correct.
I am finding that if I run the export amore than once time it does not use the range I have specified, but starts a few rows and columns in.  
However I can't replicate the error you are seeing.




0
Rey Obrero (Capricorn1)Commented:
if you want to keep your sheet { Other_costs }, rename your  range Name with
something different than { Other_costs }, say x_Costs, then use the codes below


 DoCmd.TransferSpreadsheet acExport, , "01_Project_Costs_Forecast_Crosstab", "T:\DVLA Management\DVLA FM\DB_PROJECT FORECAST\Project_Stream_Monthly_Forecast_Output.xls", True, "x_costs"
0
welsh_markAuthor Commented:
hi capricorn yes there is some formatting in there shading in row a1 but no data per se.

Hi Peter thats where i got the issue the db is access 2002-2003 database then i run it in access 2007 and export it to excel 2003, its got me in a right mess.
0
Rey Obrero (Capricorn1)Commented:
you can do either of
see my post at http:#a29200587  and  http:#a29202928
0
peter57rCommented:
What I have found now is that if I have the column headings in place in the worksheet, the export runs correctly every time.  Without the columns heads, if I just clear  the data from the sheet, then the next export will place the data in a different range.
0
welsh_markAuthor Commented:
cheers guys i will try these out and get back if there any issues
0
welsh_markAuthor Commented:
hi guys i finally got it to work by deleting the original tabs and then creating them once again and saving the file. go figure lol.
0
Rey Obrero (Capricorn1)Commented:


seen my post at http:#a29200587  and  http:#a29202928
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.