Format Excel from Access

  • Thread starter Thread starter Pastor Del
  • Start date Start date
P

Pastor Del

My customer wants me to transfer data from an existing Access 2000 database
to Excel and email it to them. They also want specific formatting of the
data. I am self taught in Access and have very little experience with Excel
at this time

I have successfully created the spreadsheet & transferred the data, but I am
having the following problems:
1. I need to format an entire column to 3 of 9 barcode
2. I also need to format a specific cell to 3 of 9 barcode
3. I need to format certain cells to bold
4. I use TransferSpreadsheet to transfer the data & it makes the first row
of the spreadsheet field names. How can I delete this row or avoid the field
names altogether

Can anyone help me with this?

I am self taught in Access and have very little experience with Excel at
this time
 
It is quite easy to perform operations in Excel, and control the entire
process from Access.

Make sure you set a reference to Excel, and then run this code in an Access
module:

Option Compare Database

Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub



Private objExcel As Excel.Application

Private xlWB As Excel.Workbook

Private xlWS As Excel.Worksheet



Sub Rep()

Dim strFile As String

strFile = "C:\MyExcelWorkbook.xls"

‘Of course, this is just an example; put the actual path to your actual file
here…

' Opens Excel and makes it Visible

Set objExcel = New Excel.Application

objExcel.Visible = True

' Opens up a Workbook

Set xlWB = objExcel.Workbooks.Open(strFile)

' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.

Set xlWS = xlWB.ActiveSheet

' Set xlWS = xlWB("Sheet1")

With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here…you can record a macro and make the process
super easy!!

End With

' Close and Cleanup

xlWB.SaveAs xlSaveFile

xlWB.Close

xlapp.Quit

Set xlapp = Nothing

End Sub
 
The basic transfer without column names is :
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tblYourTable",
"MyNew.xls", False

An array of formatting options are shown below

Regards

Kevin


Sub FormatXLSheet(FileID As String)
Dim xlApp As Object
Dim wbExcel As Object
Dim ws As Worksheet
Dim lngRow As Long
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Open(FileID)
lngRow = 1
On Error Resume Next
Set ws = wbExcel.Sheets(1)
With ws.Range("A1:AH1").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
ws.Range("A1:AH1").HorizontalAlignment = xlCenter
ws.Range("A1:AH1").Interior.ColorIndex = 15
With ws.Range("A1:AH1").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A1:AH1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A1:AH1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A1:AH1").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A1:AH1").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ws.Range("A2").Select
xlApp.Windows(1).FreezePanes = True
'Errors back in effect
On Error GoTo 0
ws.Columns("A:AH").Select
ws.Columns("A:AH").EntireColumn.AutoFit
ws.PageSetup.PrintTitleRows = "$1:$1"
ws.PageSetup.Orientation = xlLandscape
ws.PageSetup.CenterFooter = ""
ws.PageSetup.LeftFooter = "Printed &D"
ws.PageSetup.RightFooter = "Page &P of &N"
xlApp.Workbooks(1).Close savechanges:=True
xlApp.Workbooks.Close
xlApp.Quit
Set xlApp = Nothing
End Sub


Sub FormatXLSheetProvider(FileID As String)
Dim xlApp As Object
Dim wbExcel As Object
Dim ws As Worksheet
Dim lngRow As Long
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Open(FileID)
lngRow = 1
On Error Resume Next
Set ws = wbExcel.Sheets(1)
With ws.Range("A1:AH1").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
ws.Range("A1:AH1").HorizontalAlignment = xlCenter
ws.Range("A1:AH1").Interior.ColorIndex = 15
With ws.Range("A1:AH1").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A1:AH1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A1:AH1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A1:AH1").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A1:AH1").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ws.Range("A2").Select
xlApp.Windows(1).FreezePanes = True
'Errors back in effect
On Error GoTo 0
ws.Columns("A:AH").Select
ws.Columns("A:AH").EntireColumn.AutoFit
ws.PageSetup.PrintTitleRows = "$1:$1"
ws.PageSetup.Orientation = xlLandscape
ws.PageSetup.CenterFooter = ""
ws.PageSetup.LeftFooter = "Printed &D"
ws.PageSetup.RightFooter = "Page &P of &N"
xlApp.Workbooks(1).Close savechanges:=True
xlApp.Workbooks.Close
xlApp.Quit
Set xlApp = Nothing
End Sub
 
What library(s) do I need? Many of your variables, like xlEdgeTop, are not
recognized
 
Another question. I am still getting the column names. According to the
Help files the 'hasfieldnames' argument is ignored when exporting. Is there
another way to prevent these column names or delete a row?
 
To delete row 1 and move all other rows up use:

Rows("1:1").Select
Selection.Delete Shift:=xlUp
 
Back
Top