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

William,

Thanks for the additional advice, I appreciate it.

I am starting to have some fun with all of this.

I can see many opportunities as the small firm that I work for uses a
ton of Excel spreadsheets.

Brad



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
Benson (VBACreations.Com)
Sent: Friday, July 01, 2011 3:46 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

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

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.