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.

 

[stu999]

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

 

[jonman03]

Stu,

Thanks for the quick reply. I have entered the following:

Private Sub Command1_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)

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

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

End Sub

However, when I run it highlights line "Dim Xl As Excel.Application" and says "Compile Error: User-defined type not defined"

Any ideas?

Thank you!

 

[stu999]

Hi, Take a look in the VBA references, Tools - References in vba window.

You probably need to add the,
Microsoft Excel object Library

 

[jonman03]

Thanks Stu.

I added that reference and it seemed to work. It opened up my Excel file, added a row and inserted "ABC" in D2.

One more request though, Is there a way to save and close the .xls after it has done this?

Thanks again.

 

[stu999]

Hi

Add below code after; XlSheet.Range("D2") = "ABC"

Xl.ActiveWorkbook.SaveAs FileName:="C:Temp\testSave.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Xl.ActiveWorkbook.Close

 

[stu999]

Just a thought you may need to change the extention to: .xls and the file format to: FileFormat:=xlExcel8

As I am using office 2007, and was saving as a .xlsm

HTH

 

[jonman03]

Thanks again Stu.

I just used:

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

Thank you for your help. Pretty specific request but appreciate the help.

 

[jonman03]

What line would I need to add to sort Column D (descending) ?

Thanks!

 

[james_lankford]

open your excel workbook
from the menu choose Macro/ record new
now do whatever you want and then press the black square to stop recording
then open the visual basic editor in excel
look at the macro

its ugly, its horrible, its coded incredibly badly
so take that code, rework a litle and stick it in access

you'll usually have to change all the active sheet and select statements to go with your variables and you can put in
for each
next
statements which will make it much better but, it will give you the basic idea of what to do