(Solved) – Access Database Export to Excel

(solved)-–-access-database-export-to-excel

I am trying to have an Access database send information to an Excel workbook.

I can get it to do that but I had to use 3 Sub() procedures. Sub () 01, 02, 03.

I can get the Subs to run, and the Excel to open the workbook. What I need to do is have Sub() 02 run on the same opened Excel sheet, and then the Sub() 03 to just run after that.

I can get the Subs() to run in order, but I cant get it to run on the same workbook. Here is the start of Sub()01 and some of the start of Sub()02. I left it so that it opens a new Excel workbook.

Private Sub 01()

DoCmd.GoToRecord , "", acFirst

Dim objXLApp As Object
Dim objXLBook As Object

Set objXLApp = CreateObject("Excel.Application")
objXLApp.Application.Visible = True

Set objXLBook = objXLApp.workbooks.Open("S:newborn screeningAdministrative NBS1 NBS AdminItems 
Being Worked on (Matt)LD4 listLD4a MM TB Diagnostic - Master.xlsx")


  With objXLApp
  .Visible = True
  .sheets("Aug").select

  End With

objXLBook.activesheet.range("A2") = "FY " & Me.[fy year]
objXLBook.activesheet.range("F3") = Me.[month]


objXLBook.activesheet.range("A5") = Me.[item #]
objXLBook.activesheet.range("B5") = Me.[Item]
objXLBook.activesheet.range("C5") = Me.[notes]
objXLBook.activesheet.range("D5") = Me.[Unit Type]
objXLBook.activesheet.range("E5") = Me.[Cost]
objXLBook.activesheet.range("F5") = Me.[Total]
objXLBook.activesheet.range("G5") = Me.[Qty Cost]

If (Not IsNull(Me.Item.Value)) Then
objXLBook.activesheet.range("A6").EntireRow.Insert xlDown

On Error GoTo errorhandler

DoCmd.GoToRecord , "", acNext

End If

objXLBook.activesheet.range("A6") = Me.[item #]
objXLBook.activesheet.range("B6") = Me.[Item]
objXLBook.activesheet.range("C6") = Me.[notes]
objXLBook.activesheet.range("D6") = Me.[Unit Type]
objXLBook.activesheet.range("E6") = Me.[Cost]
objXLBook.activesheet.range("F6") = Me.[Total]
objXLBook.activesheet.range("G6") = Me.[Qty Cost]


If (Not IsNull(Me.Item.Value)) Then
objXLBook.activesheet.range("A7").EntireRow.Insert xlDown

On Error GoTo errorhandler

DoCmd.GoToRecord , "", acNext

End If
-----------------------------
Private Sub 02()

Dim objXLApp As Object
Dim objXLBook As Object

Set objXLApp = CreateObject("Excel.Application")
objXLApp.Application.Visible = True

Set objXLBook = objXLApp.workbooks.Open("S:newborn screeningAdministrative NBS1 NBS AdminItems     Being Worked on (Matt)LD4 listLD4a MM TB Diagnostic - Master.xlsx")


  With objXLApp
  .Visible = True
  .sheets("Aug").select

  End With

objXLBook.activesheet.range("A56") = Me.[item #]
objXLBook.activesheet.range("B56") = Me.[Item]
objXLBook.activesheet.range("C56") = Me.[notes]
objXLBook.activesheet.range("D56") = Me.[Unit Type]
objXLBook.activesheet.range("E56") = Me.[Cost]
objXLBook.activesheet.range("F56") = Me.[Total]
objXLBook.activesheet.range("G56") = Me.[Qty Cost]

If (Not IsNull(Me.Item.Value)) Then
objXLBook.activesheet.range("A57").EntireRow.Insert xlDown

On Error GoTo errorhandler

DoCmd.GoToRecord , "", acNext

End If

objXLBook.activesheet.range("A57") = Me.[item #]
objXLBook.activesheet.range("B57") = Me.[Item]
objXLBook.activesheet.range("C57") = Me.[notes]
objXLBook.activesheet.range("D57") = Me.[Unit Type]
objXLBook.activesheet.range("E57") = Me.[Cost]
objXLBook.activesheet.range("F57") = Me.[Total]
objXLBook.activesheet.range("G57") = Me.[Qty Cost]

Leave a Reply

Your email address will not be published. Required fields are marked *