Exporting Access Table data to Excel

[penndro]

Can some please help me with the code to export my access table to an Excel Sheet using the Click Event.

If someone could help me with this Access Coding I would truly apprecite it.

My table is tbl_pricing_template. I need to export all the field in that table that match the current customerID and ProductID fields.

Thanks in advance,
Penndro

 

[PHV]

Have a look at the TransferSpreadsheet method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DexterOf1977]

create a macro and then save it as a module

then go into the module and take the code to put behind your command button.

good trick to remember

 

[penndro]

I created the following code based on some tutorials;however I am getting a complile error: User-Defined type not defined.

Can someone please look at my code and help me to figure out how to define the Excel application type - also am I referring to my data table (tbl_Custom_Pricing_Template_Data_Flats) correctly so that it brings all the data from there to the Excel sheet?

HEre is my code:

Private Sub ExportBtn_Click()

'template file as used to create SAP Excel Template File
Dim MySheetPath As String

'Set the path for the template file
MySheetPath = "C:\temp\zbp1template.xls"

' Set up object variables to refer to Excel and Objects
Dim Xl As Excel.Application
Dim Xlbook As Excel.Workbook
Dim Xlwks As Excel.Worksheet

'Open an instance of Excel, open the workbook
Set Xl = CreateObject("Excel.Application")
Set ObjectVarName = GetObject(MySheetPath)

'Make Excel workbook visible on screen
Xl.Visible = True
Xlbook.Windows(1).Visible = True

'Define first sheet in workbook as Xlwks
Set Xlwks = Xlbook.Worksheets(1)

'Set Header Rows & Copy Data from Access Table "tbl_Custom_Pricing_Template_Data_Flats" to Excel Worksheet
X1wks.Range("A1") = "BGR00"
X1wks.Range("B1") = "0"
X1wks.Range("C1") = "ZBP1907"
X1wks.Range("D1") = "<SY-MANDT> OPTIONAL"
X1wks.Range("E1") = "<SY-UNAME> OPTIONAL"
X1wks.Range("A2") = "BKOND1"
X1wks.Range("B2") = "1"
X1wks.Range("C2") = "XK15"
X1wks.Range("A3:X1500").Locked = False

rst.Open "SELECT * FROM tbl_Custom_Pricing_Template_Data_Flats", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
Rows = 3 ' start row
Mcolumn = 1 ' start column
Do Until rst.EOF

With Xlwks ' existing excel worksheet.Cells(Rows, Mcolumn).Value = rst.Fields

End With
rst.MoveNext
Loop
rst.Close

' Save Workbook
Xlbook.Save

' Clean and set worksheet to screen
Set Xl = Nothing
Set Xlbook = Nothing
Set XlSheet = Nothing

End Sub

 

[ProgramError]

set a break point at the beginnning of the proceedure and step through the code to find out which statement is causing the error.


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[skyline666]

Hi penndro,

You have defined Xlwks but have used X1wks when you come to do the range bits, shudnt it be Xlwks? should be the letter l (small L) not the number 1 (one).

Andrew

 

[penndro]

It's having a problem with this statement:
Dim Xl As Excel.Application

here is the revised code:

Private Sub ExportBtn_Click()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordSet As New ADODB.Recordset
MyRecordSet.ActiveConnection = cnn

'Build the SQL Statement
Dim MySQL As String
MySQL = "SELECT [CondKey] , [AutoPop], [SalesOrg], [DistChannel], [CustomerID]"
MySQL = MySQL + "[KeyR], [PriceBreak_EffectDate],[UOM_EndDate], [Rate], [Currency_RateUnit]"
MySQL = MySQL + "[Per],[Keyx] , [KeyY], [HeaderUOM]"
MySQL = MySQL + "FROM tbl_Custom_Pricing_Template_Data_Flats"

MyRecordSet.Open MSQL

'template file as used to create SAP Excel Template File
Dim MySheetPath As String

'Set the path for the template file
MySheetPath = "C:\temp\zbp1template.xls"

' Set up object variables to refer to Excel and Objects
Dim Xl As Excel.Application
Dim Xlbook As Excel.Workbook
Dim Xlwks As Excel.Worksheet

'Open an instance of Excel, open the workbook
Set Xl = CreateObject("Excel.Application")
Set ObjectVarName = GetObject(MySheetPath)

'Make Excel workbook visible on screen
Xl.Visible = True
Xlbook.Windows(1).Visible = True

