run auto_open after the workbook is open

Question

I am trying to create a code that runs when the workbook is opened. I try with Auto_open and workbook_open (inside ThisWorkbook object) But I have a problem with both of them. The problem is that the code needs info from the workbook that I am opening As you can see in this peace of code:

Sub Auto_Open()
Dim fileNam As String
Dim text As String
Dim answer As String
Dim question As String
Dim quesPos As Integer
MsgBox "add-in start"
'On Error GoTo GetOut


fileNam = ThisWorkbook.FullName
jsonFile = Replace(jsonFile, "xls", "survey.descriptor.json")
Open jsonFile For Input As #2
pos = 1
ThisWorkbook.Sheets("Result").Select
'The code gives the error here

As the excel file is not already opened it gives an error while getting the name of the file. How can I make to exec the code when it opens but after it is opened?

The Workbook_Open() macro seems to work with your code for me with the code that you posted above. — ChrisProsser
I test with the workbook_open() but it was not working... I have to say that I am creating an Add-In with that code and then add it to excel. ThisWorkbook.FullName is working but the code continues and it gives me another error. I will put more code to say where it give me the error — Iban Arriola

Floris Floris · Accepted Answer · 2013-07-10T17:46:19

If you need some code to run AFTER the workbook is open (rather than "as it is being opened"), one solution would be to create a timed event - set the timer for 5 seconds, trigger it in the auto_open, and have it loop until the file is "properly open". This might look something like this:

In Auto_Open():

fullyOpenTime = Now + TimeValue("00:00:05")
Application.OnTime alertTime, "LetsGo"

Then you create another sub that will run once the workbook is open:

Sub LetsGo()

On Error Resume Next

' loop around until no error was triggered
Do
  DoEvents
  fileName = Application.ActiveWorkbook.FullName
  Application.Wait DateAdd("s", 1, Now)  ' "cheap trick" to wait one second
While Err.Number <> 0

On Error GoTo 0
' when you get here, you have an active workbook.

run auto_open after the workbook is open

1 Answers