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" wrote in message
...
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