Changing the format of an Excel output file made by Microsoft Access

Hello,

I have a question about changing the format of a document made with OutputTo
command.

I have an Access database which I'm exporting some of it's data to an Excel
document. Guess the Access table is named "MyAccessTable". I'm using the
following command to export the data in that table to an excel worksheet and
open it with Excel:

DoCmd.OutputTo acOutputTable , "MyAccessTable",_
acFormatXLS,"C:\ExcelOutputFile.xls",True.

What I wish to do is that after the file is opened in Excel, a procedure in
Excel will run, making all the changes needed in the formatting of the new
Excel file.

I think I will handle writing the code for changing the format in excel, but
what I don't know is how to make that code run automatically right after the
file is made.

How can I make this procedure run right after the outputto command?
Where should the procedure/module code be stored? (In Access? In another
excel file?)


Kind Regards,
Amir.

I'd make sure the reformatting macro worked against the activesheet. Then store
that macro in its own workbook. (I don't use Access, but I think this'll work.)

Then in your code that creates the exceloutputfile.xls, you can just open excel,
open both the macro workbook and the exceloutputfile.xls workbook. Go to that
first sheet and run the reformatting macro. Close the macro workbook (w/o
saving). Then close the other workbook (w/saving).

My macro that did the reformattin was called: myRefMac

This worked ok for me in MSWord.

Option Explicit
Sub RunMe()

Dim XLApp As Object
Dim XLMacWkbk As Object
Dim xlDataWkbk As Object
Dim wkbkNames(1 To 2) As String
Dim XLWasRunning As Boolean
Dim testStr As String
Dim iCtr As Long

wkbkNames(1) = "C:\my documents\excel\book1.xls" 'macro workbook
wkbkNames(2) = "C:\my documents\excel\book2.xls" 'created from access

For iCtr = LBound(wkbkNames) To UBound(wkbkNames)
testStr = ""
On Error Resume Next
testStr = Dir(wkbkNames(iCtr))
On Error GoTo 0
If testStr = "" Then
MsgBox wkbkNames(iCtr) & " wasn't found!"
Exit Sub
End If
Next iCtr

XLWasRunning = True
On Error Resume Next
Set XLApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set XLApp = CreateObject("Excel.Application")
XLWasRunning = False
End If

XLApp.Visible = True 'at least for testing!

Set XLMacWkbk = XLApp.workbooks.Open(FileName:=wkbkNames(1))
Set xlDataWkbk = XLApp.workbooks.Open(FileName:=wkbkNames(2))

XLApp.GoTo xlDataWkbk.worksheets(1).Range("a1")

XLApp.Run XLMacWkbk.Name & "!myRefMac"

XLMacWkbk.Close savechanges:=False
xlDataWkbk.Close savechanges:=True

If XLWasRunning Then
'leave it running
Else
XLApp.Quit
End If

Set XLMacWkbk = Nothing
Set xlDataWkbk = Nothing
Set XLApp = Nothing

End Sub

Amir wrote:
>
> Hello,
>
> I have a question about changing the format of a document made with OutputTo
> command.
>
> I have an Access database which I'm exporting some of it's data to an Excel
> document. Guess the Access table is named "MyAccessTable". I'm using the
> following command to export the data in that table to an excel worksheet and
> open it with Excel:
>
> DoCmd.OutputTo acOutputTable , "MyAccessTable",_
> acFormatXLS,"C:\ExcelOutputFile.xls",True.
>
> What I wish to do is that after the file is opened in Excel, a procedure in
> Excel will run, making all the changes needed in the formatting of the new
> Excel file.
>
> I think I will handle writing the code for changing the format in excel, but
> what I don't know is how to make that code run automatically right after the
> file is made.
>
> How can I make this procedure run right after the outputto command?
> Where should the procedure/module code be stored? (In Access? In another
> excel file?)
>
> Kind Regards,
> Amir.

--

Dave Peterson

Hi,
I've finally changed your code a bit but it works fine with Access.

Thank you very much Dave!

Kind Regards,
Amir.

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> I'd make sure the reformatting macro worked against the activesheet. Then
> store
> that macro in its own workbook. (I don't use Access, but I think this'll
> work.)
>
> Then in your code that creates the exceloutputfile.xls, you can just open
> excel,
> open both the macro workbook and the exceloutputfile.xls workbook. Go to
> that
> first sheet and run the reformatting macro. Close the macro workbook (w/o
> saving). Then close the other workbook (w/saving).
>
> My macro that did the reformattin was called: myRefMac
>
> This worked ok for me in MSWord.
>
> Option Explicit
> Sub RunMe()
>
> Dim XLApp As Object
> Dim XLMacWkbk As Object
> Dim xlDataWkbk As Object
> Dim wkbkNames(1 To 2) As String
> Dim XLWasRunning As Boolean
> Dim testStr As String
> Dim iCtr As Long
>
> wkbkNames(1) = "C:\my documents\excel\book1.xls" 'macro workbook
> wkbkNames(2) = "C:\my documents\excel\book2.xls" 'created from access
>
> For iCtr = LBound(wkbkNames) To UBound(wkbkNames)
> testStr = ""
> On Error Resume Next
> testStr = Dir(wkbkNames(iCtr))
> On Error GoTo 0
> If testStr = "" Then
> MsgBox wkbkNames(iCtr) & " wasn't found!"
> Exit Sub
> End If
> Next iCtr
>
> XLWasRunning = True
> On Error Resume Next
> Set XLApp = GetObject(, "Excel.Application")
> If Err.Number <> 0 Then
> Set XLApp = CreateObject("Excel.Application")
> XLWasRunning = False
> End If
>
> XLApp.Visible = True 'at least for testing!
>
> Set XLMacWkbk = XLApp.workbooks.Open(FileName:=wkbkNames(1))
> Set xlDataWkbk = XLApp.workbooks.Open(FileName:=wkbkNames(2))
>
> XLApp.GoTo xlDataWkbk.worksheets(1).Range("a1")
>
> XLApp.Run XLMacWkbk.Name & "!myRefMac"
>
> XLMacWkbk.Close savechanges:=False
> xlDataWkbk.Close savechanges:=True
>
> If XLWasRunning Then
> 'leave it running
> Else
> XLApp.Quit
> End If
>
> Set XLMacWkbk = Nothing
> Set xlDataWkbk = Nothing
> Set XLApp = Nothing
>
> End Sub
>
> Amir wrote:
>>
>> Hello,
>>
>> I have a question about changing the format of a document made with
>> OutputTo
>> command.
>>
>> I have an Access database which I'm exporting some of it's data to an
>> Excel
>> document. Guess the Access table is named "MyAccessTable". I'm using the
>> following command to export the data in that table to an excel worksheet
>> and
>> open it with Excel:
>>
>> DoCmd.OutputTo acOutputTable , "MyAccessTable",_
>> acFormatXLS,"C:\ExcelOutputFile.xls",True.
>>
>> What I wish to do is that after the file is opened in Excel, a procedure
>> in
>> Excel will run, making all the changes needed in the formatting of the
>> new
>> Excel file.
>>
>> I think I will handle writing the code for changing the format in excel,
>> but
>> what I don't know is how to make that code run automatically right after
>> the
>> file is made.
>>
>> How can I make this procedure run right after the outputto command?
>> Where should the procedure/module code be stored? (In Access? In another
>> excel file?)
>>
>> Kind Regards,
>> Amir.
>
> --
>
> Dave Peterson