[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 775
  • Last Modified:

Writing to an Excel 2007 xlsm template using ADODB

Hello,

We have a fairly complex Excel template containing many formulas and macros, which resides in a file vault.  We have a VB6 bas module which reads the template from the vault, writes user-specific information to various worksheets on the template, then opens the resulting file on the user's desktop.  This was originally an Excel 2003 template, but since our company has moved to Excel 2007, we have converted it an xlsm file.  We are now finding that our code which writes to the template (formerly using the Jet driver) is no longer working correctly.  

Below is a code sample.  In the first example, the test code correctly populates cells A1 through M1 with test data.  However, in the second example, rather than populating cells A19 through M19, the test data is written in cells A1 through M1.  Furthermore, cells A1 and A2 contain random text pulled from various other cells on the worksheet.

Any idea what we need to do differently?

Thanks!!

Kirk

Set cn = CreateObject("ADODB.Connection")
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.ConnectionString = "Data Source=" & strVaultFilePath & ";Extended Properties=""Excel 12.0 Macro;HDR=No;"""
    cn.CursorLocation = 2
    cn.Open

Set rs = CreateObject("ADODB.Recordset")

    rs.ActiveConnection = cn
    rs.CursorType = 3 ' static cursor
    rs.LockType = 2 ' pessimistic lock

'EXAMPLE 1

    strRange = "[WorkSheet1$A1:M1]"
    rs.Open = "select * from " & strRange
    rs.fields.Item("F1") = "1"
    rs.fields.Item("F2") = "2"
    rs.fields.Item("F3") = "3"
    rs.fields.Item("F4") = "4"
    rs.fields.Item("F5") = "5"
    rs.fields.Item("F6") = "6"
    rs.fields.Item("F7") = "7"
    rs.fields.Item("F8") = "8"
    rs.fields.Item("F9") = "9"
    rs.fields.Item("F10") = "10"
    rs.fields.Item("F11") = "11"
    rs.fields.Item("F12") = "12"
    rs.fields.Item("F13") = "13"
    rs.Update
    rs.Close
       
'EXAMPLE 2

   strRange = "[WorkSheet1$A19:M19]"
    rs.Open = "select * from " & strRange
    rs.movefirst
    rs.fields.Item("F1") = "1"
    rs.fields.Item("F2") = "2"
    rs.fields.Item("F3") = "3"
    rs.fields.Item("F4") = "4"
    rs.fields.Item("F5") = "5"
    rs.fields.Item("F6") = "6"
    rs.fields.Item("F7") = "7"
    rs.fields.Item("F8") = "8"
    rs.fields.Item("F9") = "9"
    rs.fields.Item("F10") = "10"
    rs.fields.Item("F11") = "11"
    rs.fields.Item("F12") = "12"
    rs.fields.Item("F13") = "13"
    rs.Update
    rs.Close
0
kadwahl
Asked:
kadwahl
  • 2
1 Solution
 
StellanRosengrenCommented:
Hi kadwahl,
You should always work on the complete table when using ADD to connect to a worksheet. So, if you redefine your range string to cover the whole table you will be able to move to any line by rs.MoveNext or rs.Find which is the fastest if you have some unique tag to search for. Please tell me if you need more advice.

Kind regards,
Stellan
0
 
StellanRosengrenCommented:
Hi again,
No response yet, but I assume that you are still interested in the question. Excuse me for mistyping ADO in my last post.
Let me explain a bit further,
if your table is in A1:M100 (as an example)
and after these statements you have a recordset that you can use to manipulate the table,
strRange = "[WorkSheet1$A1:M100]"
rs.Open = "select * from " & strRange

then you should navigate your table like this:
rs.MoveFirst 'puts the record pointer on the first row which is A1:M1
rs.Fields(0).Value="1" 'change the value of A1 to "1"
rs.Fields(12).Value="13" 'the value of M1
rs.Update

Now to change the values of row 19,
you have to position the record pointer to that row. One way is to loop:

For i=1 to 18
 rs.MoveNext
Next i 'after the for next loop, the record pointer is at row 19
rs.Fields(0).Value="The value of cell A19"
rs.Update

The above is how ADO should be used to access a table. I hope it will solve your problem.

Kind regards,
Stellan
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now