export access report into excel

[subs]

hi

I want to export access report into excel without any changes in
formatting- what are my options. Infact there are some already
predefined fields in excel . i want the fields in access report to
directly fit in the excel pre defined columns. Is there a macro in
Access or a query in exel where i can do that. Help will be greatly
appreciated.

Thanks in Advance

 

[Noëlla Gabriël]

Hi,

I usually do that with some VB programming, like in the followong example:

Public Sub AddTitles(strFile As String, intRows As Integer, astrTitles() As
String)
On Error GoTo Err_AddTitles

Dim appExcel As Excel.Application
Dim bksBooks As Excel.Workbooks
Dim wkbBook As Excel.Workbook
Dim wksSheet As Excel.Worksheet
Dim intArrRow As Integer


'Initiate all objects and variables
Set appExcel = GetObject(, "Excel.Application")
Set bksBooks = appExcel.Workbooks
Set wkbBook = bksBooks.Open(strFile)
Set wksSheet = wkbBook.Sheets(1)
wksSheet.Activate
ActiveCell.Rows("1:" & intRows + 1).EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
For intArrRow = 0 To intRows - 1
Range(astrTitles(intArrRow, 0)) = astrTitles(intArrRow, 1)
Next intArrRow
appExcel.Visible = True


Exit_AddTitles:
Exit Sub

Err_AddTitles:
If Err.Number = 429 Then 'excel not running
Set appExcel = CreateObject("excel.application")
Resume Next
Else
Call gsgErrorHandling
Resume Exit_AddTitles
End If

End Sub

 

[subs]

Hi,

I usually do that with some VB programming,  like in the followong example:

Public Sub AddTitles(strFile As String, intRows As Integer, astrTitles() As
String)
On Error GoTo Err_AddTitles

    Dim appExcel As Excel.Application
    Dim bksBooks As Excel.Workbooks
    Dim wkbBook As Excel.Workbook
    Dim wksSheet As Excel.Worksheet
    Dim intArrRow As Integer

    'Initiate all objects and variables
    Set appExcel = GetObject(, "Excel.Application")
    Set bksBooks = appExcel.Workbooks
    Set wkbBook = bksBooks.Open(strFile)
    Set wksSheet = wkbBook.Sheets(1)
    wksSheet.Activate
    ActiveCell.Rows("1:" & intRows + 1).EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    For intArrRow = 0 To intRows - 1
        Range(astrTitles(intArrRow, 0)) = astrTitles(intArrRow,1)
    Next intArrRow
    appExcel.Visible = True

Exit_AddTitles:
    Exit Sub

Err_AddTitles:
    If Err.Number = 429 Then 'excel not running
        Set appExcel = CreateObject("excel.application")
        Resume Next
    Else
        Call gsgErrorHandling
        Resume Exit_AddTitles
    End If

End Sub

--
Kind regards
Noëlla







- Show quoted text -

hi
Thanks for your help- Is this general or do i have to customize this
for my exel or access file. Pls help. Do i have to copy and paste this
in Access macro. Pls let me know the steps.
Thanks again

 

[Noëlla Gabriël]

sorry subs,

this isn't a copy and paste example, it's just an example of code to get you
started where to look if you want to work with excel objects in Access. This
works only from VBA modules.