Using Access VBA to edit Excel document

[jonman03]

Hey all,

So I have an Excel macro that has the following:

Sub Test()
Rows(2).EntireRow.Insert
[D2] = "ABC"
End Sub

which adds a new row (at row 2) and inserts "ABC" in cell D2.
However, I need some way to embed this code behind an Access button in VBA. I understand that you can call an Excel Macro from Access, but the Excel document is changing often and will not always have the macro attached.

So my question is: How can I open the Excel document, insert a Row at line 2 and type "ABC" in cell D2, and save and close the Excel document? ALL from Access VBA, It is not possible to have any macros saved in Excel.

My .xls file is named "importtest.xls" and the sheet where I want to do this editing is "Sheet1"

Thank you very much! Let me know if I was unclear, seems like a long question.

 

[mark1080p]

I am doing something similar.

but get "Object required"?

Can anyone help

 

[mark1080p]

I am doing something similar.

but get "Object required"?

Can anyone help

Ignore my query.

I have rewritten code and now works fine

 

[Hussainnasif]

Private Sub ExcelTest_Click()
Dim MySheetPath As String
Dim Xl As Object
Dim XlBook As Object
Dim XlSheet As Object


MySheetPath = (CurrentProject.path & "\hell.xlsx")


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


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


Set XlSheet = XlBook.Worksheets(1)


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


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


End Sub

Say thanks to me it will work

 

[szFAF]

Hi,

This should do the trick.

Private Sub ExcelTest_Click()
'Variables to refer to Excel and Objects
Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
 
' Tell it location of actual Excel file
MySheetPath = "C:\Temp\importtest.xls"
 
'Open Excel and the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
 
'Make sure excel is visible on the screen
Xl.Visible = True
XlBook.Windows(1).Visible = True
 
'Define the sheet in the Workbook as XlSheet
Set XlSheet = XlBook.Worksheets(1)
 
'Insert Row and the Value in the excel sheet starting at specified cell
XlSheet.Rows(2).EntireRow.Insert
XlSheet.Range("D2") = "ABC"
 
'Clean up and end with worksheet visible on the screen
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Sub

Stu99, I know that this is an old post, but you just helped me so very much and i wanted to say thanks!!!!! I was struggling with inserting a row via VBA starting at row 2 and without clobbering any existing data. It was so simple and well explained. THANK YOU.