D
Dave Peterson
Open the workbook with the code.
Open the VBE and click anywhere in that subroutine.
Hit F8 to step through each line of the code.
Maybe something will pop up that needs fixing.
Open the VBE and click anywhere in that subroutine.
Hit F8 to step through each line of the code.
Maybe something will pop up that needs fixing.
Bubba said:crap, I'm just at a loss. I filled column A with x's. ran the macro, it opsn
the data file and sits there. so, i changed the line below to "If true
then", ran the macro, it opens the data file and sits there. this is making
me feel much more stupid than I thought I was.
I'm expecting that after the macro gets done, it will open a print preview
window. Isn't that correct? I have this saved in the vb editor with the
form's xls file open, module 1 under modules. That's the only module in
there. As a matter of fact, I just copied it to sheet1 and re-ran that one
to see if it made a difference but it didn't.
I don't know where to go from here.
Dave Peterson said:Ps. If you don't want that check to be done, change this line:
If IsEmpty(.Offset(0, -1)) Then
to
If True then
Then the false portion will always be done--and that's the part that does
the
work.
Bubba said:ok, well column A is empty. how do I get something in it and what needs
to
be in it? Just numbers or what? I thought I remembered something in your
code that actually put something in column A.
If you didn't get any print preview screens, then something bad
happened.
Remember that it looks at column A to find an indicator to print the
data
on
that row. If you have anything in that cell, then that row is used to
populate
the form--if all of column A is empty, then you're not asking for
anything
to be
populated.
Bubba Gump wrote:
ok, to answer your question, yes it is always the first sheet but it
looks
like you went ahead and included that in this latest version of the
code.
I
ran the macro and got no errors, but at the same time, it didn't seem
to
do
anything. I have the form file open, did alt-f8 and selected the
testme
macro and ran it. It opened up the data file and then i couldn't tell
that
anything else happened. Am I missing something?
Thanks!
Buster
When you do this stuff, is there only one worksheet in each of those
workbooks?
If there's only one worksheet, you can just use the first (and only)
worksheet
in the workbook and avoid having to use the names.
Option Explicit
Option Base 0
Sub testme()
Dim WkbkF As Workbook
Dim WkbkD As Workbook
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim WkbkFName As String
Dim WkbkDName As String
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant
WkbkFName = "Test0607Returning.xls"
Set WkbkF = Nothing
On Error Resume Next
Set WkbkF = Workbooks(WkbkFName)
On Error GoTo 0
If WkbkF Is Nothing Then
'it's not open
Set WkbkF = Workbooks.Open _
(Filename:="G:\FinPlan\Reports\Automated Awd
Wkshts\" _
& WkbkFName)
End If
Set FormWks = WkbkF.Worksheets(1)
WkbkDName = "c07ret_07a.xls"
Set WkbkD = Nothing
On Error Resume Next
Set WkbkD = Workbooks(WkbkDName)
On Error GoTo 0
If WkbkD Is Nothing Then
'it's not open
Set WkbkD = Workbooks.Open _
(Filename:="G:\FinPlan\Reports\Automated Awd
Wkshts\" _
& WkbkDName)
End If
Set DataWks = WkbkD.Worksheets(1)
myAddresses = Array("j4", "d4", "d10", "d11", "d7", "i16", _
"i19", "i21", "i27")
With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With
For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub
I changed the wkbkF and wkbkG names to wkbkF and wkbkD (F=Form,
D=data)
to
make
it easier to know which represents which. Make sure I did the
change
correctly!
And if I can always use the first workbook (left most when you're
looking
at it
in excel), you can use:
Set FormWks = WkbkF.Worksheets(1)
instead of specifying the name.
When you get that error message:
error '91': Object variable or With block variable not set.
on this line:
with DataWks
It's because DataWks hasn't be initialized correctly. It could be
because
of a
few things that may have been fixed (now) (worksheets(1) may have
fixed
it).
And just think how dangerous you're becoming learning all this
stuff.
Remember to save often!
(and get rid of that Preview:=true when you're ready for paper.)
Bubba Gump wrote:
I found one problem with this in looking back at it but it didn't
fix
the
problem. After the endif following "Set wkbkF", i forgot to include
the
line
"Set FormWks = wkbkF.Worksheets("Sheet1")".
In case you notice on the line of mine that has the worksheet name
as
c07ret_07a, FoxPro names the sheet the same thing as the file when
it
exports the file to Excel.
ok, this is what I have and it bombs on the set myRng line.
"Run-time
error '91': Object variable or With block variable not set." Man
you're
gonna wish this thread had never surfaced.
Option Explicit
Option Base 0
Sub testme()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant
Dim wkbkF As Workbook
Set wkbkF = Nothing
On Error Resume Next
Set wkbkF = Workbooks("Test0607Returning.xls")
On Error GoTo 0
If wkbkF Is Nothing Then
'it's not open
Set wkbkF =
Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\Test0607Returning.xls")
End If
Dim wkbkG As Workbook
Set wkbkG = Nothing
On Error Resume Next
Set wkbkG = Workbooks("c07ret_07a.xls")
On Error GoTo 0
If wkbkG Is Nothing Then
'it's not open
Set wkbkG =
Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\c07ret_07a.xls")
End If
Set FormWks = wkbkG.Worksheets("c07ret_07a")
myAddresses = Array("j4", "d4", "d10", "d11", "d7", "i16",
"i19",
"i21", "i27")
With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count,
"B").End(xlUp))
End With
For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the next time
For iCtr = LBound(myAddresses) To
UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub
If the workbook isn't open, then excel can't find the stuff it
needs.
Since you could have lots of different workbooks that are named
testform.xls (in
lots of different folders, in different shared drives, on
different
CDs,
on
different USB devices, ....) excel wouldn't know which one to
open.
If you want to have excel open your workbooks, you can do:
Set FormWks _
= workbooks.open(filename:="C:\my
documents\excel\testform.xls") _
.Worksheets("sheet1")
But then this expects the workbook to be closed!
You could have the code check.
dim wkbkF as workbook
set wkbkF = nothing
on error resume next
set wkbkf = workbooks("TestForm.xls")
on error goto 0
if wkbkf is nothing then
'it's not open
set wkbkf = workbooks.open(filename:="C:\my
documents\excel\testform.xls")
end if
set formwks = wkbkf.worksheets("Sheet1")
Bubba Gump wrote:
well "no rhyme or reason" is inaccurate in that the data is
arranged
with
data similar in nature. However, it's not just sitting in
straight
columns/rows and there are merged cells all over hell and
creation
depending
on the size of the element that is in that position. does that
make
sense?
I'm gonna play with this this afternoon and see what I get. you
say
the
workbooks have to already be open? why is that? will it not
open
them
if
they're in the same folder? In other words, if i open the form
workbook
and
run the macro, will it not open the data file if it resides in
the
same
folder as the form file? if not, is there a macro comman that
will
open
it?
Thanks dave!
You can be specific about which workbook contains the
worksheet.
Set FormWks = workbooks("testform.xls").Worksheets("sheet1")
Set DataWks =
workbooks("anothernamehere.xls").Worksheets("data")
or
Set DataWks = activeworkbook.Worksheets("data")
(the workbook that is active in excel's window)
or
Set DataWks = ThisWorkbook.Worksheets("data")
(the workbook that actually holds the code)
But whatever workbooks you use have to be already open for
this
to
work.
=======
Take a look at that code once more.
Dim FormWks As Worksheet
Dim DataWks As Worksheet
...
Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")
FormWks ans DataWks are each a worksheet.
But when I do the assigment, I want to look at all the
worksheets
and
use
the
one named "Data" or "form".
(I didn't see any missing/extra s's in there.)
And yep with the addresses. I figured that the layout of the
form
would
be kind
of "free-format". You'd be plopping information all over the
worksheet--with no
rhyme or reason--except that it looks nice that way.
Was I wrong?
Bubba Gump wrote:
Hmmm, I'm creating the xls file from FoxPro using a "save
as"
command. My
only option is type xls or type xl5, or xl8. The only
difference
is
the
version number of excel it's exporting as. XL8 is Excel. XL8
is
excel
5.0
and 97. XLS is everything prior. What I'm getting at is i
don't
know
how
to
make it do a worksheet rather than a book or even if i can.
can I in those quotes, name the book and then the sheet
within
in
it
like
("[testform.xls]sheet1")?
ok, as far as the array addresses, I think I understand now.
so
since
I
have
about 60 blanks to fill in on my form, I will have to have
all
of
those
60
cell addresses referenced here, and in the correct order as
in
the
data
file?
Another question about your original code. I notice at the
top
you
referenced "Dim FormWks As Worksheet" but a few lines down
where
you
define
Set FormWks, it says "Worksheet" without the S on the end.
Is
this
correct
or a typo?
Thanks again Dave!
Buster
The FromWks variable represents a worksheet--not a
workbook.
So use
set formwks =
worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks =
worksheets("whatevertheworksheetnameisthatholdsthedatahere")
I don't know where you're copying the data from the data
worksheet
into
the form
worksheet.
change that line:
myAddresses = Array("b2", "b3", "g6", "F12")
to what you want populated in the Form worksheet.
Remember for each row in the Data worksheet, the value in
column
B
will
go
to
the first address, the value in column c will go to the
second
address,
and so
forth.
This is a text only newsgroup. It's better to post in
plain
text
(not
HTML) and
no attachments, too.
Bubba Gump wrote:
I don't understand this part:
You'll want to change these lines:
Set FormWks = Worksheets("testform.xls") I get an
error
here
saying "Runtime error 9, subscript out of range"
Set DataWks = Worksheets("testdata.xls")
myAddresses = Array("b2", "b3", "g6", "F12")
To match your workbook.
I don't understand this part either. Why do the cells
jump
alla
around
to
B3,
G6, and F12? I created an empty column A. What do I need
to
do
with
that
column A?
Since I used column A as the indicator, this will put
the
value in
column
B in
B2, column C in B3, D in G6 and E in F12. Just keep
adding
more
addresses--but
go in that natural order (left to right starting in
column
B).
message
Saved from a previous post:
First, you could have used your excel table as the
source
and
created
the
form
in MSWord (where you might have been able to make a
nicer
form???).
You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge
The first is from David McRitchie and the second is by
Beth
Melton
and
Dave
Rado.
======
But you can do what you want in excel (since your form
is
done).
But one thing I would do is add a new column A to your
data.
Use
that
as an
indicator that this row should be printed--if something
happens
and
you
have
to
reprint a few, there's no sense printing all the forms.
This is the code I used:
Option Explicit
Option Base 0
Sub testme()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant
Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")
myAddresses = Array("b2", "c3", "d6", "F12")
With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count,
"B").End(xlUp))
End With
For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the
next
time
For iCtr = LBound(myAddresses) To
UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value
_
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub
You'll want to change these lines:
Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")
myAddresses = Array("b2", "b3", "g6", "F12")
To match your workbook.
Since I used column A as the indicator, this will put
the
value
in
column B
in
B2, column C in B3, D in G6 and E in F12. Just keep
adding
more
addresses--but
go in that natural order (left to right starting in
column
B).
If you're new to macros, you may want to read David
McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Bubba Gump wrote:
ok, my title may not be accurate but I wasn't quite
sure
what
else
to
call
it. I have a workbook created that is a page layout of
a
bunch
of
different
fields in various locations that will print on a
single
sheet
of
paper. I
work in a campus records department and this sheet
(we'll
call
it
workbook1)
will be used for our counselors to manually type
student
data
into
the
blank
provided beside the field label. I have another excel
file
(we'll
call
it
workbook2) that actually has the data in it that the
counselors
need
on
workbook1. Workbook2 will include anywhere from 50-300
student
records
that
are about 50 fields of data each. Now I know how to
doa
fields
reference in
workbook1to tell it to grab data from a certain field
in
workbook2
(=[workbook2.xls]sheet1!A1). However, is there a way
to
do a
merge of
sorts so that it would do this for each and every
record
in
workbook2
so
that if workbook2 has 50 records, it provides 50 page
with
unique
data
to
that record?
Thanks!
Buster