CODEWITHSUNDEEP
vbaexcel
AnnieK
AnnieK

Reputation: 1

Insert "dummy" row in Excel via Access using VBA

I'm attempting to insert a dummy line into an excel file via access using VBA. I attached the following to an access button, and when I run it I get: "Compile error: Sub or Function not defined"

Sub Command10_Click()

Dim objActiveWkbk As Object Dim objActiveWksh As Object Dim objXL As Object Dim strWkbkName As String

strWkbkName = "C:\data\Payroll.csv"

Set objXL = CreateObject("excel.application") objXL.Application.ActiveWorkbook Set objActiveWksh = objActiveWkbk.worksheets("Payroll.csv")

Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "2500"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "1500"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("U2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("V2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("W2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("X2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AD2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AF2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AG2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AH2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AJ2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AK2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AL2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AM2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AN2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AO2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AP2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AQ2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AR2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AS2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AT2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AU2").Select
    ActiveCell.FormulaR1C1 = "BBB"
    Range("AV2").Select
    ActiveWorkbook.Save

objActiveWkbk.Close SaveChanges:=False   Set objActiveWkbk = Nothing   objXL.Application.Quit   Set objXL = Nothing End Sub




Private Sub Command4_Click() DoCmd.OpenQuery "qry_CARS Capps Compile"

End Sub

Private Sub Command8_Click() DoCmd.TransferText acImportDelim, , "CARS Capps B", "P:\EMI\Drop\CARS Capps.csv", True

End Sub

Public Sub Import_CARS_Capps_Click() Dim xlApp As Object Dim xlBook As Object

xlApp.Visible = True

Set wbExcel = xlApp.Workbooks.Add Set xlBook = xlApp.Workbooks.Open("P:\EMI\Drop\CARS Capps.csv")        'ie "C:\My Documents\Data To Import.xlsx" xlApp.DisplayAlerts = False Workbook("CARS Capps.csv").Activate
        Sheets("Sheet1").Activate                                        'Sheet1 should be the actual name of your work sheet
        Range("a2").Select
        ActiveCell.EntireRow.Insert                                        'Insert a row below the header
        Cells(2, 1).Value = "aaa" Cells(2, 2).Value = "aaa" Cells(2, 3).Value = "aaa" Cells(2, 4).Value = "aaa" Cells(2, 5).Value = "aaa" Cells(2, 6).Value = "aaa" Cells(2, 7).Value = "5555" Cells(2, 8).Value
= "5555" Cells(2, 9).Value = "aaa" Cells(2, 10).Value = "aaa" Cells(2, 11).Value = "aaa" Cells(2, 12).Value = "aaa" Cells(2, 13).Value = "aaa" Cells(2, 14).Value = "aaa" Cells(2, 15).Value = "aaa" Cells(2, 16).Value = "aaa" Cells(2, 17).Value = "aaa" Cells(2, 18).Value = "aaa" Cells(2, 19).Value = "aaa" Cells(2, 20).Value = "aaa" Cells(2, 21).Value = "aaa" Cells(2, 22).Value = "aaa" Cells(2, 23).Value = "aaa" Cells(2, 24).Value = "aaa" Cells(2, 25).Value = "aaa" Cells(2, 26).Value = "aaa" Cells(2, 27).Value = "aaa" Cells(2, 28).Value = "aaa" Cells(2, 29).Value = "aaa" Cells(2, 30).Value = "aaa" Cells(2, 31).Value = "aaa" Cells(2, 32).Value = "aaa" Cells(2, 33).Value = "aaa" Cells(2, 34).Value = "aaa" Cells(2, 35).Value = "aaa" Cells(2, 36).Value = "aaa" Cells(2, 37).Value = "aaa" Cells(2, 38).Value = "aaa" Cells(2, 39).Value = "aaa" Cells(2, 40).Value = "aaa" Cells(2, 41).Value = "aaa" Cells(2, 42).Value = "aaa" Cells(2, 43).Value = "aaa" Cells(2, 44).Value = "aaa" Cells(2, 45).Value = "aaa" Cells(2, 46).Value = "aaa" Cells(2, 47).Value = "aaa" Cells(2, 48).Value = "aaa" Cells(2, 49).Value = "aaa" Cells(2, 50).Value = "aaa" Cells(2, 51).Value = "aaa" Cells(2, 52).Value = "aaa" Cells(2, 53).Value = "aaa" Cells(2, 54).Value = "aaa" Cells(2, 55).Value = "aaa" Cells(2, 56).Value = "aaa" Cells(2, 57).Value = "aaa" Cells(2, 58).Value = "aaa" Cells(2, 59).Value = "aaa" Cells(2, 60).Value = "aaa" Cells(2, 61).Value = "aaa"

xlBook.Save Workbooks("Cars Capps.csv").ClosexlApxlApp.Quit xlApp.DisplayAlerts = True Set xlBook = Nothing Set xlApp = Nothing


End Sub

Upvotes: 0

Views: 740

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

There are a few immediate issues in the Sub Command10_Click() that I can see that will cause the code to fail. I will illustrate below.

Everything looks good with this:

Dim objActiveWkbk As Object
Dim objActiveWksh As Object
Dim objXL As Object
Dim strWkbkName As String

strWkbkName = "C:\data\Payroll.csv"

Set objXL = CreateObject("excel.application")

But this next line is wrong: Set objActiveWkbk = objXL.Application.ActiveWorkbook

You are asking VBA to set an object that does not exist. In the statement above you created an Excel Object, but you have not created an actual workbook yet attached to the Excel object.

That said, change the line to

Set objActiveWkbk = objXL.Workbooks.Open(strWkbName)

to create the object.

Then this line will work:

Set objActiveWksh = objActiveWkbk.Worksheets("Payroll.csv")

(Caveat here is that the Worksheet name actually ends in .csv. If not, edit it accordingly)

However, the rest of the code will break because you do not qualify the properties or methods to the Excel Object. Access does not understand Rows, Range, Selection etc. It's not part of the syntax of the Access VBA Object Model, so Access will not know what to do when it sees. It is part of the Excel Object Model, so you just have to tell Access VBA that by assigning parentage.

A With block is a fast and efficient way to do that.

With objActiveWksh
    .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Also notice how I refactored that statement to get rid of the Select stuff. You don't need it. It slows your code and makes it harder to maintain. Work directly with the object instead. (There are rare cases where Select is needed).

So, continuing on ... 

    .Range("A2:F2").Value = "BBB" 'set them all at once
    .Range("G2").Value = "2500"
    .Range("H2").Value = "1500"
    .Range("J2:AA2").Value = "BBB" 'set them all at once
End With

Then, honestly, I am confused about why you create a workbook and then close it without saving changes, but the rest looks good.

objActiveWkbk.Close SaveChanges:=False
Set objActiveWkbk = Nothing
objXL.Application.Quit
Set objXL = Nothing

Upvotes: 1

Related Questions