CODEWITHSUNDEEP
excelvbams-accessms-access-2013excel-2013
StarsFlyFree FromCozyNights
StarsFlyFree FromCozyNights

Reputation: 303

Format Excel From Access VBA

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

Upvotes: 0

Views: 914

Answers (1)

Bob Goblin
Bob Goblin

Reputation: 1269

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

Upvotes: 3

Related Questions