Importing .xls or similar files

[Georg Stapper]

For a simple bank account the following little bit of VBA code is all you need:

Sub QIF()
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("C:\Documents and Settings\My Documents\My_QIF_file.qif", True)

    ' a little bit of code here to catch the data from the spreadsheed
    ' then call write_QIF_header
    ' loop over all transactions including a call of write _QIF_Bank_transaction
    
End Sub

Sub write_QIF_header(a, Account As String, Description As String, _
                      Account_Type As String, Balance_Date As String, _
                      Amount As String)
    a.WriteLine ("!Account")
    a.WriteLine ("N" & Account)
    a.WriteLine ("D" & Description)
    a.WriteLine ("T" & Account_Type)
    a.WriteLine ("/" & Balance_Date)
    a.WriteLine ("$" & Amount)
    a.WriteLine ("^")
    a.WriteLine ("!Type:" & Account_Type)
End Sub

Sub write_QIF_Bank_transaction(a, Num As String, Datum As String, Memo As String, Amount As String, _
    category As String, Optional payee As String = "")
        
    a.WriteLine ("D" & Datum)
    a.WriteLine ("U" & Amount)
    a.WriteLine ("T" & Amount)
    a.WriteLine ("N" & Num)
    a.WriteLine ("M" & Memo)
    If category <> "" Then a.WriteLine ("L" & category)
    If payee <> "" Then a.WriteLine ("P" & payee)
    a.WriteLine ("Cx")  ' specify otherwise
    a.WriteLine ("^")
End Sub

Cheers,
Georg

----- Original Message ----
From: Karl Grant <karlgrant06 at gmail.com>
To: David S Dampier <dampierd01 at optusnet.com.au>
Cc: gnucash-user at gnucash.org
Sent: Monday, 14 January, 2008 9:57:50 PM
Subject: Re: Importing .xls or similar files

There is a free program called xls2qif (google it!) that changes the excel
spreadsheet to a banking format. That can then be easily imported by
gnucash.
When you are importing, say for example cash receipts, you may have to set
up lots of new expense accounts if you haven't already done it from the
import screen.

HTH,
Karl

On 14/01/2008, David S Dampier <dampierd01 at optusnet.com.au> wrote:
>
> Does anyone know how to import these types of files, can it be done? how
> successful is it?
>
> Tanks,
> Father David
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>



-- 
Karl
_______________________________________________
gnucash-user mailing list
gnucash-user at gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.


      __________________________________________________________
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com