'Define first sheet in workbook as Xlwks
Set Xlwks = Xlbook.Worksheets(1)

'Set Header Rows & Copy Data from Access Table "tbl_Custom_Pricing_Template_Data_Flats" to Excel Worksheet
Xlwks.Range("A1") = "BGR00"
Xlwks.Range("B1") = "0"
Xlwks.Range("C1") = "ZBP1907"
Xlwks.Range("D1") = "<SY-MANDT> OPTIONAL"
Xlwks.Range("E1") = "<SY-UNAME> OPTIONAL"
Xlwks.Range("A2") = "BKOND1"
Xlwks.Range("B2") = "1"
Xlwks.Range("C2") = "XK15"
Xlwks.Range("A3:X1500").Locked = False


'Save Workbook
Xlbook.Save

'Clean and set worksheet to screen
Set Xl = Nothing
Set Xlbook = Nothing
Set XlSheet = Nothing

End Sub

 

[boblarson]

The user defined error is probably happening because you don't have a reference set to Excel. You are using early binding when you use:

Dim Xl As Excel.Application
Dim Xlbook As Excel.Workbook
Dim Xlwks As Excel.Worksheet

and that requires a reference to be set. If you want, you can use LATE binding which requires no reference:

Dim Xl As Object
Dim Xlbook As Object
Dim Xlwks As Object

And you are already using the CreateObject method anyway, which goes well with this.



Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[penndro]

I've updated the code to reference Excel; however now I am getting this error:

Runtime Error '3001":
Arguements are of the wrong type, are out of acceptable range or in conflict with eachother.

Here is the updated code:

Private Sub ExportBtn_Click()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordSet As New ADODB.Recordset
MyRecordSet.ActiveConnection = cnn

'Build the SQL Statement
Dim MySQL As String
MySQL = "SELECT [CondKey] , [AutoPop], [SalesOrg], [DistChannel], [CustomerID]"
MySQL = MySQL + "[KeyR], [PriceBreak_EffectDate],[UOM_EndDate], [Rate], [Currency_RateUnit]"
MySQL = MySQL + "[Per],[Keyx] , [KeyY], [HeaderUOM]"
MySQL = MySQL + "FROM tbl_Custom_Pricing_Template_Data_Flats"

MyRecordSet.Open MSQL

'template file as used to create SAP Excel Template File
Dim MySheetPath As String

'Set the path for the template file
MySheetPath = "C:\temp\zbp1template.xls"

' Set up object variables to refer to Excel and Objects

Dim Xl As Object
Dim Xlbook As Object
Dim Xlwks As Object

'Open an instance of Excel, open the workbook
Set Xl = CreateObject("Excel.Application")
Set ObjectVarName = GetObject(MySheetPath)

'Make Excel workbook visible on screen
Xl.Visible = True
Xlbook.Windows(1).Visible = True

'Define first sheet in workbook as Xlwks
Set Xlwks = Xlbook.Worksheets(1)

'Set Header Rows & Copy Data from Access Table "tbl_Custom_Pricing_Template_Data_Flats" to Excel Worksheet
Xlwks.Range("A1") = "BGR00"
Xlwks.Range("B1") = "0"
Xlwks.Range("C1") = "ZBP1907"
Xlwks.Range("D1") = "<SY-MANDT> OPTIONAL"
Xlwks.Range("E1") = "<SY-UNAME> OPTIONAL"
Xlwks.Range("A2") = "BKOND1"
Xlwks.Range("B2") = "1"
Xlwks.Range("C2") = "XK15"
Xlwks.Range("A3:X1500").Locked = False


'Save Workbook
Xlbook.Save

'Clean and set worksheet to screen
Set Xl = Nothing
Set Xlbook = Nothing
Set XlSheet = Nothing

End Sub

 

[boblarson]

What's with this:
Set ObjectVarName = GetObject(MySheetPath)


You don't even have ObjectVarName declared. But, to get the workbook, you use (plus I removed the Xlbook.Windows(1).Visible = True because unless it is hidden, it isn't necessary):

Dim Xl As Object
Dim Xlbook As Object
Dim Xlwks As Object

'Open an instance of Excel, open the workbook
Set Xl = CreateObject("Excel.Application")
Set Xlbook = Xl.Workbooks.Open(MySheetPath)

'Make Excel workbook visible on screen
Xl.Visible = True




Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com