UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export Query To Excel With Converted Hyperlink To Text Display Hyperlink, Access 2007    
 
   
phil_andre
post Jan 24 2018, 10:49 PM
Post#1



Posts: 220
Joined: 7-December 16



Good Day UC Team,

Please advise, i have a query contains of hyperlinks and i would like to export it in excel file with converted hyperlinks to text display hyperlink.

because everytime when i paste it in excel , i need to modify it one by one to change the text display.

also i research in the net for the excel formula but it's complicated for me, either need to add macro in excel or modify it in xml which is too tasky for me.

if possible to convert direct from access to excel then it would be great...

my hyperlink reference too much lengthy. i want to make it short display.


thanks a lot in advance.

Phil Andre.
Go to the top of the page
 
doctor9
post Jan 25 2018, 10:50 AM
Post#2


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Phil,

Can you give a specific example of:

1. What your hyperlink is (the actual address and the display text)
2. What you get when you currently export to Excel
3. What you WANT to get when you export to Excel

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
phil_andre
post Jan 25 2018, 10:29 PM
Post#3



Posts: 220
Joined: 7-December 16



Good Morning Doc,

Here is the attached sample. Kindly check.

When double click, need this result to be exported in excel and converting hyperlinks into "Yes" text display instead of displaying full directory text diplay...

Because everytime i have to copied the query result / datasheet and paste to excel and modifying the links text diplay to Yes one by one. Better to be automatic or batch update.


Attached File(s)
Attached File  Capture.JPG ( 42.93K )Number of downloads: 0
 
Go to the top of the page
 
cheekybuddha
post Jan 26 2018, 03:41 AM
Post#4


UtterAccess VIP
Posts: 10,347
Joined: 6-December 03
From: Telegraph Hill


Hi,

1. What datatype is field Photo in your Access table?

2. Please post the whole code you use to perform the export to Excel.

--------------------


Regards,

David Marten
Go to the top of the page
 
phil_andre
post Jan 26 2018, 04:32 AM
Post#5



Posts: 220
Joined: 7-December 16



datatype is : Hyperlink
Go to the top of the page
 
phil_andre
post Jan 26 2018, 04:34 AM
Post#6



Posts: 220
Joined: 7-December 16



Private Sub Remarks_DblClick(Cancel As Integer)

DoCmd.OutputTo acOutputForm, "frm_StockListAQ", acFormatXLS, "StockListAQ.xls", True

End Sub
Go to the top of the page
 
cheekybuddha
post Jan 26 2018, 08:25 AM
Post#7


UtterAccess VIP
Posts: 10,347
Joined: 6-December 03
From: Telegraph Hill


Hi,

To do what you want, you will need to add the hyperlinks to the Excel file after you have output it from the table.

Are you sure you want .xls format (<= Excel 2003) and not .xlsx format (>Excel 2007)?

Due to the vagaries of automating Excel from Access, it's necessary to use a temporary Excel file and then use Save As to get the final file.

Try this code - note where you must change your real values:
CODE
  Dim strForm As String, strExcelFile As String, strSavePath As String, _
      iRow As Integer, iLastRow As Integer, iHyperlinkCol As Integer
      
  Const cYES As String = "Yes", cTMPXLS As String = "tmp.xlsx", _
        xlUp As Integer = -4162, xlWorkbookDefault = 51, _
        XLSX_FORMAT As String = "Excel Workbook (*.xlsx)"
  
  strForm = "frm_StockListAQ"               ' <-- Set this to the name of your form
  strExcelFile = "StockListAQ.xlsx"         ' <-- Set this to the name of the output file
  strSavePath = CurrentProject.Path & "\"   ' <-- Set this to the path where you want to save the Excel file
  iHyperlinkCol = 2                         ' <-- Set this to the column number containing the hyperlinks (eg A=1, B=2, etc)

' Output form to .xlsx format excel file called "tmp.xlsx" and open Excel
  DoCmd.OutputTo acOutputForm, strForm, XLSX_FORMAT, strSavePath & cTMPXLS, True
  
' Get a reference to the Excel application
  With GetObject(, "Excel.Application")
'   Get a reference to the newly opened/outputted workbook
    With .Workbooks(cTMPXLS)
'     Get a reference to the worksheet
      With .Worksheets(strForm)
'       Find the last row
        iLastRow = .Cells(.Rows.Count, iHyperlinkCol).end(xlUp)(1).Row
'       Loop from second row (first contains headers) and change to hyperlinks
        For iRow = 2 To iLastRow
          .Hyperlinks.Add Anchor:=.Cells(iRow, iHyperlinkCol), _
                          Address:=.Cells(iRow, iHyperlinkCol).Text, _
                          TextToDisplay:=cYES
        Next iRow
      End With
'     Save the temporary workbook with the desired workbook name
      .SaveAs strSavePath & strExcelFile, xlWorkbookDefault
    End With
  End With
  
  Kill strSavePath & cTMPXLS                ' <-- Delete tmp output file
  Kill strSavePath & "*.xlk"                ' <-- Delete extra backup file that is created


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
phil_andre
post Jan 26 2018, 12:39 PM
Post#8



