Find workbook name when macro run in add-in file

[Ago]

Didnt know what to use as a title but that kind of explains it.

The problem is that i made a macro that i want to run every time excel starts.
The macro should find out if its a csv-file and meets other requirements and then ask to complete the macro.

The code works if i run it as a modulecode but when i make it a add-in file that runs automaticly it cant find the name of the file.
But if i stop the code and then run it again, it works.

The code stops at the line extension=mid(.... because wkbName is empty.
Any ideas?


Private Sub Workbook_Open()
Dim A As Double
Dim wkbName As String
Dim wkb As Workbook

For Each wkb In Workbooks 'Find the name of the file
wkbName = wkb.Name
Next

For i = Len(wkbName) To 2 Step -1 'Where is the dot in filename
c = Mid(wkbName, i, 1)
If c = "." Then
pos = i + 1
Exit For
End If
Next

extension = Mid(wkbName, pos, (Len(wkbName) + 1 - pos)) 'Remove filename, but keep extension


If extension = "csv" And Range("A1").Value = "LATITUDE" And Range("B1").Value = "LONGITUDE" And Range("C1").Value = "ALTITUDE" And Range("D1").Value = "SPEED" Then
Response = MsgBox(prompt:="Run GPS-Script?", Buttons:=vbYesNo)
If Response = vbNo Then
Exit Sub
End If
Else
Exit Sub
End If
Columns(1).Insert
Rows(2).Insert
Rows(2).Insert
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("B4", "E" & LastRow).NumberFormat = "@"
For Each Dcell In Range("B4", "E" & LastRow)
A = Replace(Dcell.Value, ".", ",")
Dcell.Value = A
Next
Columns(4).Insert
Range("D:D").NumberFormat = "0"
Range("D5").Value = "=ACOS(COS(RADIANS(90-B4)) *COS(RADIANS(90-B5)) +SIN(RADIANS(90-B4)) *SIN(RADIANS(90-B5)) *COS(RADIANS(C4-C5))) *6371000"
Range("D6").Value = "=D5+ACOS(COS(RADIANS(90-B5)) *COS(RADIANS(90-B6)) +SIN(RADIANS(90-B5)) *SIN(RADIANS(90-B6)) *COS(RADIANS(C5-C6))) *6371000"
Range("D6", "D" & LastRow).FillDown
Columns(6).Insert
Range("E:G").NumberFormat = "0"
Range("F5").Value = "=((E4-E5)/1000)*60*60"
Range("F5", "F" & LastRow).FillDown
Columns(8).Insert
Range("H:H").NumberFormat = "0.000"
Range("H5").Value = "=G5/F5"
Range("H5", "H" & LastRow).FillDown
Range("I:I").TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, Other:=True, OtherChar:="Z"
Range("I:I").TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, Other:=True, OtherChar:="T"
Range("A1", "K1").ClearContents
Range("B1").Value = "Latitude"
Range("C1").Value = "Longitude"
Range("D1").Value = "H-Distance"
Range("E1").Value = "Altitude"
Range("F1").Value = "V-Speed"
Range("G1").Value = "H-Speed"
Range("H1").Value = "Glide"
Range("I1").Value = "Date"
Range("J1").Value = "Time"
Range("A2").Value = "Max"
Range("A3").Value = "Min"

Range("F2").Value = "=MAX(F5:F" & LastRow & ")"
Range("F3").Value = "=MIN(F5:F" & LastRow & ")"
Range("G2").Value = "=MAX(G5:G" & LastRow & ")"
Range("G3").Value = "=MIN(G5:G" & LastRow & ")"
Range("H2").Value = "=MAX(H5:H" & LastRow & ")"
Range("H3").Value = "=MIN(H5:H" & LastRow & ")"
Cells.Columns.AutoFit

End Sub




The csv-file i use:
http://www.hellis.me/testfil.zip

I have crossposted this in a swedish forum, but i dont belive they can help me. I will forward what they say from that forum to here if they say anything

[Aussiebear]

Just post the cross link and we can have a look at any progress there

[Ago]

Didnt know you could read Swedish ;-)
I started that thread because of a different problem but instead of making a new one i just keept the old thread

http://www.sweclockers.com/forum/showthread.php?s=&threadid=910124

[Ago]

I just tried

wkbName = Application.Workbooks(1).Name


But the same thing happens, the macro seems to start before the file has been opend.
So i tried

Application.Wait Now + TimeValue("00:00:05")
wkbName = Application.Workbooks(1).Name


Same thing, the excelwindow is empty and i get a message, "Subscript out of range".

How can i make excel first open the file and then run the automated macro?

[Paul_Hossler]

The macro should find out if its a csv-file and meets other requirements and then ask to complete the macro.


This reads like the macro is a CSV file that meets other requirements???

ThisWorkbook will return that name of the WB that the macro is in, which might be different from ActiveWorkbook.

Paul

[Ago]

Sorry if i was unclear.
What i mean is, when i open a file i want excel to find out if the file i opend is a csv-file.
And if that is true and the other requirements are meet.

If extension = "csv" And Range("A1").Value = "LATITUDE" And Range("B1").Value = "LONGITUDE" And Range("C1").Value = "ALTITUDE" And Range("D1").Value = "SPEED" Then

