Use VBA to design Excel report

M

Michael

Hello -
I've got an Access application that uses VBA to programmatically create a
series of Excel reports. I'm having a little trouble formatting the cells
correctly. The way you would code it in Excel isn't always working in
Access. Does anyone know where I can find a good reference on the proper
format? I've figured out basic font changes, but I'm having trouble with
number formats and borders.
Thanks!
 
M

Michael

Sorry, I should also include sorting. Can't seem to get the wording correct
for that either. Thanks again!
 
R

ryguy7272

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
 
M

Michael

Thanks Ryguy. I've already gotten the part where I'm working with the
spreadsheet. In fact, I'm successfully creating about 100 different
workbooks using VBA in access. My problem is in getting the individual Excel
reports formatted the way I want.

See my code below - I have figured out column widths, text sizes, etc. But
I'm having trouble figuring out how to properly word it to change a cell's
number format(i.e. date/currency, etc), put borders around cells and do
different sorts. Every way I try to word it I get errors.

Sample code::

With someworksheet
.cells(1, 1) = "some title"
.Name = "somename"
.range(.columns(1), .columns(1).end(-4161)).ColumnWidth
= 25
.cells(1, 1).FontSize = 8
etc......


Thanks!!!
 
R

ryguy7272

I just recorded this macro in Excel:
' Your Excel code begins here…you can record a macro and make the process
super easy!!
ActiveCell.FormulaR1C1 = "1/29/2010"
Range("A1").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

Try that inside the code I posted before. Read the commented lines. That
SHOULD work for ya! It should put a date in cell A1, change the formatting,
and place a bold boarder around the cell. I'm trying to get up to speed with
Office 2007 now, and I am having some problems with references, so it
actually didn't work for me, but I used to use Access 2003 all the time and
that worked flawlessly for me for a very long time. Again, record your own
macro in the section where I indicated.
 
M

Michael

Maybe I am doing something wrong. These lines do not work for me in Access.
For example I can't have any lines that start with
Range
it has to be
..range

Also Access never recognizes the border types, like xlEdgeBottom or
xlEdgeLeft. It thinks these are variables. I have Excel macros I've created
that would use lines like the ones you suggested. I just can't figure out
how to word them inside Access. Also, I can't seem to find any good
references on how to use these commands from Access.

Since I do have several different types of reports, I'd like to control all
of this from Access and not have to maintain seperate code in Excel.

Thanks so much for the info :)

ryguy7272 said:
I just recorded this macro in Excel:
' Your Excel code begins here…you can record a macro and make the process
super easy!!
ActiveCell.FormulaR1C1 = "1/29/2010"
Range("A1").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

Try that inside the code I posted before. Read the commented lines. That
SHOULD work for ya! It should put a date in cell A1, change the formatting,
and place a bold boarder around the cell. I'm trying to get up to speed with
Office 2007 now, and I am having some problems with references, so it
actually didn't work for me, but I used to use Access 2003 all the time and
that worked flawlessly for me for a very long time. Again, record your own
macro in the section where I indicated.



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Michael said:
Thanks Ryguy. I've already gotten the part where I'm working with the
spreadsheet. In fact, I'm successfully creating about 100 different
workbooks using VBA in access. My problem is in getting the individual Excel
reports formatted the way I want.

See my code below - I have figured out column widths, text sizes, etc. But
I'm having trouble figuring out how to properly word it to change a cell's
number format(i.e. date/currency, etc), put borders around cells and do
different sorts. Every way I try to word it I get errors.

Sample code::

With someworksheet
.cells(1, 1) = "some title"
.Name = "somename"
.range(.columns(1), .columns(1).end(-4161)).ColumnWidth
= 25
.cells(1, 1).FontSize = 8
etc......


Thanks!!!
Click to expand...
Click to expand...
 
R

ryguy7272

You stated...'Since I do have several different types of reports, I'd like to
control all of this from Access and not have to maintain seperate code in
Excel.'

Well, that's the whole point!!

You should be able to do all of the stuff you normally do in Excel, while
inside Access. Of, course, you have to set a reference to Excel.

IN ACCESS...Hit Alt+F11 > Tools References > Microsoft Excel xx.0 Object
Library. Use the highest #Object Library that you have available. Do you
use 2003 or 2007?

IN EXCEL...record a macro to do what you want. Copy/paste the relevant code
into the area that I indicated in my initial post...
' Your Excel code begins here. . .you can record a macro and make the
process super easy!!

Give it a go and see how you get on.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Michael said:
Maybe I am doing something wrong. These lines do not work for me in Access.
For example I can't have any lines that start with
Range
it has to be
.range

Also Access never recognizes the border types, like xlEdgeBottom or
xlEdgeLeft. It thinks these are variables. I have Excel macros I've created
that would use lines like the ones you suggested. I just can't figure out
how to word them inside Access. Also, I can't seem to find any good
references on how to use these commands from Access.

