[AccessD] A2003: Excel Question - Exporting Values as hyperlinks

Hi Paul,
Many many thanks - worked straight out of the box. Excellent. 
I'll now spend the next half day getting it to work with existing
frameworks.

Again many thanks

D

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland
Sent: Wednesday, 3 July 2013 6:35 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2003: Excel Question - Exporting Values as
hyperlinks

Darren,

Just done a quick test and works, see below (watch for line breaks etc):

Function TestExport()
    Dim dbConn As New ADODB.Connection  ' for your database connection
    Dim rsSelect As New ADODB.Recordset ' for your recordset to export
    Dim xlApp As Object         ' will be your excel object
    Dim xlWb As Object          ' will be your excel workbook
    Dim xlSht As Object         ' will be your excel sheet

    Dim lngFldCount As Long         ' used to get number of fields in
recordset
    Dim lngRecCount As Long         ' used to get number of records
    Dim lngCol As Long          ' used to loop through field names (if
required)
    Dim lngRow As Long          ' Used in loop to run through recordset
    Dim intHyper As Integer         ' Used for position of hyperlink field
    Dim strPath As String       ' used to hold pathname for hyperlink

    ' open your database connection using dbConn
    ' open your recordset
    Set dbConn = CurrentProject.Connection
    Set rsSelect = New Recordset
    rsSelect.Open "Select * From Table1", dbConn, adOpenDynamic,
adLockReadOnly

    ' create excel object
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlSht = xlWb.Worksheets("Sheet1")

    ' if you do not require field names skip this bit
    ' the loop below will put field names on the first row of excel sheet
    lngFldCount = rsSelect.Fields.Count
    For lngCol = 1 To lngFldCount ' may have to put -1 after lngFldCount
(can't remember off hand)
        xlSht.Cells(1, lngCol) = rsSelect.Fields(lngCol - 1).Name
    Next lngCol
    ' end of skip this bit

    ' copy entire recordset into excel starting at row 2 column 1
    xlSht.Cells(2, 1).CopyFromRecordset rsSelect

    ' now this is the bit I may not get right, but don't think will be too
far off
    ' now we want to try to change a column to hyperlink this involves a
loop
    ' that will stop when column 1 is empty, you will need to know the
position of the hyperlink ' field and assign it to intHyper
    lngRecCount = rsSelect.RecordCount
    intHyper = 2 ' (where 2 is position of hyperlink field)
    lngRow = 2
    Do Until xlSht.Cells(lngRow, 1) = ""
        strPath = xlSht.Cells(lngRow, intHyper)
        xlSht.Cells(lngRow, intHyper).Select
        xlSht.Cells(lngRow, intHyper).Hyperlinks.Add
Anchor:=xlSht.Cells(lngRow, intHyper), Address:=strPath,
TextToDisplay:=strPath
        lngRow = lngRow + 1
    Loop

    ' Save the Excel file, close the workbook and quit Excel
    xlWb.SaveAs "D:\put your path here.xls"
    xlWb.Close
    Set xlWb = Nothing
    xlApp.Quit
    Set xlApp = Nothing

    ' close your recordset
    rsSelect.Close
    Set rsSelect = Nothing
    ' close your database connection
    dbConn.Close
    Set dbConn = Nothing

End Function

Paul


On 3 July 2013 07:34, Paul Hartland <paul.hartland at googlemail.com> wrote:

> Used to manipulate Excel from VB6 (really must learn .net soon) at my 
> old company, I used to do the bit posted by Darryl "You can open XL, 
> create the workbook, copy the recordset and then loop thru the list 
> all from Access VBA." then if I knew the column position you can just 
> change that single column without the looping, I doubt I have got the 
> code (as was for a company I was employed by) but will have a look, if 
> not and I get time I will try to do a quick example for you (mind you 
> I am sure someone on the list has something somewhere).
>
> Paul
>
>
> On 3 July 2013 07:08, Darryl Collins
<darryl at whittleconsulting.com.au>wrote:
>
>> Hi Darren,
>>
>> Heading home shortly so I have to be fast.
>>
>> You can do it, but you would have to add it after you do the transfer 
>> (assuming you use that method).
>>
>> See : http://msdn.microsoft.com/en-us/library/office/ff822490.aspx
>>
>> For details.
>>
>> In short you would use code to loop thru the column and set the range 
>> value as the hyperlink address.
>>
>> That should do the trick for you.
>>
>> You could control this from access by setting your XL target as an 
>> object in Access VBA.
>>
>> If you need help with that from me, it is going to have to wait until 
>> tomorrow.
>>
>> You can open XL, create the workbook, copy the recordset and then 
>> loop thru the list all from Access VBA.
>>
>> Cheers
>> Darryl.
>>
>>
>>
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com [mailto:
>> accessd-bounces at databaseadvisors.com] On Behalf Of Darren
>> Sent: Wednesday, 3 July 2013 3:07 PM
>> To: Access Developers discussion and problem solving
>> Subject: [AccessD] A2003: Excel Question - Exporting Values as 
>> hyperlinks
>>
>> Hi Ya'll (Darryl et al)
>>
>> How do I output a recordset to excel and have the resulting excel doc 
>> treat one of the field's values (literal paths to docs) as a hyperlink?
>>
>> I have no knowledge or real experience with Excel automation apart 
>> from the "docmd.TransferSpreadsheet" command.
>>
>> I have values like "T:\SomeFolder\SomeNumberedFolder\somePDFFile.pdf"
>> stored in a field.
>>
>> I can export these to Excel using xfer spreadsheet but how do I make 
>> the value's 'clickable' in the resulting XL doc?
>>
>> I want users to click or double click the hyperlink field value and 
>> open the associated doc as per the stored path.
>>
>> Make sense?
>>
>>
>>
>> Many thanks in advance
>>
>> DD
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>>
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
>
>
> --
> Paul Hartland
> paul.hartland at googlemail.com
>



--
Paul Hartland
paul.hartland at googlemail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com