Writing Values To MS Excel From Database

Hi,

Have a quick question on a problem thats been driving me nuts all night. Here is what I am looking to do. I am accessing a MS Access database and grabing files and putting them into a recordset. I then want to put some of the information from this recordset in an Excel template which is opened up.

Here is where I am stuck. I can get all the way to the point of opening up the Excel file. However once opened up I cannot get the recordset data to print in the cell at all.

Here is my code that does not work. It runs like it processes everything except the part that writes to the opened Excel file.

Set oExcel = Excel.Application
oExcel.Visible = True
Set oWB = oExcel.Workbooks.Open("C:\Path To File.xls")
Set oWS = ActiveWorkbook.Worksheets("Cover")


While Not rsCust.EOF
With rsCust
If IsNull(.Fields("CompanyName").Value) Then
oWS.Cells(11, 3) = "Test" 'Places Test If No Value For Company Name
Else
oWS.Cells(11, 3) = rsCust!CompanyName 'Places Company Name If There Is A Value
End If
.MoveNext
End With
Wend


This code however will work and will print Test in the cell on the opened Excel file.


Set oExcel = Excel.Application
oExcel.Visible = True
Set oWB = oExcel.Workbooks.Open("C:\Path To File.xls")
Set oWS = ActiveWorkbook.Worksheets("Cover")
oWS.Cells(11, 3) = "Test" 'Why Will This Only Work If This Line Of Code Is In This Spot??

While Not rsCust.EOF
With rsCust
If IsNull(.Fields("CompanyName").Value) Then
oWS.Cells(11, 3) = "Test" 'Places Test If No Value For Company Name
Else
oWS.Cells(11, 3) = rsCust!CompanyName 'Places Company Name If There Is A Value
End If
.MoveNext
End With
Wend


Thanks for your help, and if you need me to elaborate any further to clear up any questions please let me know.

Hi verno_25_02,

Try This:

' Connection ...
' RecordSet ...
' SQL ...

Dim oExcel As Excel.Application '<= added
Set oExcel = New Excel.Application '<= * changed *
Dim oWB As Excel.Workbook '<= added
Dim oWS As Excel.Worksheet '<= added

Set oWB = oExcel.Workbooks.Open("PathToYourFile\File.xls")

oExcel.Visible = False ' or True

Set oWS = oWB.Worksheets("Cover") '<= * changed *

With rsCust
' if there's always only one record possible, a Loop shouldn't be needed.
If .RecordCount > 1 Then MsgBox "Error - There's more than one Record"

If IsNull(.Fields("CompanyName").Value) Then
oWS.Cells(11, 3) = "Test"
Else
oWS.Cells(11, 3) = .Fields("CompanyName").Value '<= * changed *
End If

End With

rsCust.Close

Call oWB.Close(SaveChanges:=True) ' I assume that you want to save the changes

oExcel.Quit ' optional

' Release Object's from memory
Set rsCust = Nothing
Set Cnt = Nothing ' replace "Cnt", with your actual connection variable.

Set oExcel = Nothing
Set oWB = Nothing
Set oWS = Nothing

:)
It looks to me, like you could benefit from reading The Automating Excel Tutorial:
http://www.xtremevbtalk.com/t135815.html

If the code above doesn't help, then post the portion of your code, that's accessing the MS Access database.

Here's a good Tutorial on ADO, by Optikal:
http://www.xtremevbtalk.com/showthread.php?threadid=66994

First: Are you sure that the recordset is not empty?

Second: the variable ll was never incremented inside the Do loop, so it will just in a single cell and overwrite its exisiting value.

Third: In my IMHO it is better the set the Visible property of the excel application to false (as tboltfrank provided) while the data is being written to the application, to make it run faster.

Hey Guys,

Thanks for your help. tboltfrank I placed the lines you changed into my code and it works great. I read over that tutorial along with some other posts before posting but I still couldn't figure out what was going wrong. The Dim statements I had above my option explicit line so I moved them down into my sub routine. Anyway its working great thank you all for your help I appreciate it.

Hi verno_25_02,

If you already had the Dim statements, the worst of your problem's, were probably your use of ActiveWorkbook and lack of using the "New" keyword.

your code used:
Set oExcel = Excel.Application

* but, should be:
Set oExcel = New Excel.Application

AND:
Set oWS = ActiveWorkbook.Worksheets("Cover")

* should be:
Set oWS = oWB.Worksheets("Cover")

>

Plus: as Duane so adeptly pointed out, if there were to ever be more than one record, your referenced cell, would have been over written with each cycle through the loop. For that reason, I amended my code, to do away with Your Loop and give you an Error Message, if there is ever more than one Record found.

>