Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing to a workbook without opening (Excel 97/2000)

    Is there a way to write a value to a sheet in a workbook without actually opening the workbook? What I mean is: using a macro from a open workbook, is it possible to write something in a sheet from another workbook without opening that workbook? I thought I read a post somewhere dealing with this.

    I have a template with - on a hidden sheet - the values of a standard series that I use for fitting a calibration curve. I want the user to have the possibility to change the values of the standard series. But I want him/her to have the possibility to change it in the template from the open workbook generated from that template. I hope I am clear enough? I tried to do that with Workbooks("Fit.xlt").Open Editable:=True and then do the changes and then use Workbooks("Fit.xlt").Close.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing to a workbook without opening (Excel 97/2000)

    Hans, your open & close works for me: <pre>Sub Macro1()
    With Workbooks.Open(Filename:= _
    "Cocuments and SettingssamApplication DataMicrosoftTemplatesRed.xlt", _
    Editable:=True)
    .Sheets("Sheet1").Range("D1").Interior.ColorIndex = 8 ' Update cal table instead
    .Save
    .Close
    End With
    End Sub</pre>

    Probably just syntax. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to a workbook without opening (Excel 97/2000)

    Thanks Sam,

    It works indeed! My problem was that the template contains macros in the Workbook_Open event. Therefore, I used the Editable:=True in the Workbook.Open statement, as the VBA Help says:

    " If the file isn't an add-in, True prevents the running of any Auto_Open macros."

    However, Workbook_Open doesn't seem to be an Auto_Open event. There was a small error in my workbook_open event concerning the activation of the right worksheet in my template.
    Strange thing is that when opening a workbook that contains macros with the commandline Workbooks.Open, the usual message "This file contains macros ...." does not appear.

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to a workbook without opening (Excel 97/2000)

    FYI. Auto_Open is the old style and Workbook_Open replaced it, however I tend to use both just in case the events got disabled. Auto_Open will run after Workbook_Open IF events are turned on, otherwise only Auto_Open runs. So I have a global variable that tells me if WB open event got run and if it's false then I run Auto_Open which kick starts WB Open code. It then sets the global flag to true (WBOpen = True/False). Like this:

    <pre>public sub auto_open()
    if not DidWBOpen then
    application.enableevents = true
    Call WBOpen_Code
    end if
    end sub


    public sub workbook_open()
    DidWBOpen = True
    Call WBOpen_Code
    end sub
    </pre>

    Now to your question, check out this link from John Walkenbach's site:
    http://j-walk.com/ss/excel/tips/tip82.htm (how do I make this clickable in Lounge?)

    It's code to read cells from a closed workbook. I wonder if it works if the workbook is protected - that's something I've been meaning to try for a while.

    Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to a workbook without opening (Excel 97/2000)

    <hr>how do I make this clickable in Lounge?<hr>
    Hi Deb,
    Have a look at <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=lounge&Number=98472&pag e=&view=&sb=&o=&vc=1#Post98472> this post</A>
    Francois

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to a workbook without opening (Excel 97/2000)

    I think this thread should be moved to the VB/VBA forum since it's about code.

    Anway, my question here is if you're using code to open a workbook how can you deal with a workbook which has macros in it? I mean how do you get around (respond to) the macro disable/enable message box that shows up? I assume this hasn't happened to you since your workbook doesn't contain code? None of the responses I've seen here or in books mention that possibility. I'd think that since you can't disable that message box, that you can't also bypass it by opening it via code.

    Deb <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing to a workbook without opening (Excel 97/2000)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > moved to the VB/VBA forum since it's about code
    <img src=/w3timages/blackline.gif width=33% height=2>
    All of us read both boards, so either place is fine. I actually respond first to VBA questions on the Excel board, then if I have time, I look at the VBA board.

    <img src=/w3timages/blackline.gif width=33% height=2>
    > if you're using code to open a workbook how can you deal with a workbook which has macros
    <img src=/w3timages/blackline.gif width=33% height=2>
    I added a macro to the red template that I was testing with above and didn't get the macro warning message. I suspect that you only get the warning with a manual open. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to a workbook without opening (Excel 97/2000)

    I've got the same experience. When you open a workbook containing macros via code, you don't get the macro warning message.
    I thought that using the Editable:= True option, macros in the Workbook_Open would not be fired, but apparently they are.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to a workbook without opening (Excel 97/2000)

    Thanks Deb, this is a good idea, didn't thought of it. When I want to change the values in a sheet of the template, I first disable the events and after changing the values I enable the events back again.

Posting Permissions

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