Hi. Rather than exporting your table, you could try creating a query that extracts only the data you want and then export the query to Excel.
Hi. Rather than exporting your table, you could try creating a query that extracts only the data you want and then export the query to Excel.
tt="Family#http://www.google.com#"
? left(tt,instr(1,tt,"#")-1)
Family
NewField:Left(OldField,Instr(1,OldField,"#")-1)
HTH
I think theDBguy means you would amend the data on the fly in the query?
so in the queryCode:tt="Family#http://www.google.com#" ? left(tt,instr(1,tt,"#")-1) Family
Code:NewField:Left(OldField,Instr(1,OldField,"#")-1) HTH
That is not what you originally asked?, you just asked to remove anything after the #.I have an image of a field that I want to remove the #......# from the excel but still want to have the hyperlink work in the form.
Thank you
That is not what you originally asked?, you just asked to remove anything after the #.
I believe you would need to leave it as it is if you still want the link to work.
Not something I have done, and I'm very busy trying to get a laptop back up and running, so I will bow out. Sorry.
Ok, I misread, sorry. What I supplied does not touch the table, just changes it on the fly as you export the data to Excel.?My apologies. I probably should have been more specific. I want the hyperlink to work in the table/form. But when I export the data from the form, I want to have the hyperlink (#....#) removed in the exported file.
I'm not sure if this is possible.
Ok, I misread, sorry. What I supplied does not touch the table, just changes it on the fly as you export the data to Excel.?
I *thought* you said you wanted the link to work in Excel as well?
Ok, I misread, sorry. What I supplied does not touch the table, just changes it on the fly as you export the data to Excel.?
I *thought* you said you wanted the link to work in Excel as well?
You remove the links in the export. Instead of perhaps using the table directly, you now use a query for the same fields and amend those with hyperlinks in the same way that has been demonstrated.?
So apply the same logic to your union query?
In some records in scalename you do not have any #, so you need to test for the character first.?
So which table are we talking about.?The ones without #, I didn't put in any hyperlinks in that table.
Private Sub Command28_Click()
10 On Error GoTo Command28_Clickerr
Dim fDialog As Office.FileDialog
Dim vSelected As Variant
Dim sFolder As String
Dim myquery As String
20 Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
30 With fDialog
' Set the title of the dialog box.
40 .Title = "Select Folder to Export Tables To In Excel Format"
50 .Filters.Clear
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
60 If .Show = True Then
70 For Each vSelected In .SelectedItems
80 sFolder = vSelected
90 Next vSelected
100 Else
110 MsgBox "You clicked Cancel in the file dialog box."
120 Exit Sub
130 End If
140 End With
myquery = currentrsrc
Dim qrydef As DAO.QueryDef
Dim db As DAO.Database
Set db = CurrentDb
Set qrydef = db.CreateQueryDef("tempQuery", myquery)
190 On Error Resume Next
200 Kill sFolder & "\exported.XLSX"
210 On Error GoTo Command28_Clickerr
220 DoCmd.TransferSpreadsheet acExport, , "tempQuery", sFolder & "\exported.XLSX"
230 DoCmd.RunSQL "DROP TABLE tempQuery"
240 DoEvents
250 Application.FollowHyperlink sFolder & "\exported.XLSX"
260 Exit Sub
Command28_Clickerr:
If Err.Number = 3012 Then
DoCmd.RunSQL "DROP TABLE tempQuery"
DoEvents
Resume
Else
270 MsgBox Err.Number & " " & Err.Description & " " & Erl, vbOKOnly, "Error in Command28_Clickerr"
End If
End Sub