access to excel

Hi!

I have access table (150 columns x 7000 records) and i want to have command that user can run and make xls file (export data from access to excel file).

Can anyone help with code?

Mitja
Mitja100Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richie_SimonettiIT OperationsCommented:
If you have opened an Access application throght automation and the object variable is called accapp you could use code silmilar to this:

accapp.DoCmd.DoMenuItem 'use option export to an extern database and pass the name of an Excel woorbook, table name and so on.
Hope it helps
0
AmickCommented:
Option Compare Database
Option Explicit

'------------------------------------------------------------
' Export_To_Excel - VBA
'
'------------------------------------------------------------
Function Export_To_Excel()
On Error GoTo Export_To_Excel_Err

    DoCmd.TransferSpreadsheet acExport, 8, "MyTable", "MyExcelFile.XLS", False, ""


Export_To_Excel_Exit:
    Exit Function

Export_To_Excel_Err:
    MsgBox Error$
    Resume Export_To_Excel_Exit

End Function


0
Mitja100Author Commented:
Ups!

I forgot to mention that access is not installed on computer where this app. will run, but excel is present.

Sorry!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mitja100Author Commented:
I found this and works just fine!

   Dim xlApp As Excel.Application
   Dim xlbook As Excel.Workbook
   Dim xlSheet As Excel.Worksheet
   Dim rs As Recordset
   Dim Counter As Integer


   Set xlApp = Excel.Application
   xlApp.Visible = True
   Set xlbook = xlApp.Workbooks.Open("c:\junk.xls")
   
   sql_caluse = "SELECT * FROM my_table "
    Set rs = DataEnvironment1.Connection1.Execute(sql_clause)
    rs.MoveFirst
   
   Set xlSheet = xlbook.Worksheets("Sheet1")
   Do Until rs.EOF
      Counter = Counter + 1
      With xlSheet
         .Cells(Counter, 1).Value = rs("Field1")
         .Cells(Counter, 2).Value = rs("Field2")
         .Cells(Counter, 3).Value = rs("Field3")
      End With
      rs.MoveNext
   Loop
0
NosterdamusCommented:
Hi Mitja100,

If Excel is not installed on the machine, then I'm not sure that your method will work propperly.

Try using the OutputTo method instead:

DoCmd.OutputTo acOutputTable, "TableName", acFormatXLS, "MyTable.xls", True

Hope this helps,

Nosterdamus



0
NosterdamusCommented:
Sorry, didn't notice Excel is present and Access is NOT...

Please ignore my last comment.

Nosterdamus
0
Éric MoreauSenior .Net ConsultantCommented:
Create a ADODB recordset containing your data and pass your recordset to this sub that will generate a CSV file (that Excel recognize natively):

Public Sub CopyRsToCSV(ByVal prstData As ADODB.Recordset, Optional ByVal pstrFileName As String)
Dim intFileNumber As Integer
Dim lngLoop As Long
Dim strX As String

    If Len(Trim$(pstrFileName)) = 0 Then pstrFileName = App.Path & "\zzRst.csv"
   
    'Open data file data to file
    intFileNumber = FreeFile   ' Get unused file
    Open pstrFileName For Output As #intFileNumber
    'Write data to file
    With prstData
        strX = ""
        For lngLoop = 0 To .Fields.Count - 1
            strX = strX & .Fields(lngLoop).Name & ", "
        Next lngLoop
        Print #intFileNumber, strX
        If .RecordCount > 0 Then .MoveFirst
        Do Until .EOF
            strX = ""
            For lngLoop = 0 To .Fields.Count - 1
                strX = strX & .Fields(lngLoop).Value & ", "
            Next lngLoop
            Print #intFileNumber, strX
            .MoveNext
        Loop
    End With
    'Close
    Close #intFileNumber
End Sub
0
Richie_SimonettiIT OperationsCommented:
It uses DAo engine to get the records:

Option Explicit
'set a reference to microsoft DAO 3.51 or 3.6 object library
'set areference to excel 8 object library
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet

Sub SaveXlsFile(sFileName As String)
xl.ActiveWorkbook.SaveAs sFileName
End Sub
Private Sub Form_Load()
Set xl = New Excel.Application
' open excel
With xl
    .Visible = True
    Set wb = .Workbooks.Add
    Set sh = .ActiveWorkbook.ActiveSheet
End With

'open database
Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Microsoft Visual Studio\VB98\nwind.mdb", False, False)
' populates recordset
Set rs = db.OpenRecordset("Clientes", dbOpenTable)
' show records on activesheet
sh.Range("a1").CopyFromRecordset rs

SaveXlsFile "c:\yourchoice.xls"
xl.Quit
Set xl = Nothing
Set rs = Nothing
db.Close
Set db = Nothing
End Sub




0
wileecoyCommented:
Mitja100, any progress on this?
0
NosterdamusCommented:
For Mitja100

It's time to resolve this question, so I will leave a recommendation in Community Support Topic Area that this question is:
- PAQ'd and points refunded

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Thanks!

Nosterdamus
0
NetminderCommented:
Per recommendation, points refunded and question closed.

Netminder
CS Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.