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.
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.
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.
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
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
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
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
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)
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
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)
Thanks alan It worked
Thanks for the help Itsme It worked