Format Excel From Access VBA

Question

I am in need to add a new row above Cell 1 in my workbook. I typed out the below syntax which I believe is correct, but I am getting an error of:

Object does not support this property or method

When the syntax hits the second line of my with block - .Rows("1:1").Select

What do I need to alter in order for this syntax to execute as expeceted?

Function AdddFormatting()
  Dim ExportRecordSet As DAO.Recordset
  Dim excelWS As Object, excelWS2 As Object
  Dim xl As Object
  Dim wb As Object
  Dim TemplateWB As String

  Set xl = CreateObject("Excel.Application")
  TemplateWB = "C:\Test\Testwb.xlsx"
  Set wb = xl.Workbooks.Add
  Set excelWS = wb.Worksheets(1)
  excelWS.Name = "AddedFromCode"
  Set excelWS2 = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
  excelWS2.Name = "AddedFromCode2"
  xl.Application.Visible = True

  With wb
    .Sheets(1).Activate
    .Rows("1:1").Select
    'Using this syntax throws the same error
    '.Rows(1).Select
    .Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("A1:H1").Select
    .Selection.Merge
    .ActiveCell.FormulaR1C1 = "This is the text that will display as the header"
    .Range("A1:H1").Select.Font.Name = "Arial"
    .Range("A1:H1").Select.Font.Size = 15
    .Range("A1").Activate
  End With
End Function

EDIT
Per the comment posted by @user2676140 I altered my with block from with wb to with excelWS which now throws the same error on line 3 - this one:

.Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

I think the syntax on that line is wrong... .Rows(1).Select is the way to select a full row — Martin Router King
– Martin Router King, Commented Jan 2, 2017 at 11:14
Visit this link for more information: excel-easy.com/vba/examples/entire-rows-columns.html — Martin Router King
– Martin Router King, Commented Jan 2, 2017 at 11:14
If I alter the syntax to be .Rows(1).Select I get the same error message. — StarsFlyFree FromCozyNights
– StarsFlyFree FromCozyNights, Commented Jan 2, 2017 at 11:16
I suggest referencing the Excel DLL (unmanaged so use the ActiveX not the PIA). With early binding you will get intellisense and be able to diagnose the problem much easier than with late binding — Jeremy Thompson
– Jeremy Thompson, Commented Jan 2, 2017 at 11:27
The VBA in Excel and the VBA in Access is exactly the same. The only difference is which global objects are available by default in Excel (e.g. Excel.Application) and which global objects are available by default in Access (e.g. Access.Application). You can reference Excel objects either using late binding (as you are doing, with the CreateObject function) or using early binding, by adding a reference (Tools -> References...) to the Microsoft Excel object library, as @JeremyThompson notes. The With statement does something completely different -- it lets you call members on a ... — user20416
– user20416, Commented Jan 2, 2017 at 12:13

Bob Goblin · Accepted Answer · 2017-01-02 13:25:34Z

3

This syntax can def use some cleaning up but it should get you close to your desired output. Post a comment with any issues that this still brings you and I will try to walk you through a fix.

excelWS.Activate
excelWS.Rows(1).Select
xl.Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
excelWS.Range("A1:H1").Activate
xl.Selection.Merge
xl.ActiveCell.FormulaR1C1 = "This is the text that will display as the header"
excelWS.Range("A1:H1").Activate
xl.Selection.Font.Name = "Arial"
xl.Selection.Font.Size = 15
excelWS.Range("A1").Activate

->@StarsFlyFree FromCozyNights<- to only merge A1:H1 try changing this line:

excelWS.Range("A1:H1").Activate

to this ---->

excelWS.Range("A1:H1").Select

edited Jan 2, 2017 at 13:25

answered Jan 2, 2017 at 12:06

Bob Goblin

1,2693 gold badges19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Doc Brown Over a year ago

I took the freedom and removed the "With" statements completely, they only added "noise" to the code.

StarsFlyFree FromCozyNights Over a year ago

So close to what I need. This merges all of Row 1 not just A1:H1 - can you fix?

Collectives™ on Stack Overflow

Format Excel From Access VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related