Excel for Life: 25 new messages in 13 topics

microsoft.public.access.modulesdaovba
http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

==============================================================================
TOPIC: Merging with Word
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/6d6999668610699e?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 9:41 am
From: mikelee101@hotmail.com

Hello,
I am trying to do something that I'm not sure is possible, but was
hoping someone could show me a way. I have a database on a shared
drive that multiple users access concurrently. The database is not
split. I am trying to allow a "one click" generation of various form
letters that my team needs to fax/mail/email. In searching the forum,
I've found and tried the following possible solutions:

--Reports don't seem to work, as we have images that need to be
output. We don't have Acrobat standard, nor do we have admin rights
to our machines, so installing a pdf writer would be difficult. Our
only real choice of output is rtf (Word), which won't carry the
images.
--I also checked out Albert Kallal's WordMerge utility but, per the
info on his site, multiple users "will trip over each other" if the
database isn't split. I know almost nothing about splitting
databases, but want to maintain continuity in Forms, Reports, Queries,
etc., so it doesn't sound like that's something I want to do either.

So, what I tried is to have Access open a Word Doc and fill in the
information that I want carried over. I have code in Access that will
open the Word Doc. I have code in the Word Doc that will insert the
necessary information in the correct places. What I don't have is a
way to pass the variables between applications. Here is how I'm
opening the Word Doc:

Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
Set WordDoc = WordApp.Documents.Open("~File path and file name~")

What I'm looking for is some sort of line like this:

WordDoc.Call.ChangeFields(DomNme, ContNme, ExpDte, EmlAddr)

However, none of the syntax that I've tried has worked, and I couldn't
find much in the VBA help files of either Access or Word. Access2000,
Word2003, XPPro SP2.

If anyone has any ideas, or can tell me that I'm chasing shadows and
can therefore stop, I'd really appreciate it.

Thanks to all.

Mike Lee
Coppell, TX, USA

==============================================================================
TOPIC: Access xp - Why does debugger break in when I have no breakpoi
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a8a243abe3a1ed94?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 9:49 am
From: JDRaven

Thanks. I tried that first, and it did not resolve my particular problem.

And thanks for not pointing out my basic math miscalc: the original post was
3 years old and not 2 ;o)

Have a great day!

"Robert Morley" wrote:

> Another common method is to set and clear a breakpoint on the line that has
> the phantom breakpoint. I've had fairly good luck with that method so far,
> and it's nice and quick.
>
>
> Rob
>
> JDRaven wrote:
> > 2 years later, and this post just helped me resolve a problem that cropped up
> > today with an Access database somebody else wrote. Your post has withstood
> > the test of time.
> >
> > Thanks!
> >
> > "Ken Snell [MVP]" wrote:
> >
> >> VBE sometimes remembers "phantom" breakpoints.
> >>
> >> Two ways that I use to fix this problem.
> >>
> >> (1) Comment out the line on which the breakpoint occurs. If the code won't
> >> compile without that line, then comment out a block of code so that the code
> >> can compile without it. Compile the code. Close the database. Open the
> >> database. Uncomment out the code. Compile the code.
> >>
> >> (2) Use the undocumented decompile switch when opening the database so that
> >> all compiled code is uncompiled.
> >> "PathToAccessExecutable\msaccess.exe" /decompile
> >> "PathToYourDataBaseFile\DatabaseFileName.mdb"
> >>
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >>
> >> "DJ Becker" <DJ Becker@discussions.microsoft.com> wrote in message
> >> news:DA0E9301-0716-4E1E-86C3-04C2D15D2F34@microsoft.com...
> >>> I've made a change to an app that's been running for years. After adding
> >>> some code, now the debugger breaks in at a particular line even though I
> >>> have
> >>> no breakpoint set there - I've Cleared All Breakpoints from the Debug
> >>> menu.
> >>> If I click continue, the app continues without error. I need this code to
> >>> keep running - the users will not know what to do and shouldn't have to
> >>> deal
> >>> with it anyway. Any ideas?
> >>
> >>
>

==============================================================================
TOPIC: how to enter data simultaneously to two different tables in Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0d72131113b6ee88?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 10:19 am
From: John W. Vinson

On Thu, 28 Feb 2008 01:56:02 -0800, Kate <Kate@discussions.microsoft.com>
wrote:

>I would like to know how to program the database so the data can be entered
>simultaneously to two different columns in two different tables at the same
>time. For example, I have Table 1, and one column within that table called
>"proejct #". I have Table 2, again with one column called project #. How do
>I set up the database so I can enter projec numbers to these two tables
>simultaneously. Any help would be greatly appreciated.
>
>Kate

You wouldn't, generally. Creating an empty "placeholder" record in Table2 is
very rarely a good idea!

