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. 6 1550
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******** *************@f 14g2000cwb.goog legroups.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.
Also, here is some of the code that I have:
on click event:
Dim strSpreadsheet As String
Dim xlapp As New Excel.Applicati on
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.Applicati on.worksheets(" Summary").Range ("a1").Value = clientname
function code:
Function pcpfunction()
'format individual sheets
xlapp.Applicati on.Sheets("PCP_ High").Select
xlapp.Applicati on.worksheets(" PCP_High").Rang e("d1:f1").Merg eCells =
True
It seems like I need to declare what xlapp is in the function?
Thanks,
Apgood,
Isn't that what "Dim xlapp as New Excel.Applicati on" does?
<ap*****@yahoo. com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. . Also, here is some of the code that I have: on click event: Dim strSpreadsheet As String Dim xlapp As New Excel.Applicati on
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.goo glegroups.com.. . Also, here is some of the code that I have: on click event: Dim strSpreadsheet As String Dim xlapp As New Excel.Applicati on 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.Applicati on.worksheets(" Summary").Range ("a1").Value = clientname
function code: Function pcpfunction() 'format individual sheets xlapp.Applicati on.Sheets("PCP_ High").Select xlapp.Applicati on.worksheets(" PCP_High").Rang e("d1:f1").Merg eCells = True
It seems like I need to declare what xlapp is in the function? Thanks,
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.goo glegroups.com.. . Also, here is some of the code that I have: on click event: Dim strSpreadsheet As String Dim xlapp As New Excel.Applicati on 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.Applicati on.worksheets(" Summary").Range ("a1").Value =
clientname function code: Function pcpfunction() 'format individual sheets xlapp.Applicati on.Sheets("PCP_ High").Select xlapp.Applicati on.worksheets(" PCP_High").Rang e("d1:f1").Merg eCells
= True
It seems like I need to declare what xlapp is in the function? Thanks,
"Declaring" it merely means using a Dim or Public (or Private) statement to
allocate memory for it.
For example:
Public xlapp as Excel.Applicati on
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(xla pp as Excel.applicati on)
You'd call it from your first function with something like this:
pcpfunction xlapp
HTH
- Turtle
<ap*****@yahoo. com> wrote in message
news:11******** ************@f1 4g2000cwb.googl egroups.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.goo glegroups.com.. . Also, here is some of the code that I have: on click event: Dim strSpreadsheet As String Dim xlapp As New Excel.Applicati on 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.Applicati on.worksheets(" Summary").Range ("a1").Value = clientname function code: Function pcpfunction() 'format individual sheets xlapp.Applicati on.Sheets("PCP_ High").Select xlapp.Applicati on.worksheets(" PCP_High").Rang e("d1:f1").Merg eCells = True
It seems like I need to declare what xlapp is in the function? Thanks, This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: John |
last post by:
I have a problem, it's not with any code I have because... there is no code.
When I run a blank visual basic 6 form, it opens up just fine. When I add a
text box, a caption, and a button... it gives me an error from microsoft's
error report and asks me to send or not send the error, i chose and then it
quits. The error report won't let me copy...
|
by: delete table with Visual Basic 6 |
last post by:
Dear developer,
I have an error message when I try to delete or drop Microsoft Access XP
table with ADOX in Visual Basic 6. I use the ADOX.Catalog and ADOX.table to
create and delete or drop table within Visual Basic program.
If anybody knows how to solve the problem, please give me the solution.
I would like to thank you for your attention...
|
by: Bruno Jouhier [MVP] |
last post by:
I'm currently experiencing a strange phenomenon:
At my Office, Visual Studio takes a very long time to compile our solution
(more than 1 minute for the first project).
At home, Visual Studio compiles the same solution much faster (about 10
seconds for the first project).
My home computer is only marginally faster than the one I have at...
|
by: frossberg |
last post by:
Hello!
I tried to install the Visual Basic.NET Resource Kit
(http://msdn.microsoft.com/vbasic/vbrkit/) but obviously something went very
wrong and now it sems impossible both to repair and to remove (to start over
from scratch) the installation. The messages popping up after choosing one of
these two alternatives Repair/Remove Visual...
|
by: MikeB |
last post by:
I've been all over the net with this question, I hope I've finally
found a group where I can ask about Visual Basic 2005.
I'm at uni and we're working with Visual Basic 2005. I have some books,
- Programming Visual Basic by Balena (MS Press) and
- Visual Basic 2005 by Willis (WROX),
but they don't go into the forms design aspects and...
| |
by: praful pathak |
last post by:
i
i am praful pathak,porbandar
i want to develop my own cross tab report in visual basic 6 i know what
developed query from ms access but how to coded in visual basic in
designing time and how to construct it
plz solve this problem
query from ms access
TRANSFORM Sum(Temp.SumOfS_Qty) AS SumOfSumOfS_Qty
|
by: sqlguy |
last post by:
Why do we have to contact MS for a problem that has been with this
compiler from at least the beta of VS 20005. I am so sick and tired of
the 30 - 40 clicks it takes to dismiss VS when there is a problem. Can
they not just post the fix. I see no reason to contact MS since I have
most likely sent about 1500 dumps to them and I would think...
|
by: Master Programmer |
last post by:
An friend insider told me that VB is to be killled off within 18
months. I guess this makes sence now that C# is here. I believe it and
am actualy surprised they ever even included it in VS 2003 in the first
place. Anyone else heard about this development?
The Master
|
by: Salman |
last post by:
I would like to know how I can distribute the application that I
create with Visual C++ express edition. I checked the menu options to
find a deploy option similar to the one found on the Visual Basic
Express Edition but could not find one. I have even searched the help
files and could not find anything useful for me. I want to simply...
|
by: Ronm |
last post by:
Hey Guys,
I have a problem which has been driving me crazy for the last days.
I'm working on a small project involving:
Visual Basic
Acces Database
ASP
and adobe/macromedia Flash,
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |