?
Solved

Transferspreadsheet duplicate sheet issue on export to ms excel

Posted on 2010-03-31
14
Medium Priority
?
392 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:welsh_mark
  • 5
  • 5
  • 3
13 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 29195792
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
 

Author Comment

by:welsh_mark
ID: 29198092
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 29200587
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:welsh_mark
ID: 29201175
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 29202011
welsh_mark,
do you have data in sheet  { Other_costs }? or is this a blank sheet
0
 
LVL 77

Expert Comment

by:peter57r
ID: 29202093
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 29202928
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
 

Author Comment

by:welsh_mark
ID: 29203309
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 29204638
you can do either of
see my post at http:#a29200587  and  http:#a29202928
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 1000 total points
ID: 29205539
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
 

Author Comment

by:welsh_mark
ID: 29206853
cheers guys i will try these out and get back if there any issues
0
 

Author Comment

by:welsh_mark
ID: 29994412
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 30011927


seen my post at http:#a29200587  and  http:#a29202928
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question