Since I do have several different types of reports, I'd like to control all
of this from Access and not have to maintain seperate code in Excel.

Thanks so much for the info :)

ryguy7272 said:
I just recorded this macro in Excel:
' Your Excel code begins here…you can record a macro and make the process
super easy!!
ActiveCell.FormulaR1C1 = "1/29/2010"
Range("A1").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

Try that inside the code I posted before. Read the commented lines. That
SHOULD work for ya! It should put a date in cell A1, change the formatting,
and place a bold boarder around the cell. I'm trying to get up to speed with
Office 2007 now, and I am having some problems with references, so it
actually didn't work for me, but I used to use Access 2003 all the time and
that worked flawlessly for me for a very long time. Again, record your own
macro in the section where I indicated.



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Michael said:
Thanks Ryguy. I've already gotten the part where I'm working with the
spreadsheet. In fact, I'm successfully creating about 100 different
workbooks using VBA in access. My problem is in getting the individual Excel
reports formatted the way I want.

See my code below - I have figured out column widths, text sizes, etc. But
I'm having trouble figuring out how to properly word it to change a cell's
number format(i.e. date/currency, etc), put borders around cells and do
different sorts. Every way I try to word it I get errors.

Sample code::

With someworksheet
.cells(1, 1) = "some title"
.Name = "somename"
.range(.columns(1), .columns(1).end(-4161)).ColumnWidth
= 25
.cells(1, 1).FontSize = 8
etc......


Thanks!!!
:

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Sorry, I should also include sorting. Can't seem to get the wording correct
for that either. Thanks again!

:

Hello -
I've got an Access application that uses VBA to programmatically create a
series of Excel reports. I'm having a little trouble formatting the cells
correctly. The way you would code it in Excel isn't always working in
Access. Does anyone know where I can find a good reference on the proper
format? I've figured out basic font changes, but I'm having trouble with
number formats and borders.
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
M

Michael

I guess that's where I'm having problems....the code I would use inside an
Excel macro, doesn't work from inside Access. Especially borders -
everything I try it tells me it is not supported.

ryguy7272 said:
You stated...'Since I do have several different types of reports, I'd like to
control all of this from Access and not have to maintain seperate code in
Excel.'

Well, that's the whole point!!

You should be able to do all of the stuff you normally do in Excel, while
inside Access. Of, course, you have to set a reference to Excel.

IN ACCESS...Hit Alt+F11 > Tools References > Microsoft Excel xx.0 Object
Library. Use the highest #Object Library that you have available. Do you
use 2003 or 2007?

IN EXCEL...record a macro to do what you want. Copy/paste the relevant code
into the area that I indicated in my initial post...
' Your Excel code begins here. . .you can record a macro and make the
process super easy!!

Give it a go and see how you get on.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Michael said:
Maybe I am doing something wrong. These lines do not work for me in Access.
For example I can't have any lines that start with
Range
it has to be
.range

Also Access never recognizes the border types, like xlEdgeBottom or
xlEdgeLeft. It thinks these are variables. I have Excel macros I've created
that would use lines like the ones you suggested. I just can't figure out
how to word them inside Access. Also, I can't seem to find any good
references on how to use these commands from Access.

Since I do have several different types of reports, I'd like to control all
of this from Access and not have to maintain seperate code in Excel.

Thanks so much for the info :)

ryguy7272 said:
I just recorded this macro in Excel:
' Your Excel code begins here…you can record a macro and make the process
super easy!!
ActiveCell.FormulaR1C1 = "1/29/2010"
Range("A1").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

Try that inside the code I posted before. Read the commented lines. That
SHOULD work for ya! It should put a date in cell A1, change the formatting,
and place a bold boarder around the cell. I'm trying to get up to speed with
Office 2007 now, and I am having some problems with references, so it
actually didn't work for me, but I used to use Access 2003 all the time and
that worked flawlessly for me for a very long time. Again, record your own
macro in the section where I indicated.



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Thanks Ryguy. I've already gotten the part where I'm working with the
spreadsheet. In fact, I'm successfully creating about 100 different
workbooks using VBA in access. My problem is in getting the individual Excel
reports formatted the way I want.

See my code below - I have figured out column widths, text sizes, etc. But
I'm having trouble figuring out how to properly word it to change a cell's
number format(i.e. date/currency, etc), put borders around cells and do
different sorts. Every way I try to word it I get errors.

Sample code::

With someworksheet
.cells(1, 1) = "some title"
.Name = "somename"
.range(.columns(1), .columns(1).end(-4161)).ColumnWidth
= 25
.cells(1, 1).FontSize = 8
etc......


Thanks!!!
:

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Sorry, I should also include sorting. Can't seem to get the wording correct
for that either. Thanks again!

:

Hello -
I've got an Access application that uses VBA to programmatically create a
series of Excel reports. I'm having a little trouble formatting the cells
correctly. The way you would code it in Excel isn't always working in
Access. Does anyone know where I can find a good reference on the proper
format? I've figured out basic font changes, but I'm having trouble with
number formats and borders.
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