I want the macro to keep running.


ThisWorkbook will return that name of the WB that the macro is in, which might be different from ActiveWorkbook.


Yes, but the problem is that it seems the macro runs without any workbooks open.
As you can see on the attached picture excel has not loaded the workbook when the code crashes.
If i at this point click debug or end excel loads up the file.

When i use Application.Workbooks(n).Name (after the crash) it tells me only one workbook is open, the csv-file. The addin file called GPS.xlam does not show up as open in workbooks.name.

So anyone has a suggestion on how to "delay" the macro so the workbook opens before the code or a way around this problem?

[mikerickson]

Rather than haveing your Add-In's Open event test for CSV'ness, have it create/instance an Application level WorkbookOpen event. Put the "Is CSV?" code in the WorkbookOpen event.

[ZVI]

Hi,

There are two different questions in your request.

First is in post #1:

"macro that I want to run every time excel starts"
"when i make it a add-in file that runs automatically it cant find the name of the file"

The second one is in the post #6:

“when i open a file i want excel to find out if the file I opened is a csv-file”

As you can see the bold font parts are not the same.

Below is the answer to your first question.


Delayed Workbook_Open or Auto_Open event macro in Add-Ins

Issues’ Abstract

When you open new Excel instance by clicking on Excel shortcut, an empty workbook is automatically created. But after loading of any workbook, that empty one is disappeared.
Usually we pay no regard to this fact, but must do it at using of Workbook_Open or Auto_Open event(s) code in Add-Ins. What if empty workbook is not created? Close it and have a look on menu or ribbon – there are a lot of disabled grayed items in it. Therefore several manipulations with Excel objects are impossible in this case neither by a hand nor by VBA code. This fact can be taken into account at Add-In coding.

Another problem has happen if there are no previously open Excel windows and you are trying to manipulate with loading workbook’s objects/properties/methods by triggering of Add-In’s open events. The code stops with error in this case.


What happens? The Stages

Let's analyzing the actions which happen if there are no open instances of Excel and you click on destination workbook, for example, from explorer window.

1. Click on workbook shortcut/link/item has happen.

2. Excel is open itself (core/windows) first time, but without creating of an empty workbook.
At the end of this stage Application.Ready = True, but Workbooks.Count = 0

3. Add-Ins' are loaded one by others with triggering of its opening events code if present.
But still Workbooks.Count = 0, you can check this fact in Add-In open events code.

4. The empty workbook is created. After that Workbooks.Count = 1 and all Excel options are enabled now.
All Excel objects are acceptable, events can be successfully processed, but destination workbook (see step 1) is still not loaded.

5. Clicked workbook is loaded with auto-closing of the empty workbook and with rising of open/activation events.
We have again Workbooks.Count = 1

6. Here our safe time come. But stage 5 is safety for events triggering of the opening workbook as well.

As we can see the actions with cells, ranges, sheets and other Excel objects of destination workbook on stage 3 are impossible, because it is not loaded yet.
Furthermore other actions are limited because Workbooks.Count = 0 - refer to the Abstract. Both are the issues.


The Solving

Delayed running of opening event(s) macro(s) is the safe method to solve both issues.
On stage 3 we just charge the running of the open macro actions which really will be played on stage 6.

Here is the trick guide:
A. In your Add-In rename Sub Workbook_Open() to Sub Workbook_Open1() or to the macro name which you prefer.
B. Put such code to ThisWorkbook module of your Add-In:



' New event macro, it's just for delayed call of the real actions on the safe time
Private Sub Workbook_Open()
With ThisWorkbook
' This looks like zero delayed call but it doesn't realy zero :)
Application.OnTime Now, "'" & .Name & "'!" & .CodeName & ".Workbook_Open1"
End With
End Sub

' Here is your previous code
Private Sub Workbook_Open1()

' I would suggest the next line to be added, the reason is the another story ;)
If Workbooks.Count = 0 Then Workbooks.Add

' Put your code below, it will be running now on the safe time
' ...

End Sub

The same is for Auto_Open macro.

I'll be back to answer your 2nd question :cool:

Regards,
Vladimir

[ZVI]

As it was mentioned in post#8 the stage 5 is safety for events triggering of the opening workbook.

The triggering of opening event of each loaded workbooks can be made by the aid of class module and the using of WithEvents keyword for application object variable.

But instead of adding Class Module the ThisWorkbook module can be used for this purpose as well.

Below is the code to be copied into ThisWorkbook module of your Add-Ins:



' ThisWorkbook module code
Public WithEvents App As Application

' This code plays at each workbook loading
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

' Your triggering code is here
' ...
MsgBox "Open event for " & Wb.Name & " has happen", _
vbInformation, _
"See App_WorkbookOpen()"

End Sub

' Initiation of App object
Private Sub Workbook_Open()
Set App = Application
End Sub

Regards,
Vladimir

[Paul_Hossler]

What i mean is, when i open a file i want excel to find out if the file i opend is a csv-file


How are you opening the CSV?

1. Double clicking it, or

2. Starting Excel, and then doing a File Open manually?

Could you live with one way?

How portable (i.e. Other users / computers) does it have to be?

Paul