Exporting Data from Ms Access form to Excel sheet

[jj1]

Hi



I am trying to export certain values which are in combo box of MS Access to excel sheet. But what's happening is its populating ID of the field instead of Field Name. Any Advice is appreciated.

[ItsMe]

... its populating ID of the field instead of Field Name...

What is populating what? Can you post the code you are using and or explain the export process?

[alansidman]

In your query, you need to join the ID of the Field Name in your main table to the foreign key in your lookup table. Then bring the Named field into the query grid and uncheck the ID field.

[jj1]

Private Sub cmd_Print_Click()








Dim objexcel As Excel.Application
Dim objworkbook As Excel.Workbook
Dim objxlsheet As Worksheet
Set objexcel = CreateObject("Excel.Application")
''Set objworkbook = objexcel.Workbooks.Open("C:\MSEXCEL_TEST" & "\" & "test.xlsx")
Set objworkbook = objexcel.Workbooks.Open(CurrentProject.Path & "\" & "test.xlsx")
Set objxlsheet = objworkbook.ActiveSheet






objxlsheet.Cells(4, 30) = combo1
objxlsheet.Cells(5, 30) = combo2

objexcel.Visible = True
Set objexcel = Nothing
End Sub

[ItsMe]

Me.combo1.Column(0) = "Value in First Column"

Me.combo1.Column(1) = "Value in Second Column"

combo1 = Whichever column is defined as Bound Column in said combo's properties

[jj1]

That's the code I am using.

Alan say we have 2 tables Table 1 Main table collecting all Main Information whereas Table 2 say tableDepartment has names of departments. I run a query on table dept. to bring their Dept.ID AND Dept.Name. which fills the combodepart. on the mainform. which then by using the above code. I try to spill dept information populated on the form to Excel. But it takes ID of the Dept. instead of its Name which is showing on the main form

[jj1]

objxlsheet.Cells(4, 30) = combo1
objxlsheet.Cells(5, 30) = combo2

You want me to replace this completely or how do you want me do it. I mean
I wrote this way

Me.txtdept.Column(0) = ""

but its giving run time error 424 object required

[ItsMe]

Replace

objxlsheet.Cells(4, 30) = combo1
objxlsheet.Cells(5, 30) = combo2

with something like

objxlsheet.Cells(4, 30) = Me.combo1.Column(1)
objxlsheet.Cells(5, 30) = Me.combo2.Column(1)

[jj1]

Ya its bringing value on excel sheet as false when i used

objxlsheet.Cells(4, 30) = Me.combo1.Column(1)

But i think we are getting close to solve

[ItsMe]

You need to determine which column in your combo has the value you want to store in your spreadsheet. The index starts at 0 so 1 is the second column.
Me.combo1.Column(1)

[jj1]

Thanks alan It worked

[jj1]

Thanks for the help Itsme It worked