VBA - Can this be done ?

[MVP - WannaB]

I have an Access database with a link to an excel file. The excel file is
used to pull data down from the internet and then an append query copies that
data into appropriate Access tables. Opening the Excel file, providing an
index number, and running the internet update is currently a manual process.
What code would I need so that from an Access form with the index number I
could click a button that would open the spreadsheet provide the index number
from the form that I was looking at to a specific cell on the spreadsheet,
then have the spreadsheet rn the update and close and return back to Access???
This seems so far beyond my abilities I would really appreciate your help
Thanks in advance…

[Klatuu]

I lost you on this line:

then have the spreadsheet rn the update and

Does rn mean run?
If you are wanting to update a value in the spreadsheet from within Access,
it will not work if the spreadsheet is linked table. That functionality was
removed almost 3 years ago because of a lawsuit Microsoft lost.

You can open the spreadsheet from within Access using the Shell command and
enter the index directly into the spreadsheet or you can use Automation to
open the spreadsheet and insert the index in a specific cell. You can even
keep the spreadsheet hidden if you want to using the second option.
--
Dave Hargis, Microsoft Access MVP

[Mark]

I provide custom solutions to problems like yours. My fees are very
reasonable. You would need tpo send me a copy of your Access database and
Excel file. If you are interested, contact me at san...@penn.cim.

Steve

"MVP - WannaB" <MVPW...@discussions.microsoft.com> wrote in message
news:52AAEDD2-936A-4297...@microsoft.com...

> Thanks in advance.
>

[Klatuu]

Steve,

I suggest you visit this site:
http://www.mvps.org/access/netiquette.htm

And read the paragraph regarding advertizing.
Your post is inappropriate.

--
Dave Hargis, Microsoft Access MVP

[MVP - WannaB]

Thanks Dave, Yes "rn" was a typo, should have been RUN.
The cell in the Excel file I wish to update is on a seperate TAB from the
TAB that is linked to the DB, and Yes It would be best to have the Excel file
open, remain hidden, have the value from the index field inserted in the
specific Cell on TAB A, then run it's internet update, save the linked TAB
"Export" and close, (not Excel, just the open workbook)..
I have written the macro that works from within Excel, and it runs the
Update, then saves the "Export" TAB, overwritting the existing file.
I have a little experience with the Shell command, but how I would use it to
do more then open the Excel file, or how I would use ACCESS VBA to do things
in another file, That baffles me.
I would appreciate any guidance.

As for Steve's response, I am an "MVP wannaB", and I wont get there by
having anyone else do what I can do myself "with a little assistance"
(alright maybe more then a little). I don't imagine anyone here is looking
for someone to do the work for them.
===============================

[Klatuu]

Okay, Automation with Excel is a little tricky, but doable. The main thing
you have to focus on in this situation is being very careful you fully
qualifiy all your Excel objects. If you reference an Excel object and Access
can't determine which Application object it belongs to, what can happen is
Access will create another instance of Excel.exe running in memory, so when
you Quit the Excel application object you created, it can leave the one
Access created running in memory. This can create some strange behaviour.
Most noticably if you try to open an Excel file, it will hang, because the
errant copy is running. You will not see it in the Applications tab of Task
Manager, but in the Processes tab, so watch out for that.

Now here are some basics when it comes to using Automation. First, there
are two ways to do it. One is called Early Binding and the other Late
Binding. The difference is how you Dim the application object. When you dim
it as Excel.Applcation, it is early binding. If you Dim is as Object, it is
late binding. Each has advantages and disadvantages. The advantage of early
binding is that during development, you get Intellisense and there is a
slight advantage in load time. The disadvantages are you must have a
reference to Excel in your VBA references, If the end user doesn't have the
same version of Excel installed that you developed in, it probably will not
work on their computer because it is looking the the specic version, and you
don't get Intellisense. I perfer Late Binding to avoid version errors or non
standard installs (in other than default directories). Here are some sample
ideas to get you started:

Private xlApp As Object 'Application Object
Private xlBook As Object 'Workbook Object
Private xlSheet As Object 'Worksheet Object

This code will use an existing instance of Excel if it is already running;
otherwise it will start an instance. Note the reference to the variable
blnExcelWasNotRunning.
You use that to determine whether to quit Excel. It would irritate a user
if they had a worksheet open and you closed it down for them <g>

'Set up the necessary Excel Objcts
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
End If

Err.Clear ' Clear Err object in case error occurred.
'Set error trapping back on
On Error GoTo Build_XL_Report_Error
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False

Now here is how you get out gracefully:

xlBook.Save
xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Now, to set the value of a specific cell:
xlSheet.Cells(27, 3).Value = "F"

And if you need to select a specific Sheet:
Set xlSheet = xlBook.Worksheets("SheetName")

Hopes this give you a starting place.

[MVP - WannaB]

