[AccessD] Pulling Data from Excel into Access with "Automation"

Nice going Brad,

I suggest 
	Set XLWorkbook = XLApp.Activeworkbook, rather than Workbooks(1) in
case there is a Personal.xls/m workbook opening with every instance of the
user's Excel.

But better still is Test the opening of the workbook, in case the path is
wrong or the network won't give it up

Set XLWorkbook  = XLApp.Workbooks.Open ("C:\Book1.xlsx")
If Not XLWorkbook is Nothing
    'Go on

End if

And now you get the fun of dealing with Value and Value2 as properties as
well as Text property. More fun for dates than anything else.

Consider too the GetObject (,"Excel.Application") ... which is written
respectably into a function like

Set XL = GetObject(, "Excel.Application")
If XL Is Nothing Then
  Set XL = CreateObject("Excel.Application")
End If

Last, you might need App.Activate from time to time to bring the application
you want into focus...
   AppActivate "Microsoft Access"
Or
   AppActivate "Microsoft Excel"

I am not sure if it really helps or not, but there is often an annoying
flicker when I automate, and I don't recall if XL>VISIBLE = TRUE forces
excel into the foreground of your windows, or just makes it appear should it
happen to have focus.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Friday, July 01, 2011 2:27 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Pulling Data from Excel into Access with "Automation"

Steve, William, Darryl,

Thanks for the help with my questions.  

The advice that you provided has helped me move forward.

Below is a stripped down snippet of the Access 2007 VBA code that I put
together based on the advice you provided.

I am going to post it here in case some other "newbie" is looking for the
very "basic basics" and I am posting it here to see if anyone sees a problem
with the direction that I am heading.

Thanks again,
Brad

PS.  I also ordered a book on this subject.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub Pull_Data_From_Excel_Into_Access()

Dim XLApp As Excel.Application

Dim XLWorkbook As Excel.Workbook

Dim XLSheet As Excel.Worksheet

Dim Str_Value_in_Cell As Variant

Set XLApp = CreateObject("Excel.Application")


Set XLSheet = XLWorkbook.Sheets(1)

Str_Value_in_Cell = XLSheet.Cells(1, 1).Value

MsgBox Str_Value_in_Cell

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com