Using Access VBA to edit Excel document

J

jonman03

Board Regular
Joined
May 26, 2009
Messages
69
Hey all,

So I have an Excel macro that has the following:
Code: 
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Hi,

This should do the trick.

Code: 
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
 
Upvote 0
Stu,

Thanks for the quick reply. I have entered the following:
Code: 
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!
 
Upvote 0
Hi, Take a look in the VBA references, Tools - References in vba window.

You probably need to add the,
Microsoft Excel object Library
 
Upvote 0
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.
 
Upvote 0
Hi

Add below code after; XlSheet.Range("D2") = "ABC"
Code: 
Xl.ActiveWorkbook.SaveAs FileName:="C:Temp\testSave.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Xl.ActiveWorkbook.Close
 
Upvote 0
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
 
Upvote 0
Thanks again Stu.

I just used:
Code: 
Xl.ActiveWorkbook.Save
Xl.ActiveWorkbook.Close
Xl.Quit

Thank you for your help. Pretty specific request but appreciate the help.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Linked Excel table in Access - field values change as I navigate?
Replies
5
Views
590
welshgasman
welshgasman
S
  • Solved
Export Access Table data to Excel - DoCmd.TransferSpreadsheet function
Replies
3
Views
1K
alansidman
A
M
  • Solved
Access and Excel Reports
Replies
1
Views
1K
Micron
M
A
  • Question Question
Efficiently Importing Large Records to MS - Access
Replies
8
Views
1K
jackd
J
S
  • Question Question
ACCESS, VBA: Fetch data from sql server and paste it into local table.
Replies
3
Views
2K
Micron
M

Forum statistics

Threads
1,224,586
Messages
6,179,723
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top