Announcement

Collapse
No announcement yet.

SaveAs method of Workbook class failed

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SaveAs method of Workbook class failed

    Hi All

    As part of the daily run, my script will pull a report, format it in excel and save it in the web site. The code I am using is:

    Code:
    objExcel.ActiveWorkbook.SaveAs(file name and path), 43
    But daily I am getting an error saying - "SaveAs method of Workbook class failed". And again I had to trigger the same and this I have to do for 2 - 3 times and then in some of the runs i will get the success.
    Not sure how to handle the same. Please do suggest.
    NOTE: I am using Office 2003
    Last edited by biggles77; 1st June 2009, 13:38. Reason: Wrap text in CODE

  • #2
    Re: SaveAs method of Workbook class failed

    What is the file name and path? Do you have permission?

    Thread reported for move to more appropriate forum
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: SaveAs method of Workbook class failed

      File name and path is - web link with the current excel file name. And yes I do have permission on the web to save the files.

      Comment


      • #4
        Re: SaveAs method of Workbook class failed

        Yes, but what is the exact syntax you are using (change the domain name if you want)?
        Will you also post the whole of the macro code in case it is something else causing the problem
        Tom Jones
        MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
        PhD, MSc, FIAP, MIITT
        IT Trainer / Consultant
        Ossian Ltd
        Scotland

        ** Remember to give credit where credit is due and leave reputation points where appropriate **

        Comment


        • #5
          Re: SaveAs method of Workbook class failed

          here is the code what i am using:

          Code:
          For i 1 to 20
          objExcel1.Cells(1,i).Activate
          Set objRange = objExcel1.ActiveCell.EntireColumn
           objRange.AutoFit()
          Next
           
          'Insert Rows at the top
          For i=0 to 3
           objExcel1.Cells(1,1).Activate
           Set objRange = objExcel1.ActiveCell.EntireRow
           objRange.Insert
          Next
           
          'Insert the Tittle for the Report
          var = Month(Now)
          var_Month_Name = MonthName(var, false)
          var = Day(Now)
          var_Year = Year(Now)
          objExcel1.Cells(1,6).Value = "Defect Report with Aging for " & var_Month_Name & " " & var & ", " & var_Year
          objExcel1.Cells(1,6).Font.Bold = true
           
          'Insert the Logo
          objExcel1.Range("A1").Select()
          objExcel1.ActiveSheet.Pictures.Insert(DataTable("in_Logo_File_Name_And_Path", dtGlobalSheet)).Select  ' here we are getting the logo needs to be pasted
           
          'Format the Age(Days) col to display days rounded to 2 decimal places
          objExcel1.Cells(1,13).Activate
          Set objRange = objExcel1.ActiveCell.EntireColumn
          objRange.NumberFormat = "#0.00"
           
          'Insert Auto Filter
          objExcel1.Range("A5","M5").AutoFilter
          'Select the 1st Cell
          objExcel1.Cells(5,1).Activate
          objExcel1.DisplayAlerts = False
           
          'http://isis.tsl.telus.com/imtmgt/docs/Wireless/May_AgingDefectReport.xls
          objExcel1.ActiveWorkbook.SaveAs DataTable("in_Web_URL_And_File_Name", dtGlobalSheet), 43 'I am using Excel 2003
           
          workBook1.Close true
          Set objExcel1 = Nothing
          Set workBook1 = Nothing
          Last edited by biggles77; 1st June 2009, 13:39. Reason: Wrap text in CODE

          Comment


          • #6
            Re: SaveAs method of Workbook class failed

            What is this post doing in the SUGGESTIONS and FEEDBACK forum? Moved.
            Cheers,

            Daniel Petri
            Microsoft Most Valuable Professional - Active Directory Directory Services
            MCSA/E, MCTS, MCITP, MCT

            Comment


            • #7
              Re: SaveAs method of Workbook class failed

              Originally posted by danielp View Post
              What is this post doing in the SUGGESTIONS and FEEDBACK forum? Moved.
              My fault, scroll wheel slipped when I moved it and didn't notice.
              1 1 was a racehorse.
              2 2 was 1 2.
              1 1 1 1 race 1 day,
              2 2 1 1 2

              Comment


              • #8
                Re: SaveAs method of Workbook class failed

                Assuming the "in_Web_URL_And_File_Name" is http://... that's the reason. It doesn't know how to save there.

                Is this document located on Sharepoint? If so, when you open files from Sharepoint, even though it's accessed via the website, Excel thinks it's located on \\servername\etc\.
                ** Remember to give credit where credit is due and leave reputation points where appropriate **

                Comment

                Working...
                X