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.

 

[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.

 

[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"

--
Dave Hargis, Microsoft Access MVP

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
====================================

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]

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.
====================================

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"

--
Dave Hargis, Microsoft Access MVP

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
====================================

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.
--
Dave Hargis, Microsoft Access MVP


:

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.
===============================
:

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


:

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 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.
--
Dave Hargis, Microsoft Access MVP

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.
====================================

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"

--
Dave Hargis, Microsoft Access MVP

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
====================================
:

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.
--
Dave Hargis, Microsoft Access MVP


:

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.
===============================
:

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


:

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…

 

[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.
=====================

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.
--
Dave Hargis, Microsoft Access MVP

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.
====================================

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"

--
Dave Hargis, Microsoft Access MVP


:

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
====================================
:

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.
--
Dave Hargis, Microsoft Access MVP


:

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.
===============================
:

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


:

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]

"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.
--
Dave Hargis, Microsoft Access MVP

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.
=====================

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.
--
Dave Hargis, Microsoft Access MVP

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.
====================================
:

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"

--
Dave Hargis, Microsoft Access MVP


:

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
====================================
:

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.
--
Dave Hargis, Microsoft Access MVP


:

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.
===============================
:

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


:

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…

 

[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.
============================================
"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.
--
Dave Hargis, Microsoft Access MVP

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.
=====================

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.
--
Dave Hargis, Microsoft Access MVP

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.
====================================
:

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"

--
Dave Hargis, Microsoft Access MVP


:

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
====================================
:

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.
--
Dave Hargis, Microsoft Access MVP


:

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.
===============================
:

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


:

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.

 

[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

 

[Larry Linson]

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 (e-mail address removed).

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