Exporting selecting fields to excel

[kupe]

I need to export by code some fields from an access table into an excel spreadsheet.

The table has many fields, but I want to export only three or four fields. Is this possible with VBA please?

 

[Mile-O]

Select the tables with a query and export that.

 

[kupe]

Briliant, mate, thanks very much.

 

[noccy]

Make a query like Mile says, and use the Transferspreadsheet method to export it.


ah... too late =)

 

[Mile-O]

Remember in VBA to look up the TransferSpreadsheet method.

 

[kupe]

It's naming the fields that is the problem. I haven't found with the TransferSpreadsheet method how to stipulate the fields. A query of course gets round that.

 

[kupe]

Can a hyperlinked field be exported from Access into Excel and appear in Excel as hyperlinked please?

 

[Mile-O]

The only thing I can think of is to set a reference to Excel in Access and then, once you've exported the query to Excel. OPen it with Access and then find the hyperlinked column and edit it from Access with code like this:

Sub EditHyperlinks

    Dim strHyperlink As String

    Range("B2").Select
    Do While Not IsNull(ActiveCell) Or ActiveCell = vbNullString
        
        strHyperlink = Left(ActiveCell, InStr(1, ActiveCell.Value, "#") - 1)
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
            "http://" & strHyperlink, TextToDisplay:=strHyperlink
        ActiveCell.Offset(1, 0).Select
    Loop
    
End Sub

It's not perfect but it's a suggestion. I haven't tried it myself. (Well, I tried it as far as exporting a hyperlink to Excel and writing some code to change it back to a hyperlink).

 

[kupe]

Thanks very much for that, MoP. Very impressive, appreciated very much.