Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

Mark cell in Excel bold using VBA Access

I have the following code and need to mark a specific cell in an excel sheet bold:

With objExcel
  .Visible = True
  .Workbooks.Add (xTemplate)
  .worksheets(1).Activate

  .worksheets(1).Cells(2, 1).Value = "TEST"
  .worksheets(1).Cells(2, 1).HorizontalAlignment = xlCenter
End With

unfortunately this generates an error and I have tried many different versions with no success. Thank you for any help!
0
mpim
Asked:
mpim
  • 3
  • 2
1 Solution
 
Helen FeddemaCommented:
First designate a range, then select it, then apply various types of formatting, such as this:
         sht.Range("A1:Q1").Select
         With appExcel.Selection
             .HorizontalAlignment = xlCenter
             .VerticalAlignment = xlCenter
             .MergeCells = True
         End With
         
         With appExcel.Selection.Font
             .Name = "Calibri"
             .Size = 16
         End With
         
         appExcel.Selection.Font.Bold = True

Open in new window


You can generally find the syntax you need by recording a macro.
0
 
Helen FeddemaCommented:
You might find my ebook, Working with Excel, useful.  It is available from Office Watch.  It covers exchanging data between Access and Excel in a variety of ways.
0
 
mpimAuthor Commented:
Hi Helen,
thank you and I have adjusted the code to the following but still get the following error "Unable to set HorizontalAlignment property of the range class"?? Thank you for any further help...

With objExcel
  .Visible = True
  .Workbooks.Add (xTemplate)
  .worksheets(1).Activate

  .worksheets(1).Cells(2, 1).Value = "TEST"
  .worksheets(1).Range("A2:A2").SELECT
  With objExcel.Selection
    .HorizontalAlignment = xlCenter
  End With
0
 
Helen FeddemaCommented:
Did you declare objExcel as Excel.Application?  That is needed to use named constants.  There should be a reference to the Excel object model in the References dialog.
0
 
mpimAuthor Commented:
Great! Thank you so much for your help..
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now