How to provide sample workbook?
 

Like all good posters to this newsgroup I've read Chip's hints and
tips and try to follow them. I want to provide sample data and I
definitely don't want to encourage the posting of workbooks as
attachments.

If, like me, anyone here reads the database newsgroups they will be
familiar with a guy called Joe Celko and his signatu

"Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema are."

Data Definition Language (DDL) statements (CREATE TABLE..., INSERT
INTO..., etc) allow the reader to actually create the poster's
database simply by pasting the DLL into their database system and
pressing a button.

Recently, I was asked in this newsgroup for an example workbook and,
rather than send them an email and take the discussion outside the
group, I posted the following instructions on how to create my example
workbook:

"Open a new workbook, insert a standard module and paste in the
following sub procedu

Public Sub CreateDB()

Dim ws As Excel.Worksheet

Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Database").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Set ws = ThisWorkbook.Worksheets.Add

With ws

ws.Name = "Database"

.Range("A1:C1").Value = Array("RefID", "Surname", "Balance")

.Range("A2:A11").Value = Application.Transpose(Array(1, _
2, 3, 4, 5, 6, 7, 8, 9, 10))
.Range("B2:B11").Value = Application.Transpose(Array("Nan", _
"Wet", "Ren", "Hip", _
"Nat", "Ter", "Doh", _
"Leh", "Rot", "Ton"))
.Range("C2:C11").Value = Application.Transpose(Array(Int(Rnd *
99), _
Int(Rnd * 99), Int(Rnd * 99), _
Int(Rnd * 99), Int(Rnd * 99), _
Int(Rnd * 99), Int(Rnd * 99), _
Int(Rnd * 99), Int(Rnd * 99), _
Int(Rnd * 99)))


End With

ThisWorkbook.Names.Add "Data", "=Database!$A$2:$C$11"

End Sub

And it struck me that I've never seen this kind of thing in this
newsgroup before. Usually, sample data is given in the text of post,
like this:

RefID Surname
----- -------
1 Nan
2 Wet
3 Ren
...

Even if it survives the various formatting it undergoes during the
posting process (e.g. via Google groups or Developer Network) I'm
rarely able to copy and paste it into a workbook.

I'm sure I'd be more willing to help someone if they provided me with
a quick and easy method to re-create their data, formulas, defined
Names, etc. And when I say easy, I essentially mean copy+paste and
press a button.

What do people here think? Are you frustrated with the way sample data
and workbooks are provided in this newsgroup? If you saw my CreateDB
routine in a posting, would you appreciate it as a short cut to seeing
my sample workbook? Or would you possibly dismiss it, for example
because it makes my posting look *more* complicated rather than
simplifying things?

I'd appreciate any views you may have.

How to provide sample workbook?
 

Personally I'm not militant about attachments. For one thing I set Outlook
Express to color code large messages so it's easy to see the ones with
attachments, and I have broadband at work and at home. But I know others
operate differently. Someday I like to think, with broadband becoming for
prevalent, the "prohibition" could be lifted.

Anyway, most "sample data" is simple enough that I don't mind a little
typing. I think I would be put off by a macro that dumped the data; might
take me longer to check the macro than type the data. And from the poster's
side, creating the data dumping macro is a lot more trouble.


--
Jim Rech
Excel MVP

How to provide sample workbook?
 

JohnI

Well I learnt something from your post! Thanks. Didn't know about Transpose
for Array like that, fantastic!

Funny, I've never used Transpose for anything else!

When getting data from the Newsgroup, I paste it into a column & use-

- Data - "Text To Columns" - selecting Delimited - By Spaces to split it
into columns.

Yeah, I do the same. But more often than not it doesn't quite line up:
the text wraps to a new line, blank rows get introduced, the poster
used the wrong kind of font etc. And it got to the point where I wrote
my own *array* version of the Substitute function so I could replace
many kinds of non-printing characters in one hit!

"JohnI" wrote in message ...
onedaywhen,

Well I learnt something from your post! Thanks. Didn't know about Transpose
for Array like that, fantastic!

When getting data from the Newsgroup, I paste it into a column & use-

- Data - "Text To Columns" - selecting Delimited - By Spaces to split it
into columns.

regards,

JohnI


"onedaywhen" wrote in message
om...
Like all good posters to this newsgroup I've read Chip's hints and
tips and try to follow them. I want to provide sample data and I
definitely don't want to encourage the posting of workbooks as
attachments.

If, like me, anyone here reads the database newsgroups they will be
familiar with a guy called Joe Celko and his signatu

"Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema are."

Data Definition Language (DDL) statements (CREATE TABLE..., INSERT
INTO..., etc) allow the reader to actually create the poster's
database simply by pasting the DLL into their database system and
pressing a button.

Recently, I was asked in this newsgroup for an example workbook and,
rather than send them an email and take the discussion outside the
group, I posted the following instructions on how to create my example
workbook:

"Open a new workbook, insert a standard module and paste in the
following sub procedu

Public Sub CreateDB()

Dim ws As Excel.Worksheet

Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Database").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Set ws = ThisWorkbook.Worksheets.Add

With ws

ws.Name = "Database"

.Range("A1:C1").Value = Array("RefID", "Surname", "Balance")

.Range("A2:A11").Value = Application.Transpose(Array(1, _
2, 3, 4, 5, 6, 7, 8, 9, 10))
.Range("B2:B11").Value = Application.Transpose(Array("Nan", _
"Wet", "Ren", "Hip", _
"Nat", "Ter", "Doh", _
"Leh", "Rot", "Ton"))
.Range("C2:C11").Value = Application.Transpose(Array(Int(Rnd *
99), _
Int(Rnd * 99), Int(Rnd * 99), _
Int(Rnd * 99), Int(Rnd * 99), _
Int(Rnd * 99), Int(Rnd * 99), _
Int(Rnd * 99), Int(Rnd * 99), _
Int(Rnd * 99)))


End With

ThisWorkbook.Names.Add "Data", "=Database!$A$2:$C$11"

End Sub

And it struck me that I've never seen this kind of thing in this
newsgroup before. Usually, sample data is given in the text of post,
like this:

RefID Surname
----- -------
1 Nan
2 Wet
3 Ren
...

Even if it survives the various formatting it undergoes during the
posting process (e.g. via Google groups or Developer Network) I'm
rarely able to copy and paste it into a workbook.

I'm sure I'd be more willing to help someone if they provided me with
a quick and easy method to re-create their data, formulas, defined
Names, etc. And when I say easy, I essentially mean copy+paste and
press a button.

What do people here think? Are you frustrated with the way sample data
and workbooks are provided in this newsgroup? If you saw my CreateDB
routine in a posting, would you appreciate it as a short cut to seeing
my sample workbook? Or would you possibly dismiss it, for example
because it makes my posting look *more* complicated rather than
simplifying things?

I'd appreciate any views you may have.

How to provide sample workbook?
 

Jim,

Personally I'm not militant about attachments. For one thing I set Outlook
Express to color code large messages so it's easy to see the ones with
attachments, and I have broadband at work and at home. But I know others
operate differently. Someday I like to think, with broadband becoming for
prevalent, the "prohibition" could be lifted.

You have a point but I'd want there to be a specific 'attachments'
group so that I could opt out. I line in a rural area in England and
broadband will be unavailable for some time yet. And I prefer to read
and post from Google groups so attachments are meaningless in that
context.

Anyway, most "sample data" is simple enough that I don't mind a little
typing.

I usually do just a brief description myself. For example, in this
case I originally said:

"For example, my range is A1:C10 and I want to delete the
row which has the value 'Pies' in the first column, so
if A4="Pies" I want to delete A4:C4 by shifting cells
A5:C10 up"

I thought this was enough - after all, the actual values could be
anything - but the only reply I got asked me to send a workbook in a
personal email. (I still haven't got a reply from that one, must
follow it up...)

I think I would be put off by a macro that dumped the data; might
take me longer to check the macro than type the data.

That's what I feared. My suggestion was for the reader to create the
workbook and manually run the macro. I thought that a macro that
created a workbook etc would look 'suspicious'.

And from the poster's
side, creating the data dumping macro is a lot more trouble.

Yes, many posters to this group wouldn't have the ability to do it
(that's why they're here!) But because I can, I feel I should do all I
can to help people help me. What's ten minutes coding if I can get an
answer?

If anyone can give me suggestions on what a data dumping macro should
look like I'd be grateful to have them.

"Jim Rech" wrote in message ...

Personally I'm not militant about attachments. For one thing I set Outlook
Express to color code large messages so it's easy to see the ones with
attachments, and I have broadband at work and at home. But I know others
operate differently. Someday I like to think, with broadband becoming for
prevalent, the "prohibition" could be lifted.

Anyway, most "sample data" is simple enough that I don't mind a little
typing. I think I would be put off by a macro that dumped the data; might
take me longer to check the macro than type the data. And from the poster's
side, creating the data dumping macro is a lot more trouble.