SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16
  1. #1
    Member
    Join Date
    Apr 2007
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Saving Excel Sheet using Script thows message

    when I open a excel file to read using the script it gives me Message that the file is already open.. and gives option to say Yes and No. It is from the network path that I am trying to access the file.. gave full permissions..shared the file but no go, it was working fine till date and suddenly it started giving me this problem. I also tried copying it to a local drive still no go. The line on which it throws the error is when I am trying to save the file after opening it.

  2. #2
    Moderator ifraser's Avatar
    Join Date
    Jul 2004
    Location
    Brisbane, Australia
    Posts
    2,090
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Saving Excel Sheet using Script thows message

    Do you have any drop down lists in your excel file?
    You can buy my Art from: "Post Cards now available"
    Ian Fraser Landscape Photography
    World Wide Shipping.

    http://mowogman.wordpress.com/

  3. #3
    Member
    Join Date
    Apr 2007
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Saving Excel Sheet using Script thows message

    Nope.. no drop down lists at all...just the plane text.

  4. #4
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0

    Re: Saving Excel Sheet using Script thows message

    Use

    SystemUtil.CloseProcessByName "excel.exe"
    before opening the excel application.

  5. #5
    Member
    Join Date
    Apr 2007
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Saving Excel Sheet using Script thows message

    I am using this function to kill the excel application before opening it .. but still no go..

    strComputer = "."
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colProcesses = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = 'Excel.exe'")
    For Each objProcess in colProcesses
    objProcess.Terminate()
    Next

    Even just tried with the one that you gave tarun .. still the same...anything that I am overlooking... given full control to the folder that I am accessing..

  6. #6
    Moderator ifraser's Avatar
    Join Date
    Jul 2004
    Location
    Brisbane, Australia
    Posts
    2,090
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Saving Excel Sheet using Script thows message

    Post some code
    You can buy my Art from: "Post Cards now available"
    Ian Fraser Landscape Photography
    World Wide Shipping.

    http://mowogman.wordpress.com/

  7. #7
    Member
    Join Date
    Apr 2007
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Saving Excel Sheet using Script thows message

    Public fso

    Public sub OpenExcel(FileName)
    Set fso = CreateObject("Excel.Application")
    Set objExcel = fso
    Set objWorkBook = objExcel.Workbooks.Open (FileName)
    End Sub

    Public sub CloseExcel(FileName)
    Set objExcel = fso
    Set objWorkBook = objExcel.Workbooks.Open (FileName)
    'Throws the error in the above line...
    objWorkBook.Save
    objWorkBook.Close
    Set objWorkBook = Nothing
    End Sub

    If I declare objExcel and objWorkBook as global variables and take off 1st two lines in close excel sub it throws me error ... class doesn't support automation..objWorkBook.Save

    It was giving me the same problem when I started coding it .. and when I shared that workbook it stopped and now started again and even when i share the workbook it doesn't help.

  8. #8
    Moderator ifraser's Avatar
    Join Date
    Jul 2004
    Location
    Brisbane, Australia
    Posts
    2,090
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Saving Excel Sheet using Script thows message

    Public sub OpenExcel(sFileName, vSheet)

    Set fso = CreateObject("Excel.Application")
    Set objExcel = fso
    Set objWorkBook = objExcel.Workbooks.Open (sFileName)

    'See if a sheet was referenced in the call. If not, default to the first sheet
    If vSheet = "" Then
    vSheet = 1
    End If

    'Set up a reference to the sheet in the workbook
    Set objWorkSheet = objWorkBook.WorkSheets(vSheet)

    'Create a new sheet if required
    On Error Resume Next
    if objWorkBook.WorkSheets(vSheet) is nothing then

    objWorkBook.WorkSheets.Add
    'objWorkBook.WorkSheets.Select
    newName = objWorkBook.ActiveSheet.Name

    objWorkBook.WorkSheets(newName).Name = vSheet
    'Save the changes
    objWorkBook.Save
    end if

    'Set up a reference to the sheet in the workbook
    Set objWorkSheet = objWorkBook.WorkSheets(vSheet)

    End sub

    Public sub CloseExcel(sFileName)

    Set objExcel = fso
    Set objWorkBook = objExcel.Workbooks.Open (sFileName)

    'Save the changes
    objWorkBook.Save
    objWorkBook.Close

    'Clear all the references to the objects
    Set objWorkBook = Nothing
    objExcel.Quit
    Set objExcel = Nothing
    'reset the distionary object
    Set oDict = Nothing
    sPersistXL = "" 'Global var

    End sub
    You can buy my Art from: "Post Cards now available"
    Ian Fraser Landscape Photography
    World Wide Shipping.

    http://mowogman.wordpress.com/

  9. #9
    Member
    Join Date
    Apr 2007
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Saving Excel Sheet using Script thows message

    Added those lines which were missing in the code, no go...still the same issue.

  10. #10
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0

    Re: Saving Excel Sheet using Script thows message

    Attach your complete code. From function to the code being executed.

 

 
Page 1 of 2 12 LastLast

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 Optimisation provided by DragonByte SEO v2.0.40 (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 11.54%
vBulletin Optimisation provided by vB Optimise v2.7.1 (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.3.0 (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 01:30 PM.

Copyright BetaSoft Inc.