visual basic problem

[apgoodb]

I have code that exports data to excel and formats it. I originally
put it into the on click method; however, it has not gotten too large.
I want to split it out into different modules; however, I am not sure
how to start formatting an open excel file. What I have done is export
the data, open the file and then do some initial formatting. The next
"function"? would be to do more formatting on the open excel file. Is
there a way to do this? It seems like I need to re-declare the excel
file somehow.
Thanks for any help you can give.

[Alan Webb]

Apgoodb,
Oh my, yes. If you create an instance of Excel in your VBA code that then
opens the worksheet for you there is a huge amount of stuff you can do.

<ap*****@yahoo.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

I have code that exports data to excel and formats it. I originally
put it into the on click method; however, it has not gotten too large.
I want to split it out into different modules; however, I am not sure
how to start formatting an open excel file. What I have done is export
the data, open the file and then do some initial formatting. The next
"function"? would be to do more formatting on the open excel file. Is
there a way to do this? It seems like I need to re-declare the excel
file somehow.
Thanks for any help you can give.

[apgoodb]

Also, here is some of the code that I have:
on click event:
Dim strSpreadsheet As String
Dim xlapp As New Excel.Application
strSpreadsheet = [txtdirectory]
xlapp.Visible = True
xlapp.Workbooks.Open strSpreadsheet
'add client and report names to header
Dim clientname As String
Dim reportnamename As String
clientname = Trim(DLookup("[client_name]", "clients", "[client_id] =" &
[client]))
reportnamename = Trim(DLookup("[report_name]", "reports", "[report_id]
=" & [report]))
xlapp.Application.worksheets("Summary").Range("a1" ).Value = clientname

function code:
Function pcpfunction()
'format individual sheets
xlapp.Application.Sheets("PCP_High").Select
xlapp.Application.worksheets("PCP_High").Range("d1 :f1").MergeCells =
True

It seems like I need to declare what xlapp is in the function?
Thanks,

[Alan Webb]

Apgood,
Isn't that what "Dim xlapp as New Excel.Application" does?

<ap*****@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Also, here is some of the code that I have:
on click event:
Dim strSpreadsheet As String
Dim xlapp As New Excel.Application

[MacDermott]

You can do this in a couple of ways.
1. You can declare xlapp outside of both procedures, at the top of the form
module, if all procedures will be within the same form module, or at the top
of a standard module.
2. You can pass xlapp as an argument to the function.

What you don't want to do is declare it a second time in the second
function -
that will give you a new instance of Excel, which won't know anything
about what you've done in the first one!

HTH

<ap*****@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Also, here is some of the code that I have:
on click event:
Dim strSpreadsheet As String
Dim xlapp As New Excel.Application
strSpreadsheet = [txtdirectory]
xlapp.Visible = True
xlapp.Workbooks.Open strSpreadsheet
'add client and report names to header
Dim clientname As String
Dim reportnamename As String
clientname = Trim(DLookup("[client_name]", "clients", "[client_id] =" &
[client]))
reportnamename = Trim(DLookup("[report_name]", "reports", "[report_id]
=" & [report]))
xlapp.Application.worksheets("Summary").Range("a1" ).Value = clientname

function code:
Function pcpfunction()
'format individual sheets
xlapp.Application.Sheets("PCP_High").Select
xlapp.Application.worksheets("PCP_High").Range("d1 :f1").MergeCells =
True

It seems like I need to declare what xlapp is in the function?
Thanks,

[apgoodb]

I don't think I can declare it outside because the file name is random
based upon where the user wants to save it. How would I do option 2 to
pass the argument to the function?
Thanks,

MacDermott wrote:

You can do this in a couple of ways.
1. You can declare xlapp outside of both procedures, at the top of the form module, if all procedures will be within the same form module, or at the top of a standard module.
2. You can pass xlapp as an argument to the function.

What you don't want to do is declare it a second time in the second
function -
that will give you a new instance of Excel, which won't know anything about what you've done in the first one!

HTH

<ap*****@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Also, here is some of the code that I have:
on click event:
Dim strSpreadsheet As String
Dim xlapp As New Excel.Application
strSpreadsheet = [txtdirectory]
xlapp.Visible = True
xlapp.Workbooks.Open strSpreadsheet
'add client and report names to header
Dim clientname As String
Dim reportnamename As String
clientname = Trim(DLookup("[client_name]", "clients", "[client_id] =" & [client]))
reportnamename = Trim(DLookup("[report_name]", "reports", "[report_id] =" & [report]))
xlapp.Application.worksheets("Summary").Range("a1" ).Value = clientname
function code:
Function pcpfunction()
'format individual sheets
xlapp.Application.Sheets("PCP_High").Select
xlapp.Application.worksheets("PCP_High").Range("d1 :f1").MergeCells = True

It seems like I need to declare what xlapp is in the function?
Thanks,

[MacDermott]

"Declaring" it merely means using a Dim or Public (or Private) statement to
allocate memory for it.
For example:
Public xlapp as Excel.Application
or
Dim xlapp as Object
If you do this outside of the procedure(s), you can then instantiate it
inside a procedure, and references from other procedures will use the same
instance.

To pass it as an argument, you would declare your second procedure with an
argument, e.g.
Function pcpfunction(xlapp as Excel.application)
You'd call it from your first function with something like this:
pcpfunction xlapp

HTH
- Turtle

<ap*****@yahoo.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

I don't think I can declare it outside because the file name is random
based upon where the user wants to save it. How would I do option 2 to
pass the argument to the function?
Thanks,

MacDermott wrote:

You can do this in a couple of ways.
1. You can declare xlapp outside of both procedures, at the top of

the form

module, if all procedures will be within the same form module, or at

the top

of a standard module.
2. You can pass xlapp as an argument to the function.

What you don't want to do is declare it a second time in the second
function -
that will give you a new instance of Excel, which won't know

anything

about what you've done in the first one!

HTH

<ap*****@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Also, here is some of the code that I have:
on click event:
Dim strSpreadsheet As String
Dim xlapp As New Excel.Application
strSpreadsheet = [txtdirectory]
xlapp.Visible = True
xlapp.Workbooks.Open strSpreadsheet
'add client and report names to header
Dim clientname As String
Dim reportnamename As String
clientname = Trim(DLookup("[client_name]", "clients", "[client_id] =" & [client]))
reportnamename = Trim(DLookup("[report_name]", "reports", "[report_id] =" & [report]))
xlapp.Application.worksheets("Summary").Range("a1" ).Value = clientname
function code:
Function pcpfunction()
'format individual sheets
xlapp.Application.Sheets("PCP_High").Select
xlapp.Application.worksheets("PCP_High").Range("d1 :f1").MergeCells = True

It seems like I need to declare what xlapp is in the function?
Thanks,