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

[NikkiK1234]

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]

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]

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]

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]

I think theDBguy means you would amend the data on the fly in the query?

tt="Family#http://www.google.com#"
? left(tt,instr(1,tt,"#")-1)
Family

so in the query

NewField:Left(OldField,Instr(1,OldField,"#")-1)
HTH

 

[NikkiK1234]

I think theDBguy means you would amend the data on the fly in the query?

tt="Family#http://www.google.com#"
? left(tt,instr(1,tt,"#")-1)
Family

so in the query

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

 

[Gasman]

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]

modify your query to look like this:

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

 

[NikkiK1234]

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]

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]

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]

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...

 

[NikkiK1234]

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

 

[Gasman]

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]

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]

In some records in scalename you do not have any #, so you need to test for the character first.?

 

[NikkiK1234]

In some records in scalename you do not have any #, so you need to test for the character first.?

The ones without #, I didn't put in any hyperlinks in that table.

 

[Gasman]

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]

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]

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

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