Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

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
0
Mitja100
Asked:
Mitja100
  • 3
  • 2
  • 2
  • +4
1 Solution
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now