Results 1 to 2 of 2

Excel SaveAs not working

Threaded View

  1. #1
    Registered User
    Join Date
    11-10-2009
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    1

    Excel SaveAs not working

    I am running an Excel 2007 vba program from dos (using cscript). The program opens an Excel file that has a fileformat of txt. It uses the data in the file to create two charts for each day. So far everything works except when saving the file. I would ultimately like to save it back with the same name, but needing to use a different fileformat so that the charts will display. I have tried several combinations of the saveas command. The weird thing is, when I use the := in the saveas command, the program doesn't even open Excel - but when I use only = in the command it opens the file and creates the charts. Here is the program:

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    Set objWorkbook = objExcel.Workbooks.Open("Z:\Num_Doc_BP_2009_test.xls")
    
    
    '***************************************************************************
    ' Create area charts for each day's Number of Documents.                   *
    '***************************************************************************
    
    start_value = 2
    end_value = 25
    
    range_value = "='" & objWorkbook.ActiveSheet.Name & "'!$C$" & start_value & ":$C$" & end_value & ""
        
    Do While objWorkbook.ActiveSheet.Cells(end_value,1).Value <> ""
    
        x_value = "='" & objWorkbook.ActiveSheet.Name & "'!$B$" & start_value & ":$B$" & end_value & ""
        
        objWorkbook.ActiveSheet.Shapes.AddChart.Select
        objWorkbook.ActiveChart.ChartType = 76
        
        chart_range = "='" & objWorkbook.ActiveSheet.Name & "'!$F$" & start_value & ":$P$" & end_value & ""
            
        Set RngToCover = objWorkbook.ActiveSheet.Range(chart_range)
        Set ChtOb = objWorkbook.ActiveChart.Parent
        
        ChtOb.Height = RngToCover.Height ' resize
        ChtOb.Width = RngToCover.Width ' resize
        ChtOb.Top = RngToCover.Top ' reposition
        ChtOb.Left = RngToCover.Left ' reposition
    
        objWorkbook.ActiveChart.SeriesCollection.NewSeries
        objWorkbook.ActiveChart.SeriesCollection(3).Name = "='" & objWorkbook.ActiveSheet.Name & "'!$C$1"
        objWorkbook.ActiveChart.SeriesCollection(3).Values = range_value
        objWorkbook.ActiveChart.SeriesCollection(4).Delete
        objWorkbook.ActiveChart.SeriesCollection(2).Delete
        objWorkbook.ActiveChart.SeriesCollection(1).Delete
        objWorkbook.ActiveChart.SeriesCollection(1).XValues = x_value
        
        
        start_value = start_value + 24
        end_value = end_value + 24
        
        range_value = "='" & objWorkbook.ActiveSheet.Name & "'!$C$" & start_value & ":$C$" & end_value & ""
        
        Loop
        
    '***************************************************************************
    ' Create area charts for each day's Number of Business Processes.          *
    '***************************************************************************
        
    start_value = 2
    end_value = 25
    
    range_value = "='" & objWorkbook.ActiveSheet.Name & "'!$D$" & start_value & ":$D$" & end_value & ""
        
    Do While objWorkbook.ActiveSheet.Cells(end_value,1).Value <> ""
    
        x_value = "='" & objWorkbook.ActiveSheet.Name & "'!$B$" & start_value & ":$B$" & end_value & ""
        
        objWorkbook.ActiveSheet.Shapes.AddChart.Select
        objWorkbook.ActiveChart.ChartType = 76
        
        chart_range = "='" & objWorkbook.ActiveSheet.Name & "'!$R$" & start_value & ":$AA$" & end_value & ""
            
        Set RngToCover = objWorkbook.ActiveSheet.Range(chart_range)
        Set ChtOb = objWorkbook.ActiveChart.Parent
        
        ChtOb.Height = RngToCover.Height ' resize
        ChtOb.Width = RngToCover.Width ' resize
        ChtOb.Top = RngToCover.Top ' reposition
        ChtOb.Left = RngToCover.Left ' reposition
    
        objWorkbook.ActiveChart.SeriesCollection.NewSeries
        objWorkbook.ActiveChart.SeriesCollection(3).Name = "='" & objWorkbook.ActiveSheet.Name & "'!$D$1"
        objWorkbook.ActiveChart.SeriesCollection(3).Values = range_value
        objWorkbook.ActiveChart.SeriesCollection(4).Delete
        objWorkbook.ActiveChart.SeriesCollection(2).Delete
        objWorkbook.ActiveChart.SeriesCollection(1).Delete
        objWorkbook.ActiveChart.SeriesCollection(1).XValues = x_value
        
        
        start_value = start_value + 24
        end_value = end_value + 24
        
        range_value = "='" & objWorkbook.ActiveSheet.Name & "'!$D$" & start_value & ":$D$" & end_value & ""
        
        Loop
    
    
    objExcel.ActiveWorkbook.SaveAs Filename:="C:\Num_Doc_BP_2009_test.xlsx", FileFormat:=xlOpenXMLWorkbook
    Last edited by ztm0187; 11-10-2009 at 01:14 PM. Reason: added code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1