GeoffG

You don't have to select ranges programmatically to effect a
format to an area of cells. The Range object will do the
trick. "Selection" is used to refer to what's selected in
the user interface and "Select" is used to select a range of
cells in the user interface. Programmatically, you only
need point a Range object variable to a range of cells and
then apply the format you want to the Range object.

Below is an example.

Incidentally, I have no expertise in sorting.
It looks complicated in help.



' Excel objects:
Dim objXL As Excel.Application
Dim objWBK As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRNG1 As Excel.Range
Dim objRNG2 As Excel.Range

' Start Excel:
Set objXL = New Excel.Application
objXL.Visible = True

' Open the WorkBook:
Set objWBK = objXL.Workbooks.Open(strWorkBookPath)

' Point to Worksheet
Set objWS = objWBK.Worksheets(1)

' Point to a range:
Set objRNG1 = objWS.Range("A1", "Z1")

' Put borders around each cell:
For Each objRNG2 In objRNG1.Cells
objRNG2.BorderAround xlContinuous, _
xlMedium, xlColorIndexAutomatic, _
RGB(255, 255, 255)
Next

' Format the whole range:
With objRNG1

' Shade each cell:
With .Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

With .Font
.Size = 12
.Bold = True
.FontStyle = "Arial"
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With


' Format various columns in differnt
' number formats:

' General number format:
Set objRNG1 = objWS.Columns(2)
objRNG1.NumberFormat = "General"

' Currency format
' (positive in black, negative in red)
Set objRNG1 = objWS.Columns(3)
objRNG1.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

' Time format:
Set objRNG1 = objWS.Columns(4)
objRNG1.NumberFormat = "hh:mm:ss"


' Point to top left corner:
Set objRNG1 = objWS.Range("A1")

' Expand range to embrace all contiguous data:
Set objRNG1 = objRNG1.CurrentRegion

' Sort data:
' Put cursor on the words "Sort" and
' "SortSpecial", press F1, and read help
' topics to determine which parameters to
' use after the words Sort and SortSpecial.
objRNG1.Sort
' or:
objRNG1.SortSpecial



Geoff



message
news:[email protected]...
 
M

Michael

Thank you Geoff. I got some ideas from this post. Slowly but surely I'm
figuring this out. :)

GeoffG said:
You don't have to select ranges programmatically to effect a
format to an area of cells. The Range object will do the
trick. "Selection" is used to refer to what's selected in
the user interface and "Select" is used to select a range of
cells in the user interface. Programmatically, you only
need point a Range object variable to a range of cells and
then apply the format you want to the Range object.

Below is an example.

Incidentally, I have no expertise in sorting.
It looks complicated in help.



' Excel objects:
Dim objXL As Excel.Application
Dim objWBK As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRNG1 As Excel.Range
Dim objRNG2 As Excel.Range

' Start Excel:
Set objXL = New Excel.Application
objXL.Visible = True

' Open the WorkBook:
Set objWBK = objXL.Workbooks.Open(strWorkBookPath)

' Point to Worksheet
Set objWS = objWBK.Worksheets(1)

' Point to a range:
Set objRNG1 = objWS.Range("A1", "Z1")

' Put borders around each cell:
For Each objRNG2 In objRNG1.Cells
objRNG2.BorderAround xlContinuous, _
xlMedium, xlColorIndexAutomatic, _
RGB(255, 255, 255)
Next

' Format the whole range:
With objRNG1

' Shade each cell:
With .Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

With .Font
.Size = 12
.Bold = True
.FontStyle = "Arial"
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With


' Format various columns in differnt
' number formats:

' General number format:
Set objRNG1 = objWS.Columns(2)
objRNG1.NumberFormat = "General"

' Currency format
' (positive in black, negative in red)
Set objRNG1 = objWS.Columns(3)
objRNG1.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

' Time format:
Set objRNG1 = objWS.Columns(4)
objRNG1.NumberFormat = "hh:mm:ss"


' Point to top left corner:
Set objRNG1 = objWS.Range("A1")

' Expand range to embrace all contiguous data:
Set objRNG1 = objRNG1.CurrentRegion

' Sort data:
' Put cursor on the words "Sort" and
' "SortSpecial", press F1, and read help
' topics to determine which parameters to
' use after the words Sort and SortSpecial.
objRNG1.Sort
' or:
objRNG1.SortSpecial



Geoff



message
I guess that's where I'm having problems....the code I
would use inside an
Excel macro, doesn't work from inside Access. Especially
borders -
everything I try it tells me it is not supported.
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

format header cell in excel-TJ 2
Trouble Installing Excel VBA Code Cleaner into Office 365 0
VBA Code to Save Excel Sheet to XML 1
Dynamic Report Design 3
Access VBA Equivalent to Excel VBA 12
References on A2007 Form/report design? 2
TransferSpreadsheet question 2
Access automation: Running a macro from one Excel Spreadsheet to a 2

Top