[edit] deleted previous post that was just a sketch of the code below so duplicate
I’m too old to be coding till midnight but here it is. I looked at the python verses excel vba code and could not decide what I hated more so flipped a coin and vba won. Code at the end of post.
Motivation : I have a board with 300 or so 0402 parts. Previously I would have a laptop next to me in the lab with my BOM and for every component I would mark the reference, switch to pcbnew find that part on the board then place the component and then go back to excel and manually delete the reference from the cell to keep track of what I had put down…
It is a very slow an painful process that I did not want to repeat ever again.
For the BOM generation I start with the bom2groupedCsv.xsl script such that I end up with all the common manufacture part numbers in the same row.
So what I came up with in the end is a vba script that sits inside the excel BOM that allows me to hit keyboard shortcuts to search for the part in pcbnew and also cross-probe to Eeschema for free.
At best this can be called a hack, but it will work for me this weekend. It goes without saying this is a Window only solution. You will get just about every security warning and virus checkers will probably go crazy due to the user32 system calls but just take my word for it I’m not doing that to you.
How to use :
- Get your BOM ready loaded in to Excel
- Save the BOM as a macro enabled xlsm file.
- Go to developer tab | Visual Basic and paste the code here in the Module 1 section.
- Open your pcb layout in pcbnew and you can run the schematic at the same time if you like.
- In the BOM highlight what ever cell you like containing your component references.
- Hit “.” to select the first one, “,” for the previous.This will bold underline
- Once you have placed a part hit ‘p’, if you need to unplace hit ‘u’
like this.
Notes :
I’ve only really tested this with the OpenGL engine the standard one will not cross-probe back to Eeschema properly.
You can modify the shortcuts under Main().
Main needs to run when the sheet is loaded and I’m not sure if this will work for everyone.
Because this script gets ZERO feedback from pcbnew I’ve added a few ms delays between calls, this works for me but other users may need to increase them if it’s glitch.
pcbnew search box has an option “Do not warp mouse pointer” uncheck this.
What the? : Without really wanting to understand python or all the kicad source code (for now) I reverted to the sort of code I was writing 20 years ago to make windows do interesting things, it’s a real trip revisiting that. All this script is doing is using Windows system calls to Activate the pcbnew window and then send keystrokes to it like you would if you were interacting with it directly. CTRL+F to find the part, Escape to close the find window and so on.
Credit : I will admit that chunks of this code were re-purposed from google and other good people on the internet, I just glued it together. Mainly this.
If their is any interest in this let me know, but for my sake I will retain my sanity loading my new board this weekend.
' VBA - BOM 2 pcbnew selector
' pixulator.
' Notes :
' ensure "Do not warp mouse pointer" in pcbnew find box is UNCHECKED.
Option Explicit
Public Const ViewOpenGL = True ' don't change this
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal HWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal HWnd As Long) As Long
Private Declare Function GetWindow Lib "user32" (ByVal HWnd As Long, ByVal wCmd As Long) As Long
Private Declare Function IsWindowVisible Lib "user32" (ByVal HWnd As Long) As Boolean
Private Declare Function SetForegroundWindow Lib "user32" (ByVal HWnd As Long) As Long
#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Private Const GW_HWNDNEXT = 2
Private working As Boolean
Private ref_idx As Integer
Private Sub Main()
Application.EnableEvents = True
Application.OnKey "p", "comp_placed"
Application.OnKey "u", "comp_unplaced"
Application.OnKey ",", "comp_prev"
Application.OnKey ".", "comp_next"
working = False
ref_idx = 0
End Sub
Private Sub pcbnew_find(ByVal ref As String)
Dim selflhWndP As Long
Dim lhWndP As Long
If GetHandleFromPartialCaption(lhWndP, "Pcbnew") = True _
And GetHandleFromPartialCaption(selflhWndP, "Microsoft Excel") Then
'If IsWindowVisible(lhWndP) = True Then
' MsgBox "Found VISIBLE Window Handle: " & lhWndP, vbOKOnly + vbInformation
'Else
' MsgBox "Found INVISIBLE Window Handle: " & lhWndP, vbOKOnly + vbInformation
'End If
Sleep 200
SetForegroundWindow (lhWndP)
Sleep 50
SendKeys "^f", True ' Find
Sleep 50
SendKeys ref, True ' The component refference.
Sleep 50
SendKeys vbCr, True ' ENTER - DOIT!
Sleep 100
SendKeys Chr(27), True ' Escape : Close Find Dialog
Sleep 100
' Just in case the component is not found do it again.
' kicad will go ding.
SendKeys Chr(27), True
If ViewOpenGL = False Then
' Click where ever we ended up to crossprobe in Eeschema
' Not needed in OpenGL mode, and I seems not to work in
' the default viewer so yeah.
Sleep 100
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
Sleep 100
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End If
Sleep 200
' Back to Excel
SetForegroundWindow (selflhWndP)
Else
MsgBox "Could not get handles for Excel or pcbnew?", vbOKOnly + vbExclamation
End If
End Sub
Private Function GetHandleFromPartialCaption(ByRef lWnd As Long, ByVal sCaption As String) As Boolean
Dim lhWndP As Long
Dim sStr As String
GetHandleFromPartialCaption = False
lhWndP = FindWindow(vbNullString, vbNullString) 'PARENT WINDOW
Do While lhWndP <> 0
sStr = String(GetWindowTextLength(lhWndP) + 1, Chr$(0))
GetWindowText lhWndP, sStr, Len(sStr)
sStr = Left$(sStr, Len(sStr) - 1)
If InStr(1, sStr, sCaption) > 0 Then
GetHandleFromPartialCaption = True
lWnd = lhWndP
Exit Do
End If
lhWndP = GetWindow(lhWndP, GW_HWNDNEXT)
Loop
End Function
Sub select_reff(ByVal direction As Integer, ByVal highlight_mode As Integer)
If working Then Return
Dim cell_reffs() As String
Dim col_start As Integer
Dim col_length As Integer
Dim a As Integer
Dim cell_reffs_len As Integer
working = True
ActiveCell.Font.Bold = False
ActiveCell.Font.Underline = False
cell_reffs = Split(Trim(ActiveCell.Text), " ")
cell_reffs_len = UBound(cell_reffs) - LBound(cell_reffs) + 1
ref_idx = ref_idx + direction
If ref_idx > cell_reffs_len Then ref_idx = 1
If ref_idx = 0 Then ref_idx = cell_reffs_len
col_start = 0
col_length = 0
For a = 1 To cell_reffs_len
col_length = Len(cell_reffs(a - 1)) + 1
If a = ref_idx Then Exit For
col_start = col_start + col_length
Next
ActiveCell.Characters(Start:=col_start, Length:=col_length).Font.Bold = True
ActiveCell.Characters(Start:=col_start, Length:=col_length).Font.Underline = True
If (highlight_mode = 1) Then ' Placed
ActiveCell.Characters(Start:=col_start, Length:=col_length).Font.Color = RGB(0, 255, 0)
ElseIf (highlight_mode = 2) Then ' unplaced
ActiveCell.Characters(Start:=col_start, Length:=col_length).Font.Color = RGB(0, 0, 0)
End If
If highlight_mode = 0 Then pcbnew_find (cell_reffs(ref_idx - 1))
working = False
End Sub
Sub comp_prev()
select_reff -1, 0
End Sub
Sub comp_next()
select_reff 1, 0
End Sub
Sub comp_placed()
select_reff 0, 1
End Sub
Sub comp_unplaced()
select_reff 0, 2
End Sub
