Write info from table to excel file

[smikkelsen]

I am trying to write specific data from a record set to an existing excel file. I have been messing with it for hours and am not having a whole lot of success.

I am also getting errors off and on. It seems that something isn't closing out correctly when the code finishes either because after I run it the first time, it gives me errors on the cell(3,6) part of the code. Then if I open the windows processes, there is always an excel process still running. Once I close that, it starts to partially work again.

Any ideas??

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strNoContact, strBuild, strPreview, strRevisions, strComplete, strHold, strDone As String
  Set db = CurrentDb()
  Set rs = Me.Recordset.Clone
  'Set rs = db.OpenRecordset("main_tracking")
  If rs.RecordCount = 0 Then Exit Sub
  rs.MoveFirst
  rs.MoveLast
  rs.MoveFirst
 
 
  ' Create excel file_________________________________________________________________________
Dim MyXL As Object 'Excel Application Object
Dim XL_File, DBPath As String
DBPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
XL_File = DBPath & "Exported_Sites.xlsx"  '  save location for finished file
Set MyXL = CreateObject("Excel.Application")   'Create the Excel Application Object.
Set xlbook = MyXL.Workbooks.Open(DBPath & "test.xlsx")   ' open file
 
  ' loop through each record in the first recordset__________________________________________
  Do Until rs.EOF
 
    Cells(3, 6) = rs("merchant")
 
    rs.MoveNext
  Loop
  ' Save And Close Excel File_______________________________________________
 
MyXL.Application.Visible = True
MyXL.Worksheets(1).SaveAs (XL_File)   'Save the Excel File
MyXL.Quit
 
Set MyXL = Nothing
Set xlbook = Nothing

Also, i don't think all my code is correct to open the excel file. Its pieced together from dozens of examples that I read through. All were close, but not exactly what I needed so I had to try piecing stuff together.

I'm sure this is a simple thing for most, but i'm really pulling my hair out on it.

Thanks in advance.

 

[JANR]

You might want to read this:

http://blogs.msdn.com/b/access/arch...-objects-when-referring-to-excel-in-code.aspx

That is to explicit use your declared object when you do something to your excel object. Probably your problem lies in this bit:

Do Until rs.EOF
 
    Cells(3, 6) = rs("merchant")
 
    rs.MoveNext
  Loop

try and bind this to MyXL or xlbooks

Do Until rs.EOF
 
    [B]xlbooks[/B].Cells(3, 6) = rs("merchant")
 
    rs.MoveNext
  Loop

JR

 

[JANR]

You also have a few other problems, you haven't declared the xlbooks As Object and XL_File isn't declared as string but as a variant. The same goes for strNoComntact, strBuild etc, Declare those as STRING

mod code:

  ' Create excel file_________________________________________________________________________
Dim MyXL As Object 'Excel Application Object
[B][COLOR=red]Dim xlbook As Object[/COLOR][/B]
Dim XL_File [B][COLOR=red]As String[/COLOR][/B], DBPath As String
DBPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
XL_File = DBPath & "Exported_Sites.xlsx"  '  save location for finished file
Set MyXL = CreateObject("Excel.Application")   'Create the Excel Application Object.
Set xlbook = MyXL.Workbooks.Open(DBPath & "test.xlsx")   ' open file
 
  ' loop through each record in the first recordset__________________________________________
  Do Until rs.EOF
 
   [COLOR=red] [B]xlbook[/B][/COLOR].Cells(3, 6) = rs("merchant")
 
    rs.MoveNext
  Loop
  ' Save And Close Excel File_______________________________________________
 
MyXL.Application.Visible = True
MyXL.Worksheets(1).SaveAs (XL_File)   'Save the Excel File
MyXL.Quit
 
Set MyXL = Nothing
Set xlbook = Nothing

JR