Solved: Accessing excel spreadsheets from word

[sandam]

Right, up until now I've been using the code below to open excel spreadsheets and get data from them. However as the project i'm working on nears the next phase I need to find a more speed effecient method to do it. My boss mentioned trying ODBC but after about and hour and a half on the net I'm no closer to figuring that out than I was when I started. If anyone has any suggestions on how to speed things up, I'm be much appreciative :)

Private Sub WorkWithExcel()
Dim ObjExcel As Excel.Application
Dim Wkb As Excel.Workbook
Dim WS As Excel.Worksheet

Set ObjExcel = New Excel.Application
Set Wkb = ObjExcel.Workbooks.Open(FileName:=myPath)
Set WS = Wkb.Sheets("Sheet1")

'Do stuff with the worksheet here

Wkb.Close Savechanges:=True
Set ObjExcel = Nothing
Set Wkb = Nothing
Set WS = Nothing
End Sub

[Killian]

Hi there Andy,
I remembered reading about this on XL_Dennis' OzGrid site a while back, though I never got round to using it. http://www.ozgrid.com/forum/showthread.php?t=17158

[sandam]

I tried the example but without success. I have the latest MDAC (2.8) I think however it tells me that the ADODB.Recordset is an undefined user type - which is quite confusing really. And I've definitely added the reference to the VBA macro. I shall have to investigate more as to why this has happened.



Andrew;?

[MOS MASTER]

Hi Andrew, :D

There are multiple ways to use the ADO library for this. Killian's example uses SQL to retrieve the data.

I'm using a named range in Excel because that's another easy way to do this:

If I remember well you also use 2003 like me so I've made a little example for you witch has a reference to the ADO Library in this way we can test if you're library is working or not.

The code I'm using:
Sub ExcelDataToBookmarks()
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveDocument.Path & "\Test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""

Set rst = New ADODB.Recordset
rst.Open "DataWord", cn 'DataWord is Named range in Excel

With ActiveDocument
Do Until rst.EOF
.Bookmarks(CStr("" & rst.Fields(0))).Range.Text = CStr("" & rst.Fields(1))
rst.MoveNext
Loop
End With

rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing
End Sub

Run the code in the attachment with ALT+F8

Enjoy! :whistle:

[sandam]

I'm afraid I can't download your sample just yet (I'm not allowed to download executables or zips at work) but when my boss gets in I'll get him to download it and I'll give it a bash. I made some progress yesterday afternoon by taking away the ADODB prefix but after that it still fell down. Tinker tinker tinker is all I can say. I'll get it figured out eventually :)



Andrew;?

[sandam]

-------------------------------------------------------------

IT WORKS! IT WORKS! IT WORKS! IT WORKS! IT WORKS! IT WORKS! :ole:

With the link from you Killian and the right reference from you Joost, it works. Now i'll just have to bend it to my will MUWAHAHAHAHAHAHA :devil:

Thanks guys

Andrew;?

[sandam]

------------------------------------------------------

First problem... Can't edit values... Hmmmm, maybe marked thread solved too soon... counting chickens and all that... :(

i really should read the code i'm using shouldn't I...
:doh: :banghead:

[sandam]

------------------------------------

editting values = cool, i figured that out.

but I keep getting a null value read on certain fields even though I know those fields hold values??

I'm confused here



Andrew;?

[Killian]

Hi Andy
I just had a quick test of Dennis' code and it seems to work nicely.
Can you post the procedure you're using to get the data? I have to go over to another site this afternoon but I'll try to get a look at it at some point...

OK, I was just having a browse around the board and jonske has just submitted a KB article that may interest you.Option Explicit

'you can extract data from a closed file by using an
'XLM macro. Credit for this technique goes to John
'Walkenback > http://j-walk.com/ss/excel/tips/tip82.htm

Sub GetDataDemo()

Dim FilePath$, Row&, Column&, Address$

'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Book1.xls"
Const SheetName$ = "Sheet1"
Const NumRows& = 10
Const NumColumns& = 10
FilePath = ActiveWorkbook.Path & "\"
'***************************************

Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub


Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function

[sandam]

been looking at some stuff and perhaps i need to use the rst update or add new... missed those but then its been a while since i last worked with ADO objects - 2 years to be precise so I may have forgotten a thing two ;)


