mail merging 2 workbooks???

[Bubba Gump]

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 mail 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

 

[Bubba Gump]

Anybody????? I really need help with this.

 

[Dave Peterson]

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

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 mail 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

 

[Bubba Gump]

Dave, thanks for all of your help. Let me clarify. The code you put down
towards the bottom of your post: is that macro code then. I've always though
a macro to be hit record, do your key strokes, hit stop and there's your
macro. Also, so are you saying I can use my existing excel data file, and my
other existing excel worksheet along with a macro and create the merge? If
so, does this merge them to a printer or can it merge them to one big
multi-page new file like a word mail merge does?

Thanks again Dave!
Buster

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

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 mail 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

 

[Dave Peterson]

It plops the values from the input sheet into the form sheet. Then prints that
form sheet--actually, it does a print preview (nice for testing).

Dave, thanks for all of your help. Let me clarify. The code you put down
towards the bottom of your post: is that macro code then. I've always though
a macro to be hit record, do your key strokes, hit stop and there's your
macro. Also, so are you saying I can use my existing excel data file, and my
other existing excel worksheet along with a macro and create the merge? If
so, does this merge them to a printer or can it merge them to one big
multi-page new file like a word mail merge does?

Thanks again Dave!
Buster

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

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 mail 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

 

[Bubba Gump]

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).

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

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 mail 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

 

[Dave Peterson]

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).

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

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 mail 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

 

[Bubba Gump]

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).

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 mail
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

 

[Dave Peterson]

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?

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).

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 mail
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

 

[Bubba Gump]

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?

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).

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 mail
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

 

[Dave Peterson]

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")

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?

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).

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 mail
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

 

[Bubba Gump]

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")

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).

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
mail
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

 

[Bubba Gump]

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")

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).

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
mail
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

 

[Dave Peterson]

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.)

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).

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
mail
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

 

[Bubba Gump]

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.)

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).

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
mail
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

 

[Dave Peterson]

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.

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.)

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).

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
mail
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

 

[Bubba Gump]

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.

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).

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
mail
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

 

[Dave Peterson]

There's something in the code that empties it after the code runs--so you won't
double print.

You can put anything you want in that column. I'd use X's.

And typing has always worked ok for me.

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.

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).

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
mail
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

 

[Dave Peterson]

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.

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.

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).

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
mail
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

 

[Bubba Gump]

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.

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.

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
mail
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