Posts: 220
Joined: 7-December 16



Good Evening Cheeky,

Thanks for the code but i have a problem., it does not change the text display on my hyper link in column 2. See attached images.
Attached File(s)
Attached File  P1.JPG ( 16.77K )Number of downloads: 0
Attached File  P2.JPG ( 116.8K )Number of downloads: 4
 
Go to the top of the page
 
cheekybuddha
post Jan 31 2018, 07:53 PM
Post#9


UtterAccess VIP
Posts: 10,347
Joined: 6-December 03
From: Telegraph Hill


Is Excel open by that stage (after the previous line executes)?

--------------------


Regards,

David Marten
Go to the top of the page
 
phil_andre
post Jan 31 2018, 10:30 PM
Post#10



Posts: 220
Joined: 7-December 16



Hi Sir David,

Actually NO , but now i open first the excel then it works perfectly now, it changed all the text display.

so you should open first any excel file then it will works only.

thank you very much David.
Go to the top of the page
 
cheekybuddha
post Feb 1 2018, 03:13 AM
Post#11


UtterAccess VIP
Posts: 10,347
Joined: 6-December 03
From: Telegraph Hill


It can be done without opening Excel at all if you want, but your original code opened it so I assumed that is what you wanted.

The last argument in the DoCmd.OutputTo in my code is 'True' which opens Excel after exporting.

Let me know if you would prefer any different.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 1 2018, 03:14 AM
Post#12


UtterAccess VIP
Posts: 10,347
Joined: 6-December 03
From: Telegraph Hill


There may be a timing issue between Excel opening and the next line of code running - I will have to get back to you later

--------------------


Regards,

David Marten
Go to the top of the page
 
phil_andre
post Feb 1 2018, 06:02 AM
Post#13



Posts: 220
Joined: 7-December 16



Sir David,

Please find attached, i added msgbox that you have to open first the excel in order to update text display in the hyperlinks.

and it would be more appreciated if you can show the different way, like without opening first the excel sheet.
Attached File(s)
Attached File  TextHyperlink.zip ( 27.63K )Number of downloads: 6
 
Go to the top of the page
 
phil_andre
post Feb 9 2018, 10:39 PM
Post#14



Posts: 220
Joined: 7-December 16



Dear UC Team / Mr. David,

Need your help asap, instead to display all YES in costant mode, i would like to display each line as per the description field... i am currently in constant but when i tried to change "YES" to ([fieldname]) it gives error that my VBA is constant.

I assume the field name is the description code that shown like below.

Description
A0001 and the photo column will display text A0001.jpg
A0002 and the photo column will display text A0002.jpg

appreciate your quick feedback.

thanks much.
Go to the top of the page
 
cheekybuddha
post Feb 10 2018, 04:04 PM
Post#15


UtterAccess VIP
Posts: 10,347
Joined: 6-December 03
From: Telegraph Hill


Hi,

You were on the right track!

Change to:
CODE
' ...
        For iRow = 2 To iLastRow
          .Hyperlinks.Add Anchor:=.Cells(iRow, iHyperlinkCol), _
                          Address:=.Cells(iRow, iHyperlinkCol).Text, _
                          TextToDisplay:=.Cells(iRow, iHyperlinkCol).Text
        Next iRow
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 10 2018, 04:09 PM
Post#16


UtterAccess VIP
Posts: 10,347
Joined: 6-December 03
From: Telegraph Hill


Oops!

If you just want the file name to display without the path then do:
CODE
' ...
  Dim strForm As String, strExcelFile As String, strSavePath As String, _
      iRow As Integer, iLastRow As Integer, iHyperlinkCol As Integer, _
      strFileName As String
' ...
        For iRow = 2 To iLastRow
           strFileName = Mid(.Cells(iRow, iHyperlinkCol).Text, InStrRev(.Cells(iRow, iHyperlinkCol).Text, "\") + 1)
          .Hyperlinks.Add Anchor:=.Cells(iRow, iHyperlinkCol), _
                          Address:=.Cells(iRow, iHyperlinkCol).Text, _
                          TextToDisplay:=strFileName
        Next iRow
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
phil_andre
post Feb 10 2018, 10:56 PM
Post#17



Posts: 220
Joined: 7-December 16



Sir David, thank of your valauable feedback, the text display YES is need to display as codeno field as per my attached revised demo. kindly check please and revert asap.

here i got error attempting to change the "Yes"

Const cYES As String = ([CodeNo]), cTMPXLS As String = "tmp.xlsx", _


Also in this demo, it needs two export version output.

1. Exporting to excel as per MainForm ID which is TestID
2. Exporting to excel all record in tbl_TestDetails / query, it is similar on the first demo , only the yes need to convert as per the ([codeno]) field record.

In previous demo it was C:\Test\A0001.jpg hyperlink and it changed to Yes on previous demo but now need to display to A0001 ,

C:\Test\A0002.jpg hyperlink and it changed to Yes on previous demo but now need to display to A0002 , and so on.

Appreciate your usual advise and support. thanks.


Attached File(s)
Attached File  TextHyperlink_Phil.zip ( 81.4K )Number of downloads: 7
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 06:01 AM