Sub Get_Singel_Values_Closed_Workbooks()
Dim rst As ADODB.Recordset
Dim stCon As String, stSQL As String
Dim i As Long
Dim str1 As String
Dim path As String
Dim changer As Variant

'Instantiate a new Recordset-object which also will be the only ADO-object
'we use in this sample
Set rst = New ADODB.Recordset
path = "C:\Test\Temp1\1234\1\ContactHeadings.xls"
'For Each fsoFile In fsoFolder.Files
'If fsoFile Like "*.xls" Then
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & path & ";" & "Extended Properties='Excel 8.0;HDR=YES'"
'Although we only get one cells value the syntax below, ie range C4:C4, is
'necessary because per se the approach expect a fieldname and a value.
stSQL = "SELECT * From [Sheet1$A1:E100]"
'
'Create/open the connection and execute the SQL-statement.
rst.Open stSQL, stCon, adOpenDynamic, adLockOptimistic, adCmdUnknown

With Application
.ScreenUpdating = False
Do Until rst.EOF
str1 = ""
For i = 0 To rst.Fields.count - 1
str1 = str1 & " " & CStr(rst.Fields(i).Value)
Next i
MsgBox str1
rst.MoveNext
Loop
.ScreenUpdating = True
End With
'Empty the string-variables and close the connection.
'By only closing but not setting the rst-variable to nothing we
'actually use the connection pooling technique.
stCon = Empty
stSQL = Empty
rst.Close

'Release objects from memory.
Set rst = Nothing
End Sub

[Killian]

The other thing to remember is that ADO assumes the excel data range will include heading rows to use as field names. You need to change the value in your connection string to HDR=NO to get all your data.

[sandam]

the thing is i am using heading fields. I don't know why its giving me null values on a read from a cell with a value. howver i did manage to figure out what to do to update fields. Set the rst.Fields(x).value = Cvar(info). I think the problem there was that it need a cell format or something?? so converting the new value to a variant takes care of that. I think i'll just need to be sure to write a value to the cells, even if its just a place marker that that doesn't appear on the input form. thanks again for your help :).


Andrew;?

My personal feeling is that it was just a glitch but we'll wait and see :dunno

[Killian]

The sad fact is ODBC & Excel does not give you a database where each field is of a defined type.
The Excel ODBC driver scans the first 8 rows of each column and makes a "guess" at the data type so if you have a mixture in there it may cause a few glitches or become confused with what to do with a blank field.
I think you're on the right track with trying to manage what goes into the cell values (just bear in mind the recordset field types will be based on what's in those first 8 rows)

[MOS MASTER]

Hi Andrew, :D

I don't fully understand what is going wrong in you're situation and exactly what you're trying to do here.

I still believe there is a lot possible with this method but have to know first what you're trying to do.

Could you Please post you're Word template and you're excel Workbook (Zippt I know it's hard for yah! :rofl: ) but that would help in helping you! (Strange sentence that one....)

O...and do discribe somemore what you want! :whistle:

[sandam]

In a nutshell... We have a 3rd Party product called AIM Evolution. This manages the solicitors firm's clients and matters. I am in the process of converting their old WordPerfect 9 document management system to Word, but it also has to run through AIM - supposedly to get the client/contact details to put in the various documents we send out. This falls down a little bit because the contacts side of the DB(aim is a relational database) has never been set up properly.

What I have been instructed to do is:

1. take the contact information as it comes from AIM.

2. Allow the user to edit it and then insert this into the letter template

3. Save the eddited data to an excel spreadsheet that the user can then reuse when they want to create more letters.

The reason i'm trying ADO is that it takes to long with the code I've used above and I find my users losing focus from the macro and thus making mistakes.

But dont fret too much Joost. I think I have actually solved the problems - the orginal one as well as the read blanks. I hadn't worked with ADO for a while so i guess it just took some tinkering and re-remembering of processes. Everything is working fine now, and faster too.

Dankie weer vir die hulp en ek is jammer om jou so to pla. Lekker bly en geniet die naweek.



Thanks to you too killian. You pointing out the looking at the first 8 columns helped a lot too.



Thanks both of you

Andrew;?

[MOS MASTER]

Hi Andy, :hi:

Graag gedaan hoor!

Thnx for the feedback on what you're trying to achieve. Seams to me you're getting there pretty fast now!

Could you be so kind to post you're sollution if you get there? Or else do come back and challenge us! :rofl:

Tot de volgende ronde!