Excel and Access geek drinks down london way

My mate Dick Moffat is over from Canada at the end of October.

He asked me to ask if anyone fancied meeting up for a drink at some point over the weekend of 28-30 October?

I’m not sure where I will be that weekend. But if there is an sensible way I can get to London, I will.

I guess the easiest way to organise it is to leave a note on his blog if you fancy a little office related socialising.

cheers

simon

Excess

And the other way:

public void ImportResFromAccess2Excel(xl.Range Dest)
        {
            object oo = Type.Missing;
            //int recCount = 0;

            MakeADOConnection();
            ADODB.Recordset rs = new ADODB.Recordset();

            rs.Open(“qryFinalReportView”, mADOConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdStoredProc);
            Dest.CopyFromRecordset(rs, oo, oo);
            rs.MoveLast();
            mDataRowCount = rs.RecordCount;
            mDataColcount = rs.Fields.Count;

            rs.Close();
            CloseADOConnection();
        }

Via the gift of an ado recordset.

the conn string for that is

mADOConn.Open(“Provider=Microsoft.Jet.OLEDB.4.0;” + “Data Source=” + msDBPath + “;”, “”, “”, 0);

Thats against an mdb.

btw I made the mistake of copying that directly from the VS IDE – turns out thats almost as bad as Word for scabby random html. I repasted it through my current fave text editor (Notepad++)

Cheers

simon

Accxel

I’ve been having some fun shuffling data between Excel and Access (well just Jet really) recently. Mainly this was to get around the huge memory leak that existing in previous excels when using Jet to query an open workbook. From my very limited trials this issue now seems fixed in 2007. (This code is for Office 2007 btw.)

Anyway here is the connection string and routine (in C#) to extract data from a workbook range and append it to an access table. I’m posting it here in the vain hope that the next time I need I will be able to find it. And maybe someone else might find it useful. And if not what are you doing instead?

public static void ImportFromExcelToAccess(string AccessFileFullName, string ExcelWBFullName, string AccessTableName, string SelectSQL)
        {
            string connect = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” +
                ExcelWBFullName + “;Extended Properties=\”Excel 12.0;HDR=YES\””;
            using (OleDbConnection conn = new OleDbConnection(connect))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = conn;
                    string s = “INSERT INTO [MS Access;Database=” + AccessFileFullName + “].[” + AccessTableName + “] ” + SelectSQL;
                    cmd.CommandText = s;
                    Debug.Print(s);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }

(Its static because I am calling it from Excel DNA)

I got most of it from the very nice man here.

Cheers

simon

Spreadsheet Control Projects

Any experienced developers out there looking for a role on a spreadsheet control project?

I have sniffs of a couple of roles here in sunny Geneva and also one in Luxembourg.

All for large financial institutions, all trading/banking, all looking at similar stuff.

• Assess the current spreadsheet mess
• propose solutions on a workbook by workbook basis, (either leave as is, tidy up, or migrate basically.)
• Potentially assist/do or lead that work

Rates are market rate, between 3-600 quid depending on skill and experience. All are full time, on site, you’ll have to sort out your own travel and accom if you don’t live local to the roles. All are 3 months to start, with a good chance of an extension if you know what you are doing.

You’ll need:

• good Excel skills and experience (min 5 yrs prob)
• good VBA (min 3 yrs approx)
• some none spreadsheet development experience (either relational db or .net or java or other lang)
• full SDLC experience
• Good financial services experience
• Good analysis experience

If you are interested and think you meet the requirements then drop me a line with your CV and I will pass it on to the powers that be.

cheers

Simon

(send your cv to info@codematic.net)(dont send spam or viagra or ED offers – thnx)