Small error in code?

[jonman03]

Hey all,

I have the following code that opens an excel file, adds a row and value to cell D2, saves and closes the book, and runs a macro in access to import data (I know it works up to this point).

However, I'm trying to add some code to reopen the same book, and delete the entire row 2. Here's the code I have..

Private Sub Command5_Click()

Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

MySheetPath = "G:\Tech Ops\GPC\Product Costing Database\importtest.xls"

Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

Xl.Visible = True
XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets(1)

If XlSheet.Range("D2") = "ABC" Then
Xl.ActiveWorkbook.Save
Xl.ActiveWorkbook.Close
Xl.Quit

Else

XlSheet.Rows(2).EntireRow.Insert
XlSheet.Range("D2") = "ABC"

Xl.ActiveWorkbook.Save
Xl.ActiveWorkbook.Close
Xl.Quit

End If

DoCmd.RunMacro ("mac_import")


MySheetPath = "G:\Tech Ops\GPC\Product Costing Database\importtest.xls"

Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

Xl.Visible = True
XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets(1)

[COLOR=Red]XlSheet.Rows(2).EntireRow.Delete[/COLOR]

Xl.ActiveWorkbook.Save
Xl.ActiveWorkbook.Close
Xl.Quit

Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing




MsgBox ("Data Import Complete")

End Sub

I'm getting a debug error (in red above) on "XlSheet.Rows(2).EntireRow.Delete" that says "Automation Error The remote procedure call failed".

The excel app is opening up, but I don't see my spreadsheet, so it's stopping there.

What could be the issue?

Thank you very much!

 

[ajetrumpet]

there's a lot of duplication in your code. this may work, or it may not, but try it:

Private Sub Command5_Click()

Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

MySheetPath = "G:\Tech Ops\GPC\Product Costing Database\importtest.xls"

Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

Xl.Visible = True
XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets(1)

If XlSheet.Range("D2") = "ABC" Then
Xl.ActiveWorkbook.Save
Xl.ActiveWorkbook.Close
Xl.Quit

Else

XlSheet.Rows(2).EntireRow.Insert
XlSheet.Range("D2") = "ABC"

Xl.ActiveWorkbook.Save
Xl.ActiveWorkbook.Close
Xl.Quit

End If

set xl = nothing
set xlsheet = nothing
set xlbook = nothing

DoCmd.RunMacro ("mac_import")

Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

Xl.Visible = True
XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets(1)

[COLOR="Navy"][B]XlSheet.Rows("2:2").Delete[/B][/COLOR]

Xl.ActiveWorkbook.Save
Xl.ActiveWorkbook.Close
Xl.Quit

Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing


MsgBox ("Data Import Complete")

End Sub

i changed the delete code. I think it needs two arguments. if you use the intellisense in an excel module, you can see that deleting rows required a row index and a column index. so i assume you are missing that spec