Paul Hartland
paul.hartland at googlemail.com
Thu Jul 4 03:48:56 CDT 2013
Darren,
No problem at all, I have been on this list for many years and hardly get
the chance to help anyone with all expertise on here, it's good to do so
every so often for all the help that I have received over the years
Paul
On 4 July 2013 00:30, Darren <darren at activebilling.com.au> wrote:
> 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
>
> --
> 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