Automate Excel

  • Thread starter Thread starter DungeonAccess
  • Start date Start date
D

DungeonAccess

Howdy,
I'm trying to set up some type of reporting for this Access 2000 application
that I am doing. I want to use Excel and populate certain cells with data
from my Forms in Access. What do you think is the best way to do this? I
have a button that you click on my Form in Access, and it automatically
opens up a certain Excel spreadsheet. The spreadsheet I have is sort of like
a template that looks pretty, just needs data now. Should, or could, I add
code to this button to populate the cells in the spreadsheet? If so, I'm not
sure what code to put there. I also tried doing some code in the spreadsheet
itself using the vba editor, but again, I'm kinda clueless right now as to
what to do. Any suggestions?

Thanks in advanced!!!

Thanks DA
 
The easiest thing to do is to use the Transferspreadsheet method which you can
lookup in the Help file. The transferspreadsheet method however exports Access
data into a contiguous range in the Excel worksheet. That's all well and good if
you can use the data in a contiguous range in your worksheet. If you need the
data in various cells then export the data into a contiguous range in an
out-of-the-way location in your workbook and use Excel formulas to pull the data
into the specific cells where you need it.
 
First, create a reference to Excel. IN any module, Tools -
References.
Click to expand...


Your code would look something like:

Sub exportexcel()
Dim xls As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Set xls = New Excel.Application
xls.Visible = True
'If you want to open an existing workbook
'Set wkb = xls.Workbooks.Open("C:\File.xls")
'Create a new workbook
Set wkb = xls.Workbooks.Add
Set wks = wkb.Worksheets(0)
'To sett a value
wks.Cells(1, 1) = Date

wkb.Save
xls.Quit
Set wks = Nothing
Set wkb = Nothing
Set xls = Nothing
End Sub

Is there anyting else you need specifically?


Chris
 
Man, I appreciate the fast responses. You guys rock!!

I'm so close to getting this to work. Now, when I click the button, it acts
like it opens two Excel applications....the first 1 opens fine it seems,
although my data does not populate the cell yet, but if I close the first
one and go back to my Access application, there is an error box waiting for
me that says "subscript out of range". I click ok and it opens the same
Excel file, and the data is still not getting put into the cell. I feel like
I am pretty close, just not quite there!!! UGH! Any suggestions what I am
doing wrong. I almost had your code word for word, I was just missing the
wks.Cells(1,1) = whatever. Do you want me to paste my code?

Thanks again Chris, you rock!
DA

On second thought, I just double checked my code and changed something real
quick....now it just opens my Excel file once, but right away it tells me
subscript out or range. I'll paste my code so you can see what a mess I am
making!!

Private Sub cmdCreatePurchaseOrder_Click()
On Error GoTo Err_cmdCreatePurchaseOrder_Click
'start confusion here :)

Dim Response As VbMsgBoxResult


Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Set oExcel = New Excel.Application


Set oBook = oExcel.Workbooks.Open("AnExcelfile.xls") ' shortened for
forum purpose only

Dim oSheet As Excel.Worksheet
Set oSheet = oBook.Worksheets(0)



