|
 |
access thread: automation with excel
Message #1 by "patrick slesicki" <patrick_slesicki@d...> on Thu, 20 Dec 2001 22:57:53
|
|
I want to transfer the contents of an Access 97 form to an Excel 97 sheet.
I'm having problems formatting the Excel sheet from Access as well as
passing a global variable from Access to Excel. What's a good way of
performing these tasks?
Message #2 by "Paul McLaren" <paulmcl@t...> on Thu, 20 Dec 2001 23:11:28 -0000
|
|
This is by no means the best solution but is certainly a good cheap and
cheerful alternative.
1) From Access write some code to export the data required as an excel file
to some folder on your PC, you can do this using 'docmd.outputto', if you
need help with the parameters record a macro and convert it to code.
2) Create an excel spreadsheet and record a macro to import the outputted
data and do any required formatting or other actions.
3) For operations run the process from Access to export the data, then
Access uses Automation to open the spreadsheet with the pre-recorded macro,
the recorded macro kicks in and the data is imported and presented as
required.
Complete rubbish really as it is not the tidiess but gets the desired
results.
I will be interested in the correct solution on passing variables via
Automation as I never did get round to working it out.
Regards
Paul
-----Original Message-----
From: patrick slesicki [mailto:patrick_slesicki@d...]
Sent: 20 December 2001 22:58
To: Access
Subject: [access] automation with excel
I want to transfer the contents of an Access 97 form to an Excel 97 sheet.
I'm having problems formatting the Excel sheet from Access as well as
passing a global variable from Access to Excel. What's a good way of
performing these tasks?
Message #3 by "John Ruff" <papparuff@c...> on Thu, 20 Dec 2001 16:38:35 -0800
|
|
Patrick,
Go to http://support.microsoft.com/default.aspx?scid=kb;EN-US;q167223
and download the Auto97.exe file. After downloading, run the program.
It provides you extremely valuable information and samples of how to
automate not only Excel, but Outlook, and Word. It has most of the
answers to your questions.
An easy way of creating the code to format Excel is to open Excel,
create a Macro and while the macro is running, do all your formatting.
Close the macro and then vew the code through the Excel VB Editor. You
can then cut and past the code into your Access procedure.
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: patrick slesicki [mailto:patrick_slesicki@d...]
Sent: Thursday, December 20, 2001 10:58 PM
To: Access
Subject: [access] automation with excel
I want to transfer the contents of an Access 97 form to an Excel 97
sheet.
I'm having problems formatting the Excel sheet from Access as well as
passing a global variable from Access to Excel. What's a good way of
performing these tasks?
Message #4 by Patrick_Slesicki@d... on Fri, 21 Dec 2001 09:48:56 -0800
|
|
Hi Thanks for the suggestions. They helped me get a little further. Still
having problems though. I'm first sending the data on my form to Excel
with the command
DoCmd.OutputTo acForm, "frmRFQReport", acFormatXLS, "C:
\WINNT\Profiles\pcuser\Desktop\RFQReport.xls", True, ""
My next lines of code are
Dim objExcel As Excel.Application
Set objExcel = GetObject(, "Excel.Application")
objExcel.Visible = True
With objExcel
Range("A1").CurrentRegion.Select
This last line generates an error message: Runtime Error '1004': Method
'Range' of object '_Global' failed.
Does anyone know what's happening here and how I can correct the problem?
Many thanks, once again.
Message #5 by Walt Morgan <wmorgan@s...> on Fri, 21 Dec 2001 11:58:16 -0600
|
|
If I understand correctly you are attempting to output to excel before
creating an instance of it.
Try reversing your code, i.e., place the creation of excel before you try to
output to it.
Hi Thanks for the suggestions. They helped me get a little further. Still
having problems though. I'm first sending the data on my form to Excel
with the command
DoCmd.OutputTo acForm, "frmRFQReport", acFormatXLS, "C:
\WINNT\Profiles\pcuser\Desktop\RFQReport.xls", True, ""
My next lines of code are
Dim objExcel As Excel.Application
Set objExcel = GetObject(, "Excel.Application")
objExcel.Visible = True
With objExcel
Range("A1").CurrentRegion.Select
Walt
Message #6 by "John Ruff" <papparuff@c...> on Fri, 21 Dec 2001 10:33:47 -0800
|
|
Patrick
Place a dot(.) before the word Range. Range is a property of objExcel
and to properly reference it, a dot must be before it.
With objExcel
.Range("A1").CurrentRegioin.Select
End with
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: Patrick_Slesicki@d... [mailto:Patrick_Slesicki@d...]
Sent: Friday, December 21, 2001 9:49 AM
To: Access
Subject: [access] RE: automation with excel
Hi Thanks for the suggestions. They helped me get a little further.
Still having problems though. I'm first sending the data on my form to
Excel with the command
DoCmd.OutputTo acForm, "frmRFQReport", acFormatXLS, "C:
\WINNT\Profiles\pcuser\Desktop\RFQReport.xls", True, ""
My next lines of code are
Dim objExcel As Excel.Application
Set objExcel = GetObject(, "Excel.Application")
objExcel.Visible = True
With objExcel
Range("A1").CurrentRegion.Select
This last line generates an error message: Runtime Error '1004': Method
'Range' of object '_Global' failed.
Does anyone know what's happening here and how I can correct the
problem? Many thanks, once again.
Message #7 by "patrick slesicki" <patrick_slesicki@d...> on Sat, 22 Dec 2001 00:20:56
|
|
Tried the dot and many other things but nothing seems to work.
my code is
With objExcel
.Visible = True
.Range("A1").CurrentRegion.Select
End With
I'm getting an error message "-2147023174 (800706ba): Automation Error,
the RPC server is unavailable" whenever the program goes to the following
block. I cannot get past the 'With Selection' statement. Any ideas?
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End With
Patrick
> Patrick
>
> Place a dot(.) before the word Range. Range is a property of objExcel
> and to properly reference it, a dot must be before it.
>
> With objExcel
> .Range("A1").CurrentRegioin.Select
> End with
>
> John Ruff - The Eternal Optimist :-)
>
>
>
> -----Original Message-----
> From: Patrick_Slesicki@d... [mailto:Patrick_Slesicki@d...]
> Sent: Friday, December 21, 2001 9:49 AM
> To: Access
> Subject: [access] RE: automation with excel
>
>
>
> Hi Thanks for the suggestions. They helped me get a little further.
> Still having problems though. I'm first sending the data on my form to
> Excel with the command
> DoCmd.OutputTo acForm, "frmRFQReport", acFormatXLS, "C:
> \WINNT\Profiles\pcuser\Desktop\RFQReport.xls", True, ""
>
> My next lines of code are
> Dim objExcel As Excel.Application
> Set objExcel = GetObject(, "Excel.Application")
> objExcel.Visible = True
> With objExcel
> Range("A1").CurrentRegion.Select
>
> This last line generates an error message: Runtime Error '1004': Method
> 'Range' of object '_Global' failed.
>
> Does anyone know what's happening here and how I can correct the
> problem? Many thanks, once again.
>
>
>
>
>
Message #8 by "dave sharpe" <pmmgpgp@y...> on Fri, 21 Dec 2001 19:35:28 -0500
|
|
Patrick
MS discusses this error on the page below.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q189949
I hope that it helps.
Dave
----- Original Message -----
From: "patrick slesicki" <patrick_slesicki@d...>
To: "Access" <access@p...>
Sent: Saturday, December 22, 2001 12:20 AM
Subject: [access] RE: automation with excel
Tried the dot and many other things but nothing seems to work.
my code is
With objExcel
.Visible = True
.Range("A1").CurrentRegion.Select
End With
I'm getting an error message "-2147023174 (800706ba): Automation Error,
the RPC server is unavailable" whenever the program goes to the following
block. I cannot get past the 'With Selection' statement. Any ideas?
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End With
Patrick
> Patrick
>
> Place a dot(.) before the word Range. Range is a property of objExcel
> and to properly reference it, a dot must be before it.
>
> With objExcel
> .Range("A1").CurrentRegioin.Select
> End with
>
> John Ruff - The Eternal Optimist :-)
>
>
>
> -----Original Message-----
> From: Patrick_Slesicki@d... [mailto:Patrick_Slesicki@d...]
> Sent: Friday, December 21, 2001 9:49 AM
> To: Access
> Subject: [access] RE: automation with excel
>
>
>
> Hi Thanks for the suggestions. They helped me get a little further.
> Still having problems though. I'm first sending the data on my form to
> Excel with the command
> DoCmd.OutputTo acForm, "frmRFQReport", acFormatXLS, "C:
> \WINNT\Profiles\pcuser\Desktop\RFQReport.xls", True, ""
>
> My next lines of code are
> Dim objExcel As Excel.Application
> Set objExcel = GetObject(, "Excel.Application")
> objExcel.Visible = True
> With objExcel
> Range("A1").CurrentRegion.Select
>
> This last line generates an error message: Runtime Error '1004': Method
> 'Range' of object '_Global' failed.
>
> Does anyone know what's happening here and how I can correct the
> problem? Many thanks, once again.
>
>
>
>
>
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Message #9 by "John Ruff" <papparuff@c...> on Fri, 21 Dec 2001 16:35:34 -0800
|
|
Patrick,
For the first part,
With objExcel
.ActiveSheet.Range("a1").Select
End with
For the second part,
With objExcel.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End With
I will again suggest you download the automation programs I suggested in
my earlier post. They will take you a long way to perform the tasks you
need.
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: patrick slesicki [mailto:patrick_slesicki@d...]
Sent: Saturday, December 22, 2001 12:21 AM
To: Access
Subject: [access] RE: automation with excel
Tried the dot and many other things but nothing seems to work.
my code is
With objExcel
.Visible = True
.Range("A1").CurrentRegion.Select
End With
I'm getting an error message "-2147023174 (800706ba): Automation Error,
the RPC server is unavailable" whenever the program goes to the
following
block. I cannot get past the 'With Selection' statement. Any ideas?
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End With
Patrick
> Patrick
>
> Place a dot(.) before the word Range. Range is a property of objExcel
> and to properly reference it, a dot must be before it.
>
> With objExcel
> .Range("A1").CurrentRegioin.Select
> End with
>
> John Ruff - The Eternal Optimist :-)
>
>
>
> -----Original Message-----
> From: Patrick_Slesicki@d... [mailto:Patrick_Slesicki@d...]
> Sent: Friday, December 21, 2001 9:49 AM
> To: Access
> Subject: [access] RE: automation with excel
>
>
>
> Hi Thanks for the suggestions. They helped me get a little further.
> Still having problems though. I'm first sending the data on my form
> to Excel with the command
> DoCmd.OutputTo acForm, "frmRFQReport", acFormatXLS, "C:
> \WINNT\Profiles\pcuser\Desktop\RFQReport.xls", True, ""
>
> My next lines of code are
> Dim objExcel As Excel.Application
> Set objExcel = GetObject(, "Excel.Application")
> objExcel.Visible = True
> With objExcel
> Range("A1").CurrentRegion.Select
>
> This last line generates an error message: Runtime Error '1004':
> Method 'Range' of object '_Global' failed.
>
> Does anyone know what's happening here and how I can correct the
> problem? Many thanks, once again.
>
>
$subst('Email.Unsub').
>
>
>
Message #10 by "patrick slesicki" <patrick_slesicki@d...> on Sat, 22 Dec 2001 01:34:02
|
|
John,
That seems to have accomplished the trick. I did get the Auto97.exe as you
suggested. The help files are still a little perplexing as I'm new to
Automation. Are there any other resources that you know of? Many thanks
for your help.
> Patrick,
>
> For the first part,
>
> With objExcel
> .ActiveSheet.Range("a1").Select
> End with
>
> For the second part,
>
> With objExcel.Selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlCenter
> .WrapText = False
> .Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
> End With
>
> I will again suggest you download the automation programs I suggested in
> my earlier post. They will take you a long way to perform the tasks you
> need.
>
> John Ruff - The Eternal Optimist :-)
>
>
>
> -----Original Message-----
> From: patrick slesicki [mailto:patrick_slesicki@d...]
> Sent: Saturday, December 22, 2001 12:21 AM
> To: Access
> Subject: [access] RE: automation with excel
>
>
> Tried the dot and many other things but nothing seems to work.
> my code is
>
> With objExcel
> .Visible = True
> .Range("A1").CurrentRegion.Select
> End With
>
> I'm getting an error message "-2147023174 (800706ba): Automation Error,
> the RPC server is unavailable" whenever the program goes to the
> following
> block. I cannot get past the 'With Selection' statement. Any ideas?
>
> With Selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlCenter
> .WrapText = False
> .Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
> End With
>
> Patrick
>
>
> > Patrick
> >
> > Place a dot(.) before the word Range. Range is a property of objExcel
>
> > and to properly reference it, a dot must be before it.
> >
> > With objExcel
> > .Range("A1").CurrentRegioin.Select
> > End with
> >
> > John Ruff - The Eternal Optimist :-)
> >
> >
> >
> > -----Original Message-----
> > From: Patrick_Slesicki@d... [mailto:Patrick_Slesicki@d...]
> > Sent: Friday, December 21, 2001 9:49 AM
> > To: Access
> > Subject: [access] RE: automation with excel
> >
> >
> >
> > Hi Thanks for the suggestions. They helped me get a little further.
> > Still having problems though. I'm first sending the data on my form
> > to Excel with the command
> > DoCmd.OutputTo acForm, "frmRFQReport", acFormatXLS, "C:
> > \WINNT\Profiles\pcuser\Desktop\RFQReport.xls", True, ""
> >
> > My next lines of code are
> > Dim objExcel As Excel.Application
> > Set objExcel = GetObject(, "Excel.Application")
> > objExcel.Visible = True
> > With objExcel
> > Range("A1").CurrentRegion.Select
> >
> > This last line generates an error message: Runtime Error '1004':
> > Method 'Range' of object '_Global' failed.
> >
> > Does anyone know what's happening here and how I can correct the
> > problem? Many thanks, once again.
> >
> >
> $subst('Email.Unsub').
> >
> >
> >
>
>
>
>
Message #11 by "John Ruff" <papparuff@c...> on Fri, 21 Dec 2001 20:15:15 -0800
|
|
Patrick,
I know how perplexing automation can be. I don't know of too many
resources on the subject other than what I suggested earlier. What
really helped me in learning (and we're all still learning) automation
was starting the recording of macros in Excel and then doing the
formatting and creating the formulas and everything else I wanted to do
in Excel. I then stopped the macros and went to the VBE (VB editor) to
see the code that was generated. I would then cut and paste the code to
my Access module and make the necessary modifications for Access to run
the code.
Sorry I can't provide more resources.
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: patrick slesicki [mailto:patrick_slesicki@d...]
Sent: Saturday, December 22, 2001 1:34 AM
To: Access
Subject: [access] RE: automation with excel
John,
That seems to have accomplished the trick. I did get the Auto97.exe as
you
suggested. The help files are still a little perplexing as I'm new to
Automation. Are there any other resources that you know of? Many thanks
for your help.
> Patrick,
>
> For the first part,
>
> With objExcel
> .ActiveSheet.Range("a1").Select
> End with
>
> For the second part,
>
> With objExcel.Selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlCenter
> .WrapText = False
> .Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
> End With
>
> I will again suggest you download the automation programs I suggested
> in my earlier post. They will take you a long way to perform the
> tasks you need.
>
> John Ruff - The Eternal Optimist :-)
>
>
>
> -----Original Message-----
> From: patrick slesicki [mailto:patrick_slesicki@d...]
> Sent: Saturday, December 22, 2001 12:21 AM
> To: Access
> Subject: [access] RE: automation with excel
>
>
> Tried the dot and many other things but nothing seems to work.
> my code is
>
> With objExcel
> .Visible = True
> .Range("A1").CurrentRegion.Select
> End With
>
> I'm getting an error message "-2147023174 (800706ba): Automation
> Error,
> the RPC server is unavailable" whenever the program goes to the
> following
> block. I cannot get past the 'With Selection' statement. Any ideas?
>
> With Selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlCenter
> .WrapText = False
> .Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
> End With
>
> Patrick
>
>
> > Patrick
> >
> > Place a dot(.) before the word Range. Range is a property of
> > objExcel
>
> > and to properly reference it, a dot must be before it.
> >
> > With objExcel
> > .Range("A1").CurrentRegioin.Select
> > End with
> >
> > John Ruff - The Eternal Optimist :-)
> >
> >
> >
> > -----Original Message-----
> > From: Patrick_Slesicki@d...
> > [mailto:Patrick_Slesicki@d...]
> > Sent: Friday, December 21, 2001 9:49 AM
> > To: Access
> > Subject: [access] RE: automation with excel
> >
> >
> >
> > Hi Thanks for the suggestions. They helped me get a little further.
> > Still having problems though. I'm first sending the data on my form
> > to Excel with the command
> > DoCmd.OutputTo acForm, "frmRFQReport", acFormatXLS, "C:
> > \WINNT\Profiles\pcuser\Desktop\RFQReport.xls", True, ""
> >
> > My next lines of code are
> > Dim objExcel As Excel.Application
> > Set objExcel = GetObject(, "Excel.Application")
> > objExcel.Visible = True
> > With objExcel
> > Range("A1").CurrentRegion.Select
> >
> > This last line generates an error message: Runtime Error '1004':
> > Method 'Range' of object '_Global' failed.
> >
> > Does anyone know what's happening here and how I can correct the
> > problem? Many thanks, once again.
> >
> >
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or to
> > unsubscribe send a blank email to
> $subst('Email.Unsub').
> >
> >
> >
>
$subst('Email.Unsub').
>
>
>
|
|
|
View Cart
