Access to Excel

[Swi]

I am trying to export Access data as Excel using ADO. I have tried the following two methods below. I would like the data formatting to be kept for a field in my Access database that is a field type of Hyperllink.

The top method is coming out as a text field and the bottom method comes out as a general format.

Any ideas?

Thanks.

conn.Execute "SELECT * INTO [Excel 8.0;Database=" & _
ExcelFileName & "].[Sheet1] FROM MASTER WHERE [Status] = 0", NumOfRecExp

Set rs = conn.Execute("Master", , adCmdTable)

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset rs

'Save the Workbook and Quit Excel
oBook.SaveAs ExcelFileName
oExcel.Quit

'Close the connection
rs.Close

Swi

 

[Swi]

I wasn't very keen on doing it this way but:

For Each cell In oSheet.Range("AQ2:AQ" & FileCount + 1)
    If cell.Value <> "" Then
        url = cell.Value
        oSheet.Hyperlinks.Add Anchor:=cell, _
          Address:=url, TextToDisplay:=cell.Value
    End If
Next cell

Swi

 

[Swi]

Worked but now I am running into issues when this is emailed. The links work when the Excel file is in the path it was created but when moved to another folder the hyperlinks no longer work.

Swi

 

[Swi]

Looks like an Excel issue.

I had to change this setting on the machine that this app was running on.

Tools, Options, General, Web Options, Files, UNcheck "Update links on save"

Swi

 

[SkipVought]

I'd code the entire application in Excel, where the query and hyperlink would occur when the workbook is opened.