Solved Hyperlink remains in exported excel file from a form; wish to remove. (1 Viewer)

NikkiK1234

Member
Local time
Today, 12:41
Joined
Jun 22, 2020
Messages
32
Hello!

I have some hyperlinks in my tables and when I export these fields into excel from forms I get a string that looks something like this:

Family#http://www.google.com#

Is it possible to have the export omit the "#http://www.google.com#" so that the cell is just "Family"?

Thank you!
 

Isaac

Well-known member
Local time
Today, 09:41
Joined
Mar 14, 2017
Messages
1,497
You could probably utilize some Left, Right, Len, and/or Mid functions to figure out which part of the string to replace with "".

In fact, if the only pattern that exists is what you posted, you might even just use: Left(cell,find("#",cell)) (excel formula), or Instr() for vba.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:41
Joined
Oct 29, 2018
Messages
11,114
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.
 

NikkiK1234

Member
Local time
Today, 12:41
Joined
Jun 22, 2020
Messages
32
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.
I think I understand what you mean but in my project, I'm looking for the user to do everything from the form (query + tables are hidden/locked).
And I've created an export button that exports all the data filtered from the tables by the form. So I think I need to edit the individual fields of those tables that are being called by the form to have the hyperlink clickable but the path not shown in the export.

But I'm not sure how to do this.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:41
Joined
Sep 21, 2011
Messages
6,330
I think theDBguy means you would amend the data on the fly in the query?
Code:
tt="Family#http://www.google.com#"
? left(tt,instr(1,tt,"#")-1)
Family
so in the query
Code:
NewField:Left(OldField,Instr(1,OldField,"#")-1)
HTH
 

NikkiK1234

Member
Local time
Today, 12:41
Joined
Jun 22, 2020
Messages
32
I think theDBguy means you would amend the data on the fly in the query?
Code:
tt="Family#http://www.google.com#"
? left(tt,instr(1,tt,"#")-1)
Family
so in the query
Code:
NewField:Left(OldField,Instr(1,OldField,"#")-1)
HTH
Sorry, I'm not too familiar with Query calculations but my understanding is the calculation should include the field that I'm looking to change. I'm not sure where to post the code you made for me.

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
 

Attachments

Gasman

Enthusiastic Amateur
Local time
Today, 17:41
Joined
Sep 21, 2011
Messages
6,330
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.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 00:41
Joined
May 7, 2009
Messages
9,872
modify your query to look like this:

Scale: Replace(Mid([ScaleName], Instr([ScaleName], "#") + 1), "#", "")
 

NikkiK1234

Member
Local time
Today, 12:41
Joined
Jun 22, 2020
Messages
32
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.
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.
 

Isaac

Well-known member
Local time
Today, 09:41
Joined
Mar 14, 2017
Messages
1,497
Did you try what I posted to correct the problem post-export?
Alternately, you can correct it pre-export as several have said. You need to create a query that strips out everything after the # sign, and incorporate that query into however you are currently giving the user the tool to export it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:41
Joined
Sep 21, 2011
Messages
6,330
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?
 

NikkiK1234

Member
Local time
Today, 12:41
Joined
Jun 22, 2020
Messages
32
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?
One sec...
 
Last edited:

NikkiK1234

Member
Local time
Today, 12:41
Joined
Jun 22, 2020
Messages
32
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?
Your code does work in the query; I ran it and the hyperlink strings are gone "#.....#". Went I export using my form, I'm calling my previous union query and the hyperlink strings are still there. So my question is, how do I incorporate your query into my form without removing the hyperlinks until the export. That's where I'm confused now.

Thank you
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 17:41
Joined
Sep 21, 2011
Messages
6,330
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?
 

NikkiK1234

Member
Local time
Today, 12:41
Joined
Jun 22, 2020
Messages
32
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?
I never called the tables directly, they were put in a union query and I called that union query in a form.

I can use a query for the same fields but how do I add a hyperlink to a query? There's nothing in the design form that allows me to adjust the data type.

I just called the new query with the code you sent me and I can export without the hyperlinks, but when I use them in the forms, the hyperlink no longer exists and I get rows of "#Name?" in those fields.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:41
Joined
Sep 21, 2011
Messages
6,330
In some records in scalename you do not have any #, so you need to test for the character first.?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:41
Joined
Sep 21, 2011
Messages
6,330
The ones without #, I didn't put in any hyperlinks in that table.
So which table are we talking about.?
You have put up a DB with no instructions on what needs to be done to repeat the problem? :(

I just looked at the HLinkRemove query and worked backwards.
 

NikkiK1234

Member
Local time
Today, 12:41
Joined
Jun 22, 2020
Messages
32
tblTheme2 is the table without any hyperlinks. If you use the main search form "Form Search box" and search either "PH" or "CH" theme, you will see the hyperlinks for "Scale" and "Survey".

If you export those files via the "Export" button on the form. The output should have the path or hyperlink at the end of the two fields (ScaleName/Survey).

Thank you.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:41
Joined
Sep 21, 2011
Messages
6,330
Right, I could not see the Export button the form is so large.
Your source for your tempquery is "Select * from [tblTheme Query] where [Theme]Like '*CH*'"

So you would need to amend that string to reflect the fields being used and not use * ?
I was expecting a dedicated query?
Not sure how to implement it otherwise.?

For the benefit of others the code is
Code:
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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom