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

Darren:

I've always done this through code using automation.  The formatting is hard
to figure out but easy if you record your keystrokes in Excel in a macro
then look at the code it generates, copy and paste to your Access module -
kind of like the cheat of using the QBE grid to fashion a SQL statement and
cribbing it out of the SQL view of the query.

You have to define the Excel objects:

' Object variables for Automation stuff
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLWS As Excel.Worksheet

Then set them:

' Create Excel Spreadsheet
Set objXLApp = New Excel.Application

'Set objXLApp = objXLBook.Parent
objXLApp.Workbooks.Add
Set objXLWS = objXLApp.ActiveSheet

Write out the data:

'write it out - first 3 columns first, the the rest in account number order
For intThisCol = 1 To 3
    For intThisRow = 1 To intRows
            objXLWS.Cells(intThisRow, intThisCol) = Aray(intThisRow,
intThisCol)
    Next intThisRow
Next intThisCol

'Now write out the rest of the columns in account number order
strCurrentAccount = "00000"
For intColOut = 4 To intCols - 1
    intThisCol = GetNextAccount
    
    For intThisRow = 1 To intRows
        objXLWS.Cells(intThisRow, intColOut) = Aray(intThisRow, intThisCol)
    Next intThisRow
    
    If intThisCol = 4 Then
        For intThisRow = intTotalRow + 1 To intRows
            objXLWS.Cells(intThisRow, 4) = Aray(intThisRow, intThisCol)
        Next intThisRow
    End If
    
Next intColOut

' Last write out the last 'Net' column
For intThisRow = 1 To intRows
    objXLWS.Cells(intThisRow, intCols) = Aray(intThisRow, intCols)
Next intThisRow

' Bottom Stuff
objXLWS.Cells(intRows, 3) = "Total 10400 minus Total CreditCards"
objXLWS.Cells(intRows, 4) = sglTotal10400 - sglTotalCC
intRows = intRows + 2
objXLWS.Cells(intRows, 3) = "Total 10610 Debits"
For intI = 1 To intCols
    If Aray(2, intI) = "10610" Then
        objXLWS.Cells(intRows, 6) = Aray(intTotalRow, intI)
        
    End If
Next intI

intRows = intRows + 2
For intThisCol = 1 To intCols
    If Aray(2, intThisCol) = "10420" Then Exit For
Next intThisCol

objXLWS.Cells(intRows, 3) = "Total 10420 Debits"
objXLWS.Cells(intRows, 5) = Aray(intTotalRow, intThisCol)
For intI = intTotalRow + 2 To intTotalRow + 5
    objXLWS.Cells(intRows, 5) = objXLWS.Cells(intRows, 5) -
Nz(objXLWS.Cells(intI, 5))
Next intI


Then you can add any formatting you like:

' Format the spreadsheet

' Print Gridlines
    With objXLApp.ActiveSheet.PageSetup
        .PrintGridlines = True
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 2
        .FitToPagesTall = 1
    End With
    
' Currency format for numbers
    For intThisCol = 4 To intCols
        objXLWS.Columns(intThisCol).NumberFormat = "0.00_);[Red](0.00)"
    Next intThisCol
    objXLWS.Rows(2).NumberFormat = "#####"

' Format row 1 & 2
    objXLWS.Rows(1).RowHeight = 50
    objXLWS.Rows(1).WrapText = True
    objXLWS.Rows(1).HorizontalAlignment = xlRight
    objXLWS.Cells(2, intCols).HorizontalAlignment = xlRight
    objXLWS.Rows(1).Font.ColorIndex = 11
    objXLWS.Rows(2).Font.ColorIndex = 11
    objXLWS.Cells(1, 1).HorizontalAlignment = xlCenter
    objXLWS.Cells(1, 1).VerticalAlignment = xlCenter
    
' Format Bold Some Rows
    objXLWS.Rows(1).Font.Bold = True
    objXLWS.Rows(2).Font.Bold = True
    objXLWS.Rows(intTotalRow).Font.Bold = True
    
' Column Widths
    objXLWS.Columns.EntireColumn.AutoFit

' Make the spreadsheet visible
    objXLApp.Visible = True

End Function

Now all that formatting I didn't write - I actually couldn't figure out how
to do it. Couldn't find the documentation anywhaere.  Then someone -
probably on the list - told me to record the macro in Excel and copy out the
marco code.  Turns out that ANYTHING you can do in Excel you can do to a
spreadsheet from Access.

HTH

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren
Sent: Tuesday, July 02, 2013 10: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