Response = msgbox("Make sure that your print this Invoice so that you
can fill in the necessary information on the Purchase Order Form. Select OK
to Print the Invoice now!", vbOKCancel, "Please do not forget to print an
Invoice!") ' original idea was just to have the user input it manually, but
I want to automate it.

If Response = vbOK Then
Call PrintInvoice_Click

Call msgbox("Please do not forget to save this file <Save As>!!!
This is a template only and should not be changed!!!", vbCritical, "Please
save your PO /File/Save As....!")

ElseIf Response = vbCancel Then
Call msgbox("Well you made it to cancel. Now what?", vbQuestion,
"Now what?")
Call msgbox("Please do not forget to save this file <Save As>!!!
This is a template only and should not be changed!!!", vbCritical, "Please
save your PO /File/Save As....!")

On Error Resume Next

oExcel.Visible = True
oSheet.Cells(10, 2) = Me.OrderID.Text
' if I can get just one of the cells to populate with what I want,
the rest should be a piece of cake

'also for the purpose of this task, I am always hitting cancel so I
do not print a million things


End If


oExcel.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing


Exit_cmdCreatePurchaseOrder_Click:
Exit Sub

Err_cmdCreatePurchaseOrder_Click:
msgbox Err.Description
Resume Exit_cmdCreatePurchaseOrder_Click

End Sub

Thanks again,

DA
 
Oh, and I appear to be crashing Excel a lot. And still getting subscript out
of range.

DA
 
Here's another example:

Dim wb As Object
Set wb = GetObject("c:\MyWorkbook.xls")
wb.ActiveSheet.Cells(1, 1) = Date
wb.Close -1
Set wb = Nothing

Range("A1") in the activesheet in yr Excel workbook
will get updated.

Krgrds,
Perry
 
What line does it give you the Subscript Out of Range?
I'm guessing on the Set oSheet = oBook.Worksheets(0),
right?

That would probably be my vault. It seems the Worksheets
Collection is 1 based, as opposed to the normal 0 based.
Worksheets(1) references the first worksheet. Sorry about
that.

BTW, you can record macros and use that code to help you
write your code to format the cells. It takes some
translating, but works.


Chris

-----Original Message-----
Man, I appreciate the fast responses. You guys rock!!

I'm so close to getting this to work. Now, when I click the button, it acts
like it opens two Excel applications....the first 1 opens fine it seems,
although my data does not populate the cell yet, but if I close the first
one and go back to my Access application, there is an error box waiting for
me that says "subscript out of range". I click ok and it opens the same
Excel file, and the data is still not getting put into the cell. I feel like
I am pretty close, just not quite there!!! UGH! Any suggestions what I am
doing wrong. I almost had your code word for word, I was just missing the
wks.Cells(1,1) = whatever. Do you want me to paste my code?

Thanks again Chris, you rock!
DA

On second thought, I just double checked my code and changed something real
quick....now it just opens my Excel file once, but right away it tells me
subscript out or range. I'll paste my code so you can see what a mess I am
making!!

Private Sub cmdCreatePurchaseOrder_Click()
On Error GoTo Err_cmdCreatePurchaseOrder_Click
'start confusion here :)

Dim Response As VbMsgBoxResult


Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Set oExcel = New Excel.Application


Set oBook = oExcel.Workbooks.Open
Click to expand...
("AnExcelfile.xls") ' shortened for
forum purpose only

Dim oSheet As Excel.Worksheet
Set oSheet = oBook.Worksheets(0)



Response = msgbox("Make sure that your print this Invoice so that you
can fill in the necessary information on the Purchase Order Form. Select OK
to Print the Invoice now!", vbOKCancel, "Please do not forget to print an
Invoice!") ' original idea was just to have the user input it manually, but
I want to automate it.

If Response = vbOK Then
Call PrintInvoice_Click

Call msgbox("Please do not forget to save this
Click to expand...
 
Thanks again guys, I appreciate the help. I was off yesterday and will try
these ideas today and let you know how they work.

Thanks again for everything!!
DA
 
Hey Chris, you hit it right on the nose!!! Thanks man. It updates the
desired cell so the rest of the cells will be easy for me to do now!! The
only problem I am still having is that it is opening Excel twice!!!! The
first time it opens, it has the correct data in the desired cell. However,
the second one that opens does not!! What I did originally was make a button
in Access and did the whole wizard thing where I told it to open an Excel
application, and then I made the hyperlink to the file that I wanted. I
think what I am going to do now is just start over with a blank cmd and put
the code I have behind it. I get this feeling that there is some underlying
code in Access that is causing it to open the Excel file, besides the code
that I am giving it. Do you have any suggestions?

Thanks,
DA
 
Ok, I called my function from a new button that I created and everything
worked great!! Man, you guys are awesome!! It updates my cell and only opens
once!

I hope I can return the favor someday!!
DA
 
Back
Top