Importing payroll transactions into GnuCash from Excel

[Art]

Hi Derek,

I'd love to share. Since I didn' know the best way to post to the list, I first posted to WordPress.com. Here's the URL, which appears to be world-readable (since I can read from an independent account),
http://pinaartblog.wordpress.com/2012/10/16/custom-xl2qif-to-import-csv-payroll-salary-into-gnucash/

It has the output, input, snippets, and the full Excel macro, with some comments.
Here's the script for quick reference, thanks to your strong hints, it worked exactly as I wanted it to work!
Sub XL2QIF()
Dim Rw As Long, Clm As Long, filename As String
  ' Columns("B:F").NumberFormat = "General"
  ' Row 1 is the user description of the columns
  ' Original macro used them for the transaction or column codes
  ' 121013 Max records set to 1,000 rows in spreadsheet for input
  filename = "c:\temp\my.qif"
  Close #1
  Open filename For Output As 1
  Print #1, "!Type:Bank"
  Print #1, "NSalary_BLE"  'Account

  For Rw = 2 To 1000
    If Cells(Rw, 1) = "" Then GoTo done
    ' Columns are fixed for this import
    If Trim(Cells(Rw, 1)) <> "" Then ' Account Salary_BLE
       Print #1, "PSalary_BLE"  'Payee. Or a description
       Print #1, "D" & Cells(Rw, 1).Text
       Print #1, "T" & Cells(Rw, 2).Text 'net deposit
       Print #1, "SGrossIncome_BLE" ' Split Category
       Print #1, "$" & Cells(Rw, 3).Text 'gross income - Split
       Print #1, "STaxes_BLE"
       Print #1, "$-" & Cells(Rw, 4).Text 'Taxes - Split
       Print #1, "SOtherDeductions_BLE" ' OtherDeductions
       Print #1, "$-" & Cells(Rw, 3) - Cells(Rw, 4) - Cells(Rw, 2) 'OtherDeductions - Split
    End If
    If Trim(Cells(Rw, 5)) <> "" Then ' Memo
       Print #1, "MHourRate " & Cells(Rw, 5).Text
    End If
    Print #1, "^"

  Next Rw
done:
  Close #1
  'The following can be included to view output,
  Shell "notepad.exe " & """" & filename & """", 1
End Sub
(Oops, I shrunk it to 8-pitch font, then couldn't undo it - I'm using Yahoo mail with its WYSIWYG [where the G is Got vs. Get! :->])
I thought I could use LSE highlighting for optimal readability in WordPress, but I didn't find the tags or means to do that.
Yes, the code is quite simple with hardcoded parameters and no error checking or validation, but it works! My excuse was it's "known good input" (and less than 1,000 records). ->


- Art



________________________________
 From: Derek Atkins <warlord at MIT.EDU>
To: Art <pinaart at yahoo.com> 
Cc: "gnucash-user at gnucash.org" <gnucash-user at gnucash.org> 
Sent: Monday, October 15, 2012 7:02 AM
Subject: Re: Importing payroll transactions into GnuCash from Excel
 
Art <pinaart at yahoo.com> writes:

> Thanks, Frank. I already resolved this. I updated the macro to create
> the QIF I needed and tested per Derek's quite constructive advice.(I
> guess I use Excel & CSV loosely because it means the same to me - I
> had loaded the XLA, but it didn't suit my needs, hence my search for
> VBA or any scripting capable of operating on CSV or Excel.)

Any chance you're willing to share your work with the rest of the class?
;)

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
      warlord at MIT.EDU                        PGP key available