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

Topic

New Reply

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.

Reply | Quote

Replies

Hans, your open & close works for me:

Sub Macro1()
  With Workbooks.Open(Filename:= _
         "C:Documents and SettingssamApplication DataMicrosoftTemplatesRed.xlt", _
         Editable:=True)
     .Sheets("Sheet1").Range("D1").Interior.ColorIndex = 8  ' Update cal table instead
     .Save
     .Close
  End With
End Sub

Probably just syntax. HTH –Sam

Reply | Quote

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.

Reply | Quote

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:

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

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

Reply | Quote

[indent]

---

how do I make this clickable in Lounge?

---

[/indent]Hi Deb,
Have a look at this post

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

> moved to the VB/VBA forum since it’s about code

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.

> if you’re using code to open a workbook how can you deal with a workbook which has macros

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

Reply | Quote

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.

Reply | Quote