Formatting Excel from Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to format an excel sheet from access,
Access won't accept some of Excel's VB though.
For example,
For Each cell In xlSheet.Range("A1,"S1")

So, I guess my only option is to open excel & run the VB code from excel?
I'm not sure how to do that tho.
Is there a way to call a VB sub in excel from access?

I would really appreciate Any Help =(
Thanks
 
It is possible. Show us your Access VBA code and we can advise where you are
going wrong. You need to use Excel Automation creating objects that represent
worksheets and cells before you try to do anything with them.

Steve
 
I've made it this far...
unfortunately I can't even get the
Set xlApp = CreateObject("excel.application")
statement to work =(


Public Sub export()

Dim blnStopXL As Boolean
Dim blnExcelWasNotRunning As Boolean
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim bytTemp As Byte
Const conWhite As Long = 16777215


On Error GoTo ErrorReport

DoCmd.Hourglass (True)

'''' Call FixSql ?????????????


On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear
On Error GoTo ErrorReport
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Add

' Remove Excess Worksheets
Do While xlBook.Worksheets.Count > 1
xlApp.Worksheets(xlApp.Worksheets.Count).Delete
Loop
Set xlSheet = xlBook.ActiveSheet

With xlSheet.Cells(bytTemp, 1)
'Format Row 1

' For Each cell In xlSheet.Range("A1,"S1")
For bytTemp = 1 To 30
With xlSheet.Cells(bytTemp, 1)
.Font.Size = 10
.Font.Name = "Arial"
.Font.Bold = True
.Font.Color = conWhite
.Interior.Color = 0
.HorizontalAlignment = xlHAlignCenter
.WrapText = True
End With
Next bytTemp
xlSheet.Rows(1).RowHeight = 25.5
End With

' Threw in the exit Sub so Excel wouldn't close

Exit Sub

XL_EXIT:
If blnStopXL = True Then
xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End If
DoCmd.Hourglass (False)

Exit Sub


ErrorReport:
MsgBox "An unexpexted error occurred." & vbCrLf & "Error #" & Err.Number
& ": " & Err.Description, vbCritical, "Unexpexted Error"
blnStopXL = True
GoTo XL_EXIT
End Sub
 
"DetectExcel" Pissed off Access as well =(

SteveM said:
It is possible. Show us your Access VBA code and we can advise where you are
going wrong. You need to use Excel Automation creating objects that represent
worksheets and cells before you try to do anything with them.

Steve
Click to expand...
 
I'll copy and paste the actual data over when I figure out (I have to paste
vs exporting because eventually it'd be nice to paste it onto a sheet with
pre-existing data on it).

For right now though, I just want to see if I Can format excel from Access.
 
With copious help from people here, I built a routine that solves this
program in a slightly different way. First, using a template copy of the
Excel file, I recorded the formatting I wanted to do as a macro. Then I
copied the exact Excel VBA into a text file. Thereafter, from Access, any
time I need to do the same formatting I open the Excel file, insert the
entire contents of the text file as an Excel macro, then from Access I simply
tell Excel to run the macro. Then I close the Excel file. Works like a
charm, and I find it far easier than trying to write all the individual
formatting steps in Access VBA that will be acceptable to Excel. Bad enough
trying to keep Excel VBA and Access VBA separate and straight in my mind,
without the added layer of "Access trying to speak Excel" VBA.

If this is of interest, let me know and I can post or E-mail my code to you.
 
Here's a sample:

Sub ExcelFormat()

'Set variables to format the download
Dim strFilePath As String
Dim strFileName As String
Dim objXLApp As Object
Dim objXLBook As Object
Dim objXLSheet1 As Object

'Set the objects to format
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open(strFilePath & "\" &
strFileName)
Set objXLSheet1 = objXLBook.Worksheets("sheet1")

'Format various worksheets in the workbook
objXLSheet1.Range("A1:L3").Font.Bold = True
objXLSheet1.Range("A:L").Columns.AutoFit
objXLSheet1.Range("B:D").EntireColumn.Hidden = True

'Clean-Up
objXLBook.Save
objXLBook.Close
objXLApp.Quit
Set objXLSheet1 = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

End Sub

For more commands, record a macro in excel first, then paste the VBA over
from the code window...I will warn you though, the VBA isn't identical, so
you'd need to do some "adjusting"
 
Back
Top