WOW!!! This is great. I am always working multiple project and not sure
when I will get back to this one, hopfully next week. I see some questions I
will have, but will try working with this a bit before I post me questions,
and when I do I will open a new post "VBA - Access code to Excel"
One quick question though, I previously tried to capture the value of a
field on a form and pass it out from access and was unable to accomplish it.
In your line here

Now, to set the value of a specific cell:
xlSheet.Cells(27, 3).Value = "F"

How would I set "F" to that value ?
Wild guess here>> xlSheet.Cells(27, 3).Value =
xlMsAccess.[formname].field.value
Thanks a TON!! Dave. I really appreciate the help
====================================

[Klatuu]

xlSheet.Cells(27, 3).Value = "F"

was a hard coded example from an app I did a couple of years ago. If you
wanted to use a control value it would be
xlSheet.Cells(27, 3).Value = Me.MyControlName

How about as a subject line when you have questions on this, you use this
subject line, so I will recognize it easily. You don't seriously expect me
to remember something for a whole week, do you? :)

"Ping Klatuu - Excel Automation"

[MVP - WannaB]

Same Subject line, but new post, OK. I thought that might be poor Etiquette,
but OK.

Me.MyControlName surprised me at first, but after seeing it written out it
makes sense. After all I am originating the call from the control source.
"I think I phrased that correctly"
Thank you.
====================================

[Klatuu]

I am originating the call from the control source.

I don't understand the above statement. Can you explain a bit more and post
the code in the control source and any code it calls?

I've never seen this done before.

[MVP - WannaB]

Sorry, "I am originating the call from the control source" is just my way of
stateing what I think I understand. Let me try again in more words >> (and
again this is just my words, I am probably way off base and very wrong in
many ways

When Code like this >>"xlSheet.Cells(27, 3).Value = Me.MyControlName" is
executed from a form "Me." is the control source. When the code is run to
(copy or export) a value from a field on the active form (the control
source), Me. should be acceptable to capture the value, but how it the value
held (what variable will be recognized) when it come time to paste that value
into the destination???
I admit I have a very wierd way of looking at things, and my understanding,
is sometimes ODD.
Thanks.
=====================

[Klatuu]

"xlSheet.Cells(27, 3).Value = Me.MyControlName"

Me. is not the control source. It is a reference to the form the code is
in. It is short for:
Forms!MyFormName

MyControlName is the name of a control on the form. MyControlName is only
an example. You would use the actual name of the control. The cell
referenced in the code would be populated with the value of the control when
the code executes.

There is no import or copy involved. What you are actually doing is
programmatically opening the Excel spreadsheet, puting a value in a cell, and
closing the spreadsheet. It is a substitution for doing the exact same thing
a user would do manually.

BTW, ControlSource is a property of a form or report control. It specifies
what will be displayed in the control. You can specify a calculation, a
specific value, or a field in the form's recordset, or nothing at all.

[MVP - WannaB]

While I understood Me. to be a reference to the form that the code is in, My
mistake is in thinking of it as the control source.

The computer can not enter anything that it does not hold in a variable or
memory, so to enter a value in a cell the computer needs to hold that value
somewhere that can be referenced by something other then the application where
the value was stored. So that is how I come up with the idea that the value is
either copied or imported into the Cell of the Excel file.

Thank you for clearing up my confusion on the ControlSource subject.
============================================
"Klatuu" <Kla...@discussions.microsoft.com> wrote in message
news:186F76AA-51FF-419B...@microsoft.com...
"xlSheet.Cells(27, 3).Value = Me.MyControlName"

"MVP - WannaB" wrote:

> > > > > > > > > Thanks in advance.
> > > > > > > > >

[John Marshall, MVP]

So how many times do you have to be told that these newsgroups are provided
by Microsoft for FREE peer to peer support?

John... Visio MVP

"Mark" <no...@email.com> wrote in message
news:0NGdnVWo0ua0DM_V...@earthlink.com...

[Larry Linson]

"Mark" <no...@email.com> wrote in message
news:0NGdnVWo0ua0DM_V...@earthlink.com...

>I provide custom solutions to problems like yours. My fees are very
>reasonable. You would need tpo send me a copy of your Access database and
>Excel file. If you are interested, contact me at san...@penn.cim.
>
> Steve

Warning to newsgroup participants:

Please be cautious of people who will not follow the rules of the newsgroups
that prohibit soliciting business here. These newsgroups are intended for
volunteers to provide free answers to questions. If a person is not
sufficiently ethical to abide by the rules and charter, then you should
wonder how ethical they are or would be, in other areas.

This "Steve," under several pseudonyms, has long tried to co-opt technical
newsgroups as his personal classifieds, but has demonstrated a
less-than-impressive knowledge of the products in which he proposes to
develop for a fee.

Larry Linson
Microsoft Office Access MVP