Ago
02-09-2010, 11:39 PM
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
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