CODEWITHSUNDEEP
excelvbams-access
smalliest onefifty
smalliest onefifty

Reputation: 31

ms access export to ms excel format

I have an access database with 1 table 6 columns ie item, units, quote1, quote2, quote3 and quote4.

I would like to have a form where items are type and an excel sheet is produced with the same format but only items typed are printed and exported to excel.

The excel sheet has the following format No, Item, Units, SOH, quote1, quote2, quote3, quote4.

How do I accomplish this?

Upvotes: 0

Views: 150

Answers (1)

TerenceLam
TerenceLam

Reputation: 173

The approach I used at work is as follows:

Build a form with the data fields you would like to export put on it.

Start a new module and put the following sample code inside. My form was called "TableView"

Sub exportcasetable4()
  DoCmd.OpenForm "TableView", acFormDS
  Forms!tableview.SetFocus
  DoCmd.RunCommand (acCmdSelectAllRecords)
  DoCmd.RunCommand (acCmdCopy)
  Dim objXLOutput As Object
  Dim objWBOutput As Object
  Dim objWSOutput As Object
  Set objXLOutput = CreateObject("Excel.Application")
  objXLOutput.Visible = True
  Set objWBOutput = objXLOutput.Workbooks.Add
  Set objWSOutput = objXLOutput.ActiveWorkbook.ActiveSheet
  objWSOutput.Range("A1").Select
  objWSOutput.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon _
    :=False
  objWSOutput.Range("A1").Select
  DoCmd.Close acForm, "TableView"
  Set objXLOutput = Nothing
  Set objWBOutput = Nothing
  Set objWSOutput = Nothing
End Sub

You can then call this subroutine and it will dump all the data fields into Excel. You may choose to call it from a button.

Hope this help!

Upvotes: 1

Related Questions