How do I Format Excel after exporting the report

[Din FL]

I used to know how, but it has been too long. Can someone please tell me how
to open EXCEL and then send the formating instructions?

Thanks

 

[Klatuu]

You have to use Automation to open or create an instance of an Excel
spreadsheet, then do the formatting using VBA.
That is the short answer. It takes a lot of code.
One shortcut method is to do the formatting in Excel while recording a
macro. Then copy the code the macro creates into your Access module and
modify it for the correct syntax to address the Excel object.

 

[Din FL]

Okay,

I already recorded my macro, but I don't know how to code the
EXcel.application statement.

I found another response from you and started using it

Option Explicit ' Use this to make sure your variables are defined
Private objExcel As Excel.Application

Private xlWB As Excel.Workbook

Private xlWS As Excel.worksheet


But this gives me a "USER TYpe" not defined error.

How do I deal with that?

Thanks
DD

 

[Klatuu]

Not sure. And I don't remember using Private variables, but here is some
sample code that may help.

'D Hargis 3/2005 - Create the Report in an Excel Spreadsheet
Sub Build_XL_Report(strOutPut As String)
Const conLightGray As Long = 12632256
Const conLightBlue As Long = 16777164
Const conLightYellow As Long = 10092543

Dim xlApp As Object 'Application Object
Dim xlBook As Object 'Workbook Object
Dim xlSheet As Object 'Worksheet Object
Dim varGetFileName As Variant 'File Name with Full Path
Dim rstSCCB As Recordset 'Recordset to load data from
Dim rstItms As Recordset 'Recordset to load ITM Name in Header
Dim qdf As QueryDef 'Query def to load data
Dim lngItmCount As Long 'Number of ITMs in the RecordSet
Dim lngDetailCount As Long 'Number of Detail Data rows in the recordset
Dim intX As Integer 'Loop Counter
Dim strMonth As String 'Used to create a Short month name ie
January to Jan
Dim strCurrItm As String 'Hold the ITM Name to format Total cell
Dim lngRowCount As Long 'A loop counter that gives the current row
reference
Dim lngTotalPos As Long 'Used to format ITM Total cells
Dim strPrintArea As String 'Defines the print area for the sheet
Dim strTitleRows As String 'Defines the rows to print at the top of
each page
Dim strLeftRange As String 'Used to format range references
Dim strRightRange As String 'Used to format range references
Dim lngFirstDataRow As Long 'The first row with detail data
Dim lngLastDataRow As Long 'The last row with detail data
Dim blnExcelWasNotRunning As Boolean
Dim strDefaultDir 'Where to save spreadsheet
Dim strDefaultFileName 'Name to Save as
Dim lngFlags As Long 'Flags for common dialog
Dim strFilter As String 'File Display for Common Dialog
Dim strCurrMonth As String 'To create directory name for save
Dim strCurrYear As String 'To create directory name for save
Dim blnStopXl As Boolean 'Leave Open for Spreadsheet Version

On Error GoTo Build_XL_Report_ERR

DoCmd.Hourglass (True)
Me.txtStatus = "Updating Queries"
Me.txtStatus.Visible = True
'Fix the Queries so you dont have to be hand each month
Call FixSql("qselsccbactual", "actual_res_export")
Call FixSql("qselsccbactualtot", "actual_res_export")
Me.txtStatus = "Getting ITM Data"
Me.Repaint

'Set up the necessary objcts
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo Build_XL_Report_ERR
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Add

Me.txtStatus = "Building Workbook"
Me.Repaint

 

[Din FL]

Getting closer.

Now how do I open the EXCEL spreadsheet I just put out with an outputto
statement?

 

[Klatuu]

This will open the workbok. varGetFileName is a variable I use to pass the
full path and filename of the workbook.

Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)

This will select the worksheet in the book.

Set xlSheet = xlBook.Worksheets("Actuals_res_export")

 

[Din FL]

Almost there.

Have 2 issues with the EXCEL code.

I only want border on the bottom, but it doesn't like

With xlApp.Selection.Borders(xlEdgeBottom)

and how do I move to last row. in EXCEL I would use the xldown, but

xlSheet.Selection.End(xlDown).Select doesn't work.

Appreciate your help

 

[Klatuu]

Don't know if I can answer either of those questions. I can't find examples
where I did exactly that.

Sorry so long getting back. My router died yesterday and I just got it back
up

 

[Din FL]

That's okay. I have fought this all day. I was trying to do that because I
didn't know how many columns and rows I had. The number of columns has been
completely defined and I can figure out the number of rows. A different
engineer posted a very short example showing how to do that. I saw it
yesterday but can't find it now for anything.

I have 2 of what are probably easy questions:
1.
I want the EXCEL workbook to show so I can work through all this formatting.
It is completely hidden. If I don't have EXCEL open when I do the following:

Set xlApp = GetObject(, "Excel.Application")

it gives me a run-time error '429' ActiveX component can't create object.

I may not have my references in the correct order?????

So I open EXCEL, but can't see my spreadsheet
Rest of my open routine


Set xlApp = GetObject(, "Excel.Application")


If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo Build_XL_Report_ERR
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True

xnm = "H:\Capital.xls"
rptnm = "Med D PMO Operating & Capital Expense Report"

Set xlBook = xlApp.Workbooks.Open(xnm, 0, True)

Set xlSheet = xlBook.Worksheets(1)


2. How do I close with save without the user having to be involved?

Thanks. I want to finish this today and those 2 issues are stopping me.

 

[Ken Snell [MVP]]

Replace the intrinsic EXCEL constants with their actual values:

?xlDown
-4121

?xlEdgeBottom
9

 

[Din FL]

Thanks. I had two more questions if you could answer them, I would
appreciate it. Trying to finish this project this evening

 

[Klatuu]

The code I originally posted should keep you from getting the error. Here is
that part:

'This line turns off error trapping
On Error Resume Next ' Defer error trapping.

'Tries to establish a reference to an existing instance of Excel already
running
'If there is not a running instance, it will error out

Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then 'Excel was not running, create an instance
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If

'Sets the error number back to 0
Err.Clear ' Clear Err object in case error occurred.

Here is the code for a standard module than handles part of this.
Just paste the code into a new standard module.

 

[Din FL]

I couldn't get the detectExcel to compile. AND I missed the on error
resume Next.

Only other issue, is saving on close.

Really have appreciated your help.

 

[Ken Snell [MVP]]

To close a workbook and save the file (file has already been created/saved
with a filename):
xlBook Close True


To close a workbook and not save the file:
xlBook Close False


To turn off the "do you want to save?" prompt to the user:
xlApp.DisplayAlerts = False
xlBook Close True
xlApp.DisplayAlerts = True

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Din FL]

THANK YOU KEN and DAVE!!

I finished my project and can again format EXCEL from ACCESS.