If table1 is related one-to-many to table2, typically you would use a Form
based on table1 with a Subform based on table2, using [Project #] as the
master/link field. This will ensure that Table2 inherits the project number as
soon as there is any data entered into Table2 (but not before, because it
isn't needed before!).

Just a side note - you may want to avoid the use of special characters such as
blanks and # in fieldnames. # is a date delimiter and Access can get confused.
A field named ProjectNo will be less hassle and will let you upgrade more
easily to SQL/Server if that should ever come to pass. If you do use Project #
then you must ALWAYS enclose the fieldname in brackets - [Project #].
--
John W. Vinson [MVP]

==============================================================================
TOPIC: Are there specific compilers required for C programming?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a64256192545373e?hl=en
==============================================================================

== 1 of 3 ==
Date: Thurs, Feb 28 2008 10:20 am
From: John W. Vinson

On Thu, 28 Feb 2008 07:22:01 -0800, Michaela <granyausom@aol.com> wrote:

Yes. There are many C compilers on the market, and C code is utterly useless
unless it's compiled. Different compilers will often have subtle dialect
differences in the syntax and features of the language.

I'm curious that you're asking this question in a newsgroup supporting the
altogether different program Microsoft Access.
--
John W. Vinson [MVP]

== 2 of 3 ==
Date: Thurs, Feb 28 2008 1:17 pm
From: Michaela

Thank you for your response. a few years ago I had taken a course in C++
programming to determine the basis of most programs. It was very interesting
and gave me a little background [very basic] information on programming set
up for accomplishing a way to provide a source for our company to handle
orders etc. After that experience I handled our orders, inventory and
various other information with Lotus Smart Suite and QuickBookPro. My
husband and I had a Company that was abnormal and did not handle ordinary
"widgets". It is a semi service company that reclaims industrial oils.
Basically it acquires oil from the customer and reprocesses it to a "like
new" oil and returns it to the customer at the cost of restoration. This
company is currently being run and handled by our children. I still help
with refining their processes and hoped to learn to use Microsoft Access
enough to change over to confirm to the majority of the users in the
industries.
--
Mickey

"John W. Vinson" wrote:

> On Thu, 28 Feb 2008 07:22:01 -0800, Michaela <granyausom@aol.com> wrote:
>
> Yes. There are many C compilers on the market, and C code is utterly useless
> unless it's compiled. Different compilers will often have subtle dialect
> differences in the syntax and features of the language.
>
> I'm curious that you're asking this question in a newsgroup supporting the
> altogether different program Microsoft Access.
> --
> John W. Vinson [MVP]
>

== 3 of 3 ==
Date: Thurs, Feb 28 2008 3:15 pm
From: John W. Vinson

On Thu, 28 Feb 2008 13:17:03 -0800, Michaela <granyausom@aol.com> wrote:

>Thank you for your response. a few years ago I had taken a course in C++
>programming to determine the basis of most programs. It was very interesting
>and gave me a little background [very basic] information on programming set
>up for accomplishing a way to provide a source for our company to handle
>orders etc. After that experience I handled our orders, inventory and
>various other information with Lotus Smart Suite and QuickBookPro. My
>husband and I had a Company that was abnormal and did not handle ordinary
>"widgets". It is a semi service company that reclaims industrial oils.
>Basically it acquires oil from the customer and reprocesses it to a "like
>new" oil and returns it to the customer at the cost of restoration. This
>company is currently being run and handled by our children. I still help
>with refining their processes and hoped to learn to use Microsoft Access
>enough to change over to confirm to the majority of the users in the
>industries.

ok... that's interesting... but irrelevant.

C is one language (actually a family of older languages).

C++ is a QUITE DIFFERENT language, one which grew out of C but is distinct.

Neither one is used in ordinary Access database developement (though I believe
large parts of the Access executable were written in C, C++ or C# or some
combination of the above).

In any case, there is no C (or C++ or C#) compiler in Access, and Access would
not be a suitable programming enviornment for such programs.

Do you want to develop applications for this business in Access (for which the
programming language of choice is Visual Basic for Applictions, aka VBA)? or
in C? or in C++? Either of the latter will be a VAST amount more work.

--
John W. Vinson [MVP]

==============================================================================
TOPIC: How to access an instance of a report that is generated?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/d0ed78e507f14137?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Feb 28 2008 10:58 am
From: Wayne-I-M

Not sure I understand this. Anything that can be created in a report can
also be create on a form. So instead of running the 1st report you could
create a form and run the "2nd" (?) report from that

Just an idea

--
Wayne
Manchester, England.

"Archer" wrote:

> I need to work on data that's created once the report's generated and based
> on it either generate a new report.
> or
> write a macro to generate a report based on the conditions I set based on a
> query I run on the database tables
>
> Could anyone happen to know anything related to this?
>

== 2 of 2 ==
Date: Thurs, Feb 28 2008 1:23 pm
From: John W. Vinson

On Thu, 28 Feb 2008 09:47:02 -0800, Archer <Archer@discussions.microsoft.com>
wrote:

>I need to work on data that's created once the report's generated and based
>on it either generate a new report.
>or
>write a macro to generate a report based on the conditions I set based on a
>query I run on the database tables
>
>Could anyone happen to know anything related to this?

Well, both Forms and Reports are just tools. There is no data stored in a
report, and you can't search or reuse the "generated report"'s data! The data
exists solely in the table, perhaps combined with calculated fields in a
Query.

Could you explain more about the nature of your data and of the report you
want to generate?
--
John W. Vinson [MVP]

==============================================================================
TOPIC: Access 97 => 2003
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/285c89a0db9cb6aa?hl=en
==============================================================================

== 1 of 6 ==
Date: Thurs, Feb 28 2008 11:21 am
From: KarenFio

I converted an Access 97 database to 2003 and a form that uses the following
code to choose a start date for a report did not work:

Private Sub TDDReportTime_AfterUpdate()
Select Case Me.TDDReportTime
Case 1
MyStartDate = DateAdd("d", -1, Date)
Case 2
MyStartDate = DateAdd("d", -2, Date)
Case 3
MyStartDate = DateAdd("d", -7, Date)
Case 4
MyStartDate = DateAdd("d", -14, Date)
Case 5
MyStartDate = DateAdd("m", -1, Date)
Case 6
MyStartDate = DateAdd("m", -2, Date)
Case 7
MyStartDate = DateAdd("m", -3, Date)
Case 8
MyStartDate = #10/1/2004#

Case 9
MyStartDate = #10/1/2005#
Case 10
MyStartDate = #10/1/2006#
Case 11
MyStartDate = #4/15/2006#
Case 12
MyStartDate = #10/1/2007#

End Select
Me.Requery

The text box that should display the date has the message "#Name?".

Access 2003 keeps crashing with the converted database. I have tried
turning of Name AutoCorrect.

Any suggstions would be greatly appreciated. Thanks.

== 2 of 6 ==
Date: Thurs, Feb 28 2008 11:32 am
From: Wayne-I-M

this works fine when testing - I just ran it on a small unbound form and it
seems OK. They may be another problem not connected with this option group

Option Compare Database
Option Explicit

Private Sub TDDReportTime_AfterUpdate()
Select Case Me.TDDReportTime
Case 1
MyStartDate = DateAdd("d", -1, Date)
Case 2
MyStartDate = DateAdd("d", -2, Date)
Case 3
MyStartDate = DateAdd("d", -7, Date)
Case 4
MyStartDate = DateAdd("d", -14, Date)
Case 5
MyStartDate = DateAdd("m", -1, Date)
Case 6
MyStartDate = DateAdd("m", -2, Date)
Case 7
MyStartDate = DateAdd("m", -3, Date)
Case 8
MyStartDate = #10/1/2004#
Case 9
MyStartDate = #10/1/2005#
Case 10
MyStartDate = #10/1/2006#
Case 11
MyStartDate = #4/15/2006#
Case 12
MyStartDate = #10/1/2007#
End Select
End Sub

--
Wayne
Manchester, England.

"KarenFio" wrote:

> I converted an Access 97 database to 2003 and a form that uses the following
> code to choose a start date for a report did not work:
>
> Private Sub TDDReportTime_AfterUpdate()
> Select Case Me.TDDReportTime
> Case 1
> MyStartDate = DateAdd("d", -1, Date)
> Case 2
> MyStartDate = DateAdd("d", -2, Date)
> Case 3
> MyStartDate = DateAdd("d", -7, Date)
> Case 4
> MyStartDate = DateAdd("d", -14, Date)
> Case 5
> MyStartDate = DateAdd("m", -1, Date)
> Case 6
> MyStartDate = DateAdd("m", -2, Date)
> Case 7
> MyStartDate = DateAdd("m", -3, Date)
> Case 8
> MyStartDate = #10/1/2004#
>
> Case 9
> MyStartDate = #10/1/2005#
> Case 10
> MyStartDate = #10/1/2006#
> Case 11
> MyStartDate = #4/15/2006#
> Case 12
> MyStartDate = #10/1/2007#
>
>
>
> End Select
> Me.Requery
>
>
> The text box that should display the date has the message "#Name?".
>
> Access 2003 keeps crashing with the converted database. I have tried
> turning of Name AutoCorrect.
>
> Any suggstions would be greatly appreciated. Thanks.
>

== 3 of 6 ==
Date: Thurs, Feb 28 2008 11:42 am
From: KarenFio

Thanks for testing it. I'll have to see what else it might be.

Another thing I forgot to mention which is probably more urgent is that I
can't open, go into design view, or print preview any of my reports in other
databases created in Access 2003 after I converted this database. I can
still do everything in Access 97.

Any suggestions or ideas would be appreciated.

"Wayne-I-M" wrote:

> Hi
>
> this works fine when testing - I just ran it on a small unbound form and it
> seems OK. They may be another problem not connected with this option group
>
>
>
>
> Option Compare Database
> Option Explicit
>
> Private Sub TDDReportTime_AfterUpdate()
> Select Case Me.TDDReportTime
> Case 1
> MyStartDate = DateAdd("d", -1, Date)
> Case 2
> MyStartDate = DateAdd("d", -2, Date)
> Case 3
> MyStartDate = DateAdd("d", -7, Date)
> Case 4
> MyStartDate = DateAdd("d", -14, Date)
> Case 5
> MyStartDate = DateAdd("m", -1, Date)
> Case 6
> MyStartDate = DateAdd("m", -2, Date)
> Case 7
> MyStartDate = DateAdd("m", -3, Date)
> Case 8
> MyStartDate = #10/1/2004#
> Case 9
> MyStartDate = #10/1/2005#
> Case 10
> MyStartDate = #10/1/2006#
> Case 11
> MyStartDate = #4/15/2006#
> Case 12
> MyStartDate = #10/1/2007#
> End Select
> End Sub
>
> --
> Wayne
> Manchester, England.
>
>
>
> "KarenFio" wrote:
>
> > I converted an Access 97 database to 2003 and a form that uses the following
> > code to choose a start date for a report did not work:
> >
> > Private Sub TDDReportTime_AfterUpdate()
> > Select Case Me.TDDReportTime
> > Case 1
> > MyStartDate = DateAdd("d", -1, Date)
> > Case 2
> > MyStartDate = DateAdd("d", -2, Date)
> > Case 3
> > MyStartDate = DateAdd("d", -7, Date)
> > Case 4
> > MyStartDate = DateAdd("d", -14, Date)
> > Case 5
> > MyStartDate = DateAdd("m", -1, Date)
> > Case 6
> > MyStartDate = DateAdd("m", -2, Date)
> > Case 7
> > MyStartDate = DateAdd("m", -3, Date)
> > Case 8
> > MyStartDate = #10/1/2004#
> >
> > Case 9
> > MyStartDate = #10/1/2005#
> > Case 10
> > MyStartDate = #10/1/2006#
> > Case 11
> > MyStartDate = #4/15/2006#
> > Case 12
> > MyStartDate = #10/1/2007#
> >
> >
> >
> > End Select
> > Me.Requery
> >
> >
> > The text box that should display the date has the message "#Name?".
> >
> > Access 2003 keeps crashing with the converted database. I have tried
> > turning of Name AutoCorrect.
> >
> > Any suggstions would be greatly appreciated. Thanks.
> >

== 4 of 6 ==
Date: Thurs, Feb 28 2008 11:48 am
From: Wayne-I-M

I think (just my opinion) that the best site you can look at for 97 -> 2K3 is
this

http://allenbrowne.com/ser-48.html

Have a look and see what you can find. Sorry can't be much help as I have
never seen your problem with the design view. When we converted (back in the
day) it seemed to go quite smoothly

--
Wayne
Manchester, England.

"KarenFio" wrote:

> Thanks for testing it. I'll have to see what else it might be.
>
> Another thing I forgot to mention which is probably more urgent is that I
> can't open, go into design view, or print preview any of my reports in other
> databases created in Access 2003 after I converted this database. I can
> still do everything in Access 97.
>
> Any suggestions or ideas would be appreciated.
>
> "Wayne-I-M" wrote:
>
> > Hi
> >
> > this works fine when testing - I just ran it on a small unbound form and it
> > seems OK. They may be another problem not connected with this option group
> >
> >
> >
> >
> > Option Compare Database
> > Option Explicit
> >
> > Private Sub TDDReportTime_AfterUpdate()
> > Select Case Me.TDDReportTime
> > Case 1
> > MyStartDate = DateAdd("d", -1, Date)
> > Case 2
> > MyStartDate = DateAdd("d", -2, Date)
> > Case 3
> > MyStartDate = DateAdd("d", -7, Date)
> > Case 4
> > MyStartDate = DateAdd("d", -14, Date)
> > Case 5
> > MyStartDate = DateAdd("m", -1, Date)
> > Case 6
> > MyStartDate = DateAdd("m", -2, Date)
> > Case 7
> > MyStartDate = DateAdd("m", -3, Date)
> > Case 8
> > MyStartDate = #10/1/2004#
> > Case 9
> > MyStartDate = #10/1/2005#
> > Case 10
> > MyStartDate = #10/1/2006#
> > Case 11
> > MyStartDate = #4/15/2006#
> > Case 12
> > MyStartDate = #10/1/2007#
> > End Select
> > End Sub
> >
> > --
> > Wayne
> > Manchester, England.
> >
> >
> >
> > "KarenFio" wrote:
> >
> > > I converted an Access 97 database to 2003 and a form that uses the following
> > > code to choose a start date for a report did not work:
> > >
> > > Private Sub TDDReportTime_AfterUpdate()
> > > Select Case Me.TDDReportTime
> > > Case 1
> > > MyStartDate = DateAdd("d", -1, Date)
> > > Case 2
> > > MyStartDate = DateAdd("d", -2, Date)
> > > Case 3
> > > MyStartDate = DateAdd("d", -7, Date)
> > > Case 4
> > > MyStartDate = DateAdd("d", -14, Date)
> > > Case 5
> > > MyStartDate = DateAdd("m", -1, Date)
> > > Case 6
> > > MyStartDate = DateAdd("m", -2, Date)
> > > Case 7
> > > MyStartDate = DateAdd("m", -3, Date)
> > > Case 8
> > > MyStartDate = #10/1/2004#
> > >
> > > Case 9
> > > MyStartDate = #10/1/2005#
> > > Case 10
> > > MyStartDate = #10/1/2006#
> > > Case 11
> > > MyStartDate = #4/15/2006#
> > > Case 12
> > > MyStartDate = #10/1/2007#
> > >
> > >
> > >
> > > End Select
> > > Me.Requery
> > >
> > >
> > > The text box that should display the date has the message "#Name?".
> > >
> > > Access 2003 keeps crashing with the converted database. I have tried
> > > turning of Name AutoCorrect.
> > >
> > > Any suggstions would be greatly appreciated. Thanks.
> > >

== 5 of 6 ==
Date: Thurs, Feb 28 2008 11:53 am
From: Wayne-I-M

I just found this which may be of some use

http://www.access-programmers.co.uk/forums/archive/index.php/t-93958.html

--
Wayne
Manchester, England.

"KarenFio" wrote:

== 6 of 6 ==
Date: Thurs, Feb 28 2008 11:53 am
From: Klatuu

I think you have a VBA Library Reference problem. When you change version,
the library files the old version was using may be no longer on your system
or they may not work with the current version.

When you change versions, check your references to ensure they are not
missing and that they are using the correct version of the library.
--
Dave Hargis, Microsoft Access MVP

"KarenFio" wrote:

==============================================================================
TOPIC: Printing Sub Reports in Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/c6c09f52dcba93a9?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 12:36 pm
From: dimpie

Both the subreports have data in Print Preview mode. But when i have to
print, then it prints a blank page.

There is no data printer by the printer ever though i see data in the print
prview mode

Thanks!!

"NetworkTrade" wrote:

> you are saying that one of the subreports shows data on screen; but is blank
> when printed?
>
> or are you saying that there is a blank page appended to the end of the
> report when printed?
> --
> NTC
>
>
> "dimpie" wrote:
>
> > Ya i have tried printing on 2 diff printers and also a Pdf writer. Non works.
> >
> > Any ideas Please!!
> >
> > "NetworkTrade" wrote:
> >
> > > are you able to sanity check and compare behavior on a different PC/printer?
> > > --
> > > NTC
> > >
> > >
> > > "dimpie" wrote:
> > >
> > > > Hi - I have 2 sub reports included in one main report. Two sub reports run on
> > > > different queries.
> > > >
> > > > In the Print Preview of the main report, it is fine. But when i try to print
> > > > on a printer, it prints a blank page.
> > > >
> > > > Please HELP!!!!!
> > > >
> > > > Really appreciate your time.
> > > >
> > > > Thanks.

==============================================================================
TOPIC: Using Excel from Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/47873f5aa8db5ce8?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Feb 28 2008 1:01 pm
From: Daniel Pineault

Try the following code,

Function Print_TOC_Chart()
Dim xlApp As Object
Dim xlWrkBk As Object
Dim xlWrkSht As Object

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance
of Excel

If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
'On Error GoTo Err_Handler
Set xlApp = CreateObject("excel.application")
Else
'On Error GoTo Err_Handler
End If

'xlApp.Visible = True 'make excel visible to the user
Set xlWrkBk = xlApp.Workbooks.Open("\\hn-s-fileserv1\sharedmfg\mfg" & _
"'\CFM\Reports\TOC_Reports\Workcenter TOC Reports.xls")

'Call Set_Printer(ExcelApp, Me.ComboBox_prtSelect.Value)

Set xlWrkSht = xlApp.Worksheets("WorkBookSheetName")

With xlWrkSht.PageSetup
.PrintArea = "B2:D4"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.Orientation = xlLandscape
End With

xlWrkSht.PrintOut Copies:=1

ExcelApp.WkBk.Close False
End Function

It work for me, but I did not try the change printer aspect of it. If I
have the time I will look at it and post back with the solution, but this
should get you one step closer to the solution.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.

"john.fuller@gatech.edu" wrote:

> Inside an Access DB I am trying to open up and print a selected range
> from an Excel File (long reason on why I ahve to do it this way, but
> there is no work around). I wrote the following code in Excel (and it
> works great there). I then transferred it into access, added teh
> excelapp object lines, and basically put excelapp in front of every
> command line. This, however, did not work. The 2 places I find it
> crashing now is trying to set the active printer and set the wkbk. It
> will open the file, but won't set the wkbk variable. Also, right now
> the set printer code runs (due to the on error resume next), but it
> doesn't change the printer. I tried giving set
> excelapp.application.activeprinter a good string (as opposed to
> looping through all the possible Ne##), and it still won't set it.
> Any help is appreciated.
>
> Sub Print_TOC_Chart()
> Dim WkBk As Workbook, WkSht As Worksheet, ExcelApp As Object
>
> Set ExcelApp = CreateObject("Excel.Application")
> ExcelApp.Visible = True
>
> Call Set_Printer(ExcelApp, Me.ComboBox_prtSelect.Value)
>
> Set WkBk = ExcelApp.Workbooks.Open("\\hn-s-fileserv1\sharedmfg\mfg
> \CFM\Reports\TOC_Reports\Workcenter TOC Reports.xls", 0)
>
> Set WkSht = ExcelApp.WkBk.Worksheets("Workcenter Charts")
>
> With ExcelApp.WkSht.PageSetup
> .PrintArea = "B112:AW122"
> .Zoom = False
> .FitToPagesTall = 1
> .FitToPagesWide = 1
> .Orientation = xlLandscape
> End With
>
> ExcelApp.curWkSht.PrintOut Copies:=1
>
> ExcelApp.WkBk.Close False
> End Sub
>
> Sub Set_Printer(ExcelApp As Object, PrinterName As String)
> 'On Error Resume Next
>
> For i = 0 To 99
> If i < 10 Then
> ExcelApp.Application.ActivePrinter = PrinterName & " on
> Ne0" & i & ":"
> If ExcelApp.Err.Number = 0 Then Exit Sub
> ExcelApp.Err.Clear
> Else
> ExcelApp.Application.ActivePrinter = PrinterName & " on
> Ne" & i & ":"
> If ExcelApp.Err.Number = 0 Then Exit Sub
> ExcelApp.Err.Clear
> End If
> Next i
> End Sub
>

== 2 of 2 ==
Date: Thurs, Feb 28 2008 1:21 pm
From: Daniel Pineault

It's actually an easy fix, see below.

Function Print_TOC_Chart()
Dim xlApp As Object
Dim xlWrkBk As Object
Dim xlWrkSht As Object

'xlApp.Visible = True 'make excel visible to the user
Set xlWrkBk = xlApp.Workbooks.Open("\\hn-s-fileserv1\sharedmfg\mfg" & _
"'\CFM\Reports\TOC_Reports\Workcenter TOC Reports.xls")

Set xlWrkSht = xlApp.Worksheets("Workcenter Charts")

With xlWrkSht.PageSetup
.PrintArea = "B2:D4"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.Orientation = xlLandscape
End With

xlWrkSht.PrintOut Copies:=1, ActivePrinter:=Me.ComboBox_prtSelect.Value

xlWrkBk.Close False
xlApp.Close

Set xlWrkSht = Nothing
Set xlWrkBk = Nothing
Set xlApp = Nothing

End Function

you can simply add the ActivePrinter parameter to the PrintOut line such as:
xlWrkSht.PrintOut Copies:=1, ActivePrinter:=Me.ComboBox_prtSelect.Value

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.

"john.fuller@gatech.edu" wrote:

==============================================================================
TOPIC: Formatting Text in VBA
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b917bbf2b4e6e9e3?hl=en
==============================================================================

== 1 of 3 ==
Date: Thurs, Feb 28 2008 1:04 pm
From: Sash

I need to reformat a field via VBA. But it's an odd situation.

I have a field where I store code and it may be

E800.0
E801
E8002

ONLY in the cases where it is E8002, I need to reformat it to be E800.2

In the same field I need to strip off leading zeros. Could I do something
like:

If field like 0* then
mid([field],2,10)
End if

The reason for using mid is that the length of the field varies, but is
never more than 10.

== 2 of 3 ==
Date: Thurs, Feb 28 2008 1:37 pm
From: Sash

Okay. I figured out the E problem and stripping off the leading zero, but
how would I strip of a trailing 0 if each field is different lengths? Can
someone help???

"Sash" wrote:

> I need to reformat a field via VBA. But it's an odd situation.
>
> I have a field where I store code and it may be
>
> E800.0
> E801
> E8002
>
> ONLY in the cases where it is E8002, I need to reformat it to be E800.2
>
> In the same field I need to strip off leading zeros. Could I do something
> like:
>
> If field like 0* then
> mid([field],2,10)
> End if
>
> The reason for using mid is that the length of the field varies, but is
> never more than 10.

== 3 of 3 ==
Date: Thurs, Feb 28 2008 3:04 pm
From: Klatuu

This will change E8002 to E800.2:
replace([field],"E8002","E800.2")

As to removing a trailing zero:

If Right([field],1) = "0" Then
[field] = Left([field], Len([field]) -1)
End If

--
Dave Hargis, Microsoft Access MVP

"Sash" wrote:

> Okay. I figured out the E problem and stripping off the leading zero, but
> how would I strip of a trailing 0 if each field is different lengths? Can
> someone help???
>
> "Sash" wrote:
>
> > I need to reformat a field via VBA. But it's an odd situation.
> >
> > I have a field where I store code and it may be
> >
> > E800.0
> > E801
> > E8002
> >
> > ONLY in the cases where it is E8002, I need to reformat it to be E800.2
> >
> > In the same field I need to strip off leading zeros. Could I do something
> > like:
> >
> > If field like 0* then
> > mid([field],2,10)
> > End if
> >
> > The reason for using mid is that the length of the field varies, but is
> > never more than 10.

==============================================================================
TOPIC: Linked Excel File
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/2fa5574cee47ac93?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 2:49 pm
From: Klatuu

I believe it is a timing issue.
It can take a little time for the copy from the pc to the network to take
place. I suspect the when access tries to open the table, the copy process
has not yet completed.
--
Dave Hargis, Microsoft Access MVP

"Weste" wrote:

> I have an MS Access 2003 app that has a linked Excel file. There is a
> process that copies the Excel file from the user's pc to the network, then
> refreshes the link. If this process is run, then a user makes a change in
> the file and runs the copy/refresh process again, the updated file doesn't
> get copied to the network if this is done within about a minute of the first
> copy/refresh. It seems like the app is holding a copy of the old Excel file
> temporarily in memory. I have tried deleting/creating a new link vs. refresh
> and this doesn't fix the problem. I have also killed the file on the network
> before copying the updated file, and the old file still gets copied. If I
> wait approx 1 minute, the updated Excel file gets copied. Is there a way to
> flush the old copy out of memory? Thanks.

==============================================================================
TOPIC: queries
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/31e56ff86c16e9b5?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 2:53 pm
From: Klatuu

You are suffering the first symptom of inaccurate relational database design.
The correct answer is combine both tables and add a field to identify new
and used. Then you wont have this problem.

You could solve the immediate problem with a Union query, but Union queries
are not updatable, so you can't use it for a form record source unless you
want it to be read only.

But, as I said before, you need to redesign your database. This is only the
first of many problems you will have using two tables than have the same data.
--
Dave Hargis, Microsoft Access MVP

"hogan" wrote:

> Let me add a few things. The tables have the same field names other than the
> primary key. I have two seperate tables because my primary keys are
> autonumbers with the format of "DFN"000 for new parts and "DFU"000 for used
> parts.
>
> "hogan" wrote:
>
> > I have two tables in a parts database called "new parts" & "used parts". The
> > primary keys are "used part number" & "new part number". How do I create a
> > query so I can have both used and new parts listed together. If you need
> > additional info please let me know.

==============================================================================
TOPIC: Email attachments
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/31fe0214c298d430?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Feb 28 2008 3:00 pm
From: Daniel Pineault

Sadly, the SendObject method only supports a single filename being passed as
an argument. So you cannot send both reports in 1 email. that said you have
two choices.

1. perform to send objects, 1 per report
DoCmd.SendObject acReport, "Record of Complaint",
"SnapshotFormat(*.snp)", "", "", "", "Consumer Complaint", "", True
DoCmd.SendObject acReport, "AckLetter",
"SnapshotFormat(*.snp)", "", "", "", "Letter", "", True

Also, you do not need to open the report to send the e-mail.

2. automate Outlook to create a mail, with both attachement and fire it off.

You can find an example (need to be modified slightly for your needs) at

http://msdn2.microsoft.com/en-us/library/aa159619(office.11).aspx#odc_ac_olauto_sendanoutlookmessageusingaccess

If you need more help, post back indicating which method you've chosen to
adopt and how I can further assist you.
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.

"DeanT" wrote:

> I want to send email with 2 report attachments. My coding is below. The
> problem I have is this coding only attaches the first report. How do I add in
> the 2nd report into the coding. I only want one email sent with both reports
> attached.
>
>
>
> Function Send_RC()
>
> On Error GoTo Send_RC_Err
>
>
>
> DoCmd.OpenReport "Record of Complaint", acViewPreview, "",
> "[Complaints]![ComplaintNumber]=[Forms]![ComplaintForm]![ComplaintNumber]",
> acNormal
>
> DoCmd.OpenReport "AckLetter", acViewPreview, "",
> "[Complaints]![ComplaintNumber]=[Forms]![ComplaintForm]![ComplaintNumber]",
> acNormal
>
> DoCmd.SendObject acReport, "Record of Complaint",
> "SnapshotFormat(*.snp)", "", "", "", "Consumer Complaint", "", True, ""
>
>
>
> Send_RC_Exit:
>
> Exit Function
>
> Send_RC_Err:
>
> MsgBox Error$
>
> Resume Send_RC_Exit
>
> End Function
>
>
>
> Thanks for any help.
>
>

== 2 of 2 ==
Date: Thurs, Feb 28 2008 3:01 pm
From: Daniel Pineault

Just came across a really good post on the subject with another solution.
Check out

http://groups.google.ca/group/microsoft.public.access.macros/browse_thread/thread/ac7e96df8b3a280e/c60eaa86563f3e19?hl=en&lnk=st&q=vba+sendobject+multiple+attachments#c60eaa86563f3e19
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.

"DeanT" wrote:

==============================================================================
TOPIC: Data import
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/57e28cad12b11e59?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 3:05 pm
From: Daniel Pineault

Why not create a function in Access that would, 1 by 1, read the file and add
the info to the appropriate table fields?

You're going to need to explain your table structure vs. text file content
for us to be able to help design the function.

Might I also ask, why you are proposing going through a txt orExcel first?
The cse file can be open with a standard text editor? if so, then my original
solution stands.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.

"Irina" wrote:

> I have a question:
> I have a folder with 3,700 .cse files, and i need to dump those somehow into
> either a .txt file or excel to import those into Access db later.
> The text of the files looks like this:
> {A41F6C3E-2643-11D2-A02E-00A0FFFF9114}]
> {1}
> 50=Philistin BYSSAINTHE^Arial@9@0@
> 120=500 Fernwood Avenue^Arial@9@0@
> 130=Rochester^Arial@9@0@
> 150=14609^Arial@9@0@
> 340=Philistin BYSSAINTHE^Arial@9@0@
> 180=Y,,
> 190=New York Court of Appeals^Arial@9@1@
> 330=All immigration matters regarding Philistin BYSSAINTHE.^@
> 30=^Arial@9@0@
> 40=^Arial@9@0@
> 80=N,
> 60=N,
> 90=N,
> 70=Y,
> 110=^Arial@9@0@
> 140=^Arial@9@0@
> 210=^Arial@9@0@
> 220=N,,
> 260=Harter Secrest & Emery LLP^Arial@9@1@
> 270=1600 Bausch & Lomb Place^Arial@9@1@
> 280=Rochester, New York 14604^Arial@9@1@
> 290=Margaret A. Catillaz, Esq.^Arial@9@1@
> 320=Margaret A. Catillaz, Esq.^Arial@9@0@
> 300=(716)231-1122^Arial@9@1@
> 310=(716)232-2152^Arial@8@1@
> 200=,,
> 240=,,
> 10=Form I-765 and Related Documents^Arial@9@1@<OVERRIDE>@
> 20=^Arial@9@1@
> {2}
> [{A41F6C41-2643-11D2-A02E-00A0FFFF9114}]
> {1}
> 220=Y,,
> 240=,,
> 260=N,,
> 280=,,
> 360=Harter, Secrest & Emery LLP^Arial@9@1@
> 330=Margaret A. Catillaz, Esq.^Arial@9@1@<OVERRIDE>@
> 370=^@
> 340=^Arial@9@1@
> {2}
>
> So my question is, how do I dump those files into a readable format
> for access automatically - if I do this manually, it would take me months....
> is there some utility that would do such a thing?
> please anyone advise
>
> appreciate your expertise.
>
>

==============================================================================

You received this message because you are subscribed to the Google Groups "microsoft.public.access.modulesdaovba"
group.

To post to this group, visit http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

To unsubscribe from this group, send email to microsoft.public.access.modulesdaovba-unsubscribe@googlegroups.com

To change the way you get mail from this group, visit:
http://groups.google.com/group/microsoft.public.access.modulesdaovba/subscribe?hl=en

To report abuse, send email explaining the problem to abuse@googlegroups.com

==============================================================================
Google Groups: http://groups.google.com?hl=en

Excel for Life

Kamis, 28 Februari 2008

25 new messages in 13 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya