subform to excel (1 Viewer)

[bigmac]

hi all , can you help me please i have a subform that the user can select which colums to hid ,
for example sub form has three colums ( time ,date, and gender) if the user wishes to hid the gender column then all they have to do is right click the name of the column and click hide to get ride of it ,

BUT when i export it to excel it still shows the hidden column , how do i stop the hidden columns from showing in the excel sheet please

 

[MarkK]

Hmmnn, so gender varies with time? Is that what the data shows?

As far as the export goes, there are, give or take, about ten thousand ways to export to Excel from Access. It would help us help you if you gave more details about what, exactly, you are doing.
Mark

 

[bigmac]

hi, i am using the following code to create the excel book
Set xlApp = Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
'With xlBook
xlApp.Visible = False
With xlSheet
.Name = rname
.Cells.Font.Name = "Calibri"
.Cells.Font.Size = 11
With ActiveSheet.Pictures.Insert("C:\Users\phili\Desktop\weld maps\tetdd.jpg")
.Left = 100
End With
For cols = 1 To rs1.Fields.Count - 1
.Cells(10, cols + 2).Value = rs1.Fields(cols).Name
Next
.Range("b11").CopyFromRecordset rs1
i = i + 10


as i say this produces the excel book and exports all the data from the sub form to it but it also exports the hidden columns

 

[Uncle Gizmo]

Please send me the record source of the subform in SQL string format, something like:- Select From Where Orderby



Sent from my SM-G925F using Tapatalk

 

[JHB]

You need to use the property ColumnHidden for the control.
There is an example here in this thread, post #33.
https://access-programmers.co.uk/forums/showthread.php?t=298421&page=3

 

[bigmac]

hi JHB , this looks great but the show hide column box Is beyond my capabilities, how do you build this?

 

[MarkK]

To me it's not clear what you are doing. The code you posted is a fragment. There is a recordset in there you have not shown us how it is opened. And where is the subform? You want to hide the columns that are hidden in the subform, but where is the subform reference in your code? Is this code running on the subform? How is this code run?
Without knowing stuff like that it is impossible to know what the critical features are of what you are trying to do, and it is therefore very difficult to advise you with any certainty.
hth
Mark

 

[JHB]

hi JHB , this looks great but the show hide column box Is beyond my capabilities, how do you build this?

I didn't build that, (I only helped out by the same problem you've), but it is a Embedded Macro which trigger the menu command UnhideColumns. Actually it is the same as when you chose from the ribbon: "Records"->"More"->"Unhide Fields".