Unasked Question is the Dumb Question

[PerryNZ]

It’s too long since I did this sort of thing. And the program is
no longer available. Can I make VB6 do this for me, within MS
Word for Windows. (Office 2000 on w2kp)

Roughly speaking, I’m seeking to extract data from a data source,
more or less like the following:

Bracketed words signify <field name>

IF <keyword> contains (the word) 'redundant' [true response]
skip to next record (and repeat that test [for every record!])

Should that above conditional test return a [false response]
then, extract the data from the record, thus:

Insert <name1> data in bold, followed by a tab, then move
to the next field.

IF <name2> contains data, insert that data, followed by a
comma and a space, ELSE IF <name2> contains no data,
skip to next field.

. . . . . . . . .

If <home_tel> contains data, insert the text string "Home
Tel:" in bold, then the field data, followed by a comma and a
space, ELSE IF <home_tel> contains no data, skip to next
field.

On and on and on . . . . till the end-of-record, [where two
carriage returns are inserted] then move to the next record
until end of file. (Ye olde WHILE WEND instruction?)

The desired outcome of this exercise is a WP file printed on
A5, (or two columns on A4 landscape) for inclusion in a
small diary/ring-binder.

The data source has 26-30 fields per record and less than
1k records.

Perry

[si_the_geek]

Thread moved to Office Development forum
(Despite the slightly misleading "About" dialog, the VB editor inside Office applications is actually VBA rather than VB)

[PerryNZ]

Hello PerryNZ,

si_the_geek has just replied to a thread you have subscribed to entitled - Unasked Question is the Dumb Question - in the Office Development forum of VBForums.

This thread is located at:
http://www.vbforums.com/showthread.p...4&goto=newpost

There may be other replies also, but you will not receive any more notifications until you visit the forum again.

Yours,
VBForums team

Si,

The FAQs can help, but I could not find an answer to
the basic (pun OK?) question. An example is the use
of conditional instructions on a per field basis.

My efforts so far indicate that such is not an option.
At least, not as far a Windoze HELP is concerned.
Your link to RobDog888's tutorial didn't seem to change
that. Well, not much - as I saw it.

I don't mind doing the 'hard yards,' but I don't want
to waste time going down a path that leads to some
dead end or other.

When I did this sort of stuff, years ago, there were
odd-ball things available. They were called 'manuals.'
They had examples. They were (in the main) helpful.
So I kept reminding myself at 0300, as I repeatedly
tested a complex mail merge document, replete with
conditional tests and paragraphs!

Back then, I was using CP/M+ on an 8 bit processor
running at 4Hz and no hard drive! (And a painfully
small and slow RAM drive, until I installed a daughter
board with extra RAM.)

Part of my question included my 'desired outcome.'
I don't expect anyone to do the job for me. Just tell
me: yes, it is possible - here are some tips to help
you through the changes since yesteryear . . .

Or

No, it isn't possible with VB - try this (xxxx) instead.

So - for the time being - I'm seeking the benefit of
others' experience.

[westconn1]

yes you can do
you can use ADO to connect to a data source
you can use sql to return a list based on select <fileds> where keyword compares to somevalue

you don't specify what data source you have (or want to use), but an access (or other database) should do what you want

si the geek has a tutorial for connecting to database
if you have an existing data source, that is text based then you should consider other method, of working with string arrays

[PerryNZ]

The original data was in an aged DOS WP program (Script*)
that had a really good little 'pop-up' database embedded. I've
extracted it all into a 'modern' spreadsheet. I could put it
into a database, but I'm also struggling to find a reason for
doing that for such a basic process, as well as struggling
to find a simple, but fully user-defined fields, flat-file DB.

There's a thousand and one of such types around, but not
one with a fully user-defined fields option. At least, not
that I could see that would be suitable for home use.
Cardbox is a bit of an overkill, in every way, even though
I used the CP/M+ version, years ago. Far too expensive,
as well.

I was tempted to persist with Locofile, but at a recent OS
re-install, it became apparent that the original 3.5" program
master disc 1 had died. I still have the program and
its associated datafile running on the old hard drive in an
alternate PC.

Being DOS, I hoped that I could make it work on the new
PC, by simply moving it across, but so far - no luck. The
pif file may be the problem. Besides, I suppose I can't go
on like this, for ever.

But, if you say that the idea I hope for is workable, then
I will persist with my efforts. I presume your comments
apply to older versions of VBA, like VBv6 on Office 2k?

Thanks for the reply.

Perry

* Locomotive Software

[westconn1]

the is differences between using VB6 and VBA as with word excel etc
as the data is already in excel, there is no reason that you can't do all you want within the excel program, with code to automate the process

many times the best way is to record a macro of a specific task, this will generate some sample code, there are many examples on this forum for doing near anything you want

[PerryNZ]

as the data is already in excel, there is no reason
that you can't do all you want within the excel
program, with code to automate the process

there are many examples on this forum for
doing near anything you want.

Can you point me to one that you know of that's
as close to specific to my needs as you think I'll
get - as a starter?

[westconn1]

in excel you can use the find function to locate a specific value within a range (column or row)

dim found as range
set found = range("a:a").find somekeyvalue  'search for key value in column A
if found is nothing then exit sub  ' key not found exit code
' key found continue
'do something based on the location of the found key

this may not be the best way to do what you want, but it has possibilities
possibly you need a userform for input and result display
your discription of what you want to do is a bit difficult to follow, but once you get started you will be able to do what ever you want

[BManke]

If you want to do it SQL style, you can stick to the Excel spreadsheet and use it like a DB...if you dont do this often and performance or data security are not of the issue...

You need to connect to your "Excel DB" and then you can run all SQL queries you like.

The query below should be pretty close to what you want to do.

Where would your data be and what table to query? On your spreadsheet select the relevant data and give that range a name. Tada: your table name.

http://www.connectionstrings.com/?carrier=excel2007
http://www.connectionstrings.com/?carrier=excel

To connect:

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst as ADODB.recordset
Dim strQry As String


Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cnn.Open

strQry = "SELECT * FROM myDataRange WHERE myField Like '*ABC*'

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = strQry
Set rst = New ADODB.Recordset
Set rst = cmd.Execute

'manipulate write or do whatever to your data here

rst.close
set rst = nothing
set cmd = nothing
set cnn = nothing

[PerryNZ]

your description of what you want to do is a bit difficult to follow,
but once you get started you will be able to do what ever you want

Should I try again? I don't mind making a second attempt.
If so, what sort of things or descriptions would make
comprehension easier?

[westconn1]

start designing your form etc, then post specific questions as you get to them

see the office development faqs (first post in this forum), while we are happy to help you with problems, we can not design your program, either interface or structure, you have been given several suggestions on methods available, you need to research the options then make a start

[PerryNZ]

start designing your form etc, then post specific questions as you get to them

see the office development faqs (first post in this forum), while we are happy to help you with problems, we can not design your program, either interface or structure, you have been given several suggestions on methods available, you need to research the options then make a start

OK, I'll see how I get on. Part of my problem (indeed, the larger part)
is that I don't even - as yet - grasp the jargon. I need to find a very
bottom-level, basic primer to do that.

I also note that many of the helpful examples are for later versions of
the s/ware than I'm using.

[si_the_geek]

I've just had a quick look, and it seems that most of the articles in the Office Development FAQs would work for you.. only the ones about VB.Net and C# are definitely not right, but some of the VB6 ones might be.


In terms of writing code to work with Excel, this suggestion is extremely good (and used by most of us):

many times the best way is to record a macro of a specific task, this will generate some sample code, there are many examples on this forum for doing near anything you want

After you have recorded the macro, you can go into the VB Editor and see what the code is - which you can then copy into your code as appropriate.

[BManke]

Perry: You have your data in an Excel spreadsheet? In this case I would suggest sticking to Excel as your 'development environment'. Hit alt F11, add a module, write your code.

You can extract the data from one sheet and write it to another easily.

Once you have it there you should be done, if I got you correctly. From there you can format, reformat and print.

[westconn1]

don't worry about the finished product at this stage, just start doing stuff, the more you do, you will figure how your code should work

try doing things one step at a time, then later you can combine the steps, with improvements

[PerryNZ]

As I described, part of my problem is the jargon. Despite the best
efforts and intentions of respondents to this thread, I have - in no
small measure - no idea what the connection is between some of
the items being described.

Yes, I know what a macro is. But what would I be recording in it?

Sure, I can manage an excel conditional instruction, but that's just
to return a value or string in a cell after a test. I don't see how I can
write something like that, such that a lot of data is extracted,
formatted and pasted into a Word document.

In other words, I can't "design a form" or "add a module" (I think)
because I have no idea what those terms mean, nor where to begin
such a project. [ALT+F11] excepted! Helpful as I'm sure it's
intended to be, "the best way is to record a macro of a specific
task," encounters a similar problem. I have no idea what
keystrokes I'd be using to do what I want, so my macro is going to
be empty. I know the end-product task/outcome I want to achieve,
but that's about all, so far.

Yes, everything is in a spreadsheet. Columns headers are
<fieldnames> and rows are data. (Except row one, which contains
the column fieldname/header.)

Si, thanks for checking the compatibility issues with some of the
examples. This is a vexing enough exercise, without having
version conflicts thrown in, too.

Now I don't ask for anyone to lay a keyboard of gold at my feet,
but, at the end of this post, I've added a bit to show the sort of code
I was doing 15+ years ago. I'm sure it made sense – then!? One
reason for pasting it in here is to – I hope – demonstrate that I don't
mind giving it a go. I just need some hand-holding to the start.

Perhaps the stark reality is that this is really a forum for those who
have some knowledge and need help with hiccups. I'm truly an
ignorant newbie – starting from scratch.

Sample for yesteryear (Yes, I can still just understand it – but only just.)

(DaTe:Day) (DaTe:D#) (Date:month) DaTe:Year)
(+mail)
tab = " "
null = ""
cr = "
"
stop = ". "
banker = "Bank: "
bustel = "Bus Tel: "
busfax = "Bus Fax: "
buselmail = "Bus E-mail: "
buscell = "Bus Cell: "
hometel = "Home Tel: "
homefax = "Home Fax: "
homeelmail = "Home E-mail: "
homecell = "Home Cell: "
web = "www: "
see = "See: "
bkt1 = "("
bkt2 = ")"
space = " "
colon = ": "
comma = ", "
comma2 = ", "
$ = "name_1"
listing ="(+mail)
# keyword <> "*redundant*"
<
# name_2 <> null:<name_1:tab:name_2:colon:><name_1:tab:>
# salutation <> null:<:salutation:stop:>
# cross_reference <> null :<:bkt1:see:cross_reference:bkt2:stop:>
# add1 <> null:<:add1:comma2:>
# add2 <> null:<:add2:comma2:>
# add3 <> null:<:add3:comma2:>
# place <> null:<:place:stop:>
# home_tel <> null:<:hometel:home_tel:comma2:>
# home_fax <> null:<:homefax:home_fax:comma2:>
# home_email <> null:<:homeelmail:home_email:stop:>
# home_cell <> null:<:homecell:home_cell:stop:>
# organisation <> null:<:organisation:comma2:>
# org_add1 <> null:<:org_add1:comma2:>
# org_add2 <> null:<:org_add2:comma2:>
# org_add3 <> null:<:org_add3:comma2:>
# org_place <> null:<:org_place:stop:>
# bus_tel <> null:<:bustel:bus_tel:comma2:>
# bus_fax <> null:<:busfax:bus_fax:comma2:>
# bus_email <> null:<:buselmail:bus_email:stop:>
# bus_cell <> null:<:buscell:bus_cell:stop:>
# WWW <> null:<:web:WWW:stop:>
# keyword <> "p":<:keyword:stop:>
# bank <> null:<:banker:bank:stop:>
# comments <> null:<:comments:stop:>
cr
>
$+
(-mail)"(-mail)(+mail)%listing@name_1(-mail)

There a few non-printing characters in that, but you'll get the
general idea, nonetheless.

Lastly, if I'm right that a rank newbie should be getting help
somewhere else, can someone point me to that place, please?

[BManke]

you can get help here....we will work something out. might take some time but dont worry. most ppl here know a bit more, that's why you received some higher level answers. being a newbie does not excude u from getting help.

Yes, everything is in a spreadsheet. Columns headers are
<fieldnames> and rows are data. (Except row one, which contains
the column fieldname/header.)

good...we can work from here if you like. step by step

make a copy of the file.

start by selecting all data including the header and give this selection a name (if you dont know how, dont hesitate to ask) - I suggest you call it SourceRange

copy your header to a second sheet in the same workbook. Select an area as big as the sourcerange including the header and call it targetrange.

try if you can figure out how to add a module to write code and tell me what worked and what didnt - then we will continue - if you like.

regards

BManke

[PerryNZ]

you can get help here....we will work something out. might take some time but dont worry. most ppl here know a bit more, that's why you received some higher level answers. being a newbie does not excude u from getting help.

good...we can work from here if you like. step by step

make a copy of the file.

start by selecting all data including the header and give this selection a name (if you dont know how, dont hesitate to ask) - I suggest you call it SourceRange

copy your header to a second sheet in the same workbook. Select an area as big as the sourcerange including the header and call it targetrange.

try if you can figure out how to add a module to write code and tell me what worked and what didnt - then we will continue - if you like.

Right - thanks. I appreciate the hand held out.
Here's as far as I could get, following your
directions. I have no idea what code to
write, nor even what keypresses are apt
for any code/instructions.



That's the copied sheet, in the copied xls file,
(with the named range "TargetRange"), with
the module window in front.

I've been looking around the www and found
some sites more focussed on VBA. Not many:
most seem to relate to VB6 as a standalone.

I'll work at this exercise and read through the
VBA sites, as I can.

[BManke]

Here as little example to get yourself familiar with the coding procedure, before we solve your original question:


in this empty module window write

option explicit

(this will force you to declare variables which is a good idea because it helps debugging a lot)

then write

sub test

and hit enter

result should be:

sub test()
end sub


between those statements write ( or copy paste from here )

'\\Purpose: learning some basics
dim intC1 as Integer
dim intUB1 as Integer
dim strText as string


msgbox "Hello VBA World!"

intUB1 = 5
for intc1 = 0 to intUB1
debug.print "The counter is now: "; intc1
next intC1

strText = "Hello VBA World!"
strText = strText & "....over and out."
debug.print strtext
'\\-----------------



first hit CTRL g

this will open a little window below called direct window. you need it for debugging.


Now we are ready to run this.

To do so: click somwewhere in the Sub you just produced so that the cursor is there. Then hit F8. you will see a yellow line marking where your program is at. Each time you hit F8 you will advance a line. Like this you have maximum control to see what your program does.

End of the tutorial...next post we can start doing stuff to your data.


BManke



This is what the result will look like:

Sub test()
'\\Purpose: learning some basics
Dim intC1 As Integer
Dim intUB1 As Integer
Dim strText As String


MsgBox "Hello VBA World!"

intUB1 = 5
For intC1 = 0 To intUB1
    Debug.Print "The counter is now: "; intC1
Next intC1

strText = "Hello VBA World!"
strText = strText & "....over and out."
Debug.Print strText
'\\-----------------

End Sub

[PerryNZ]

OK - so far, so good.

Are the series of dashes after '\\ of significance?

It doesn't have any impact, if they're included or not.

[westconn1]

' means the line is a comment has nothing to do with the code, except for letting you (or others) know later why it was done,
can be on the same line as code, at the end, will show in green in the code window
the \\ has no meaning at all in vb, including vba, just make the comment stand out, it is a c or c++ thing

to record macro in excel, you go menu > tools > macro > record new macro
you will get a dialog where the macro will be recorded to then a small menu bar with pause and stop buttons,
you can the proceed to do most things in excel, though sometimes the mouse does not work as expected, best to use menu and keyboard
try doing a saveAs or open a file,
after stopping the macro you can go to the vba code window and find the recorded macro

tab = " "
null = ""
cr = "
"

in vba these are constants, in order
vbTab
vbNullString
vbNewLine

the string variables you have joined using :
would be like var1 & var2 & var3

# home_email <> null:<:homeelmail:home_email:stop:>

i believe this would be

if not home_email = vbnullstring then home_email = homeemail & home_email & "."  ' some comment in green

in vb a colon : in a line of codes indicate separate statements

a = b : c = b

is same as

a = b
c = b

hope some of this helps a little
keep going with it, once you get started, it will become easier
more help will be forthcoming as you ask the right questions lol

[PerryNZ]

' means the line is a comment has nothing to do with the code, except for letting you (or others) know later why it was done,
can be on the same line as code, at the end, will show in green in the code window
the \\ has no meaning at all in vb, including vba, just make the comment stand out, it is a c or c++ thing

Seems much the same as 'rem' then.

to record macro in excel, you go menu > tools > macro > record new macro
you will get a dialog where the macro will be recorded to then a small menu bar with pause and stop buttons,
you can the proceed to do most things in excel, though sometimes the mouse does not work as expected, best to use menu and keyboard
try doing a saveAs or open a file,
after stopping the macro you can go to the vba code window and find the recorded macro

I get that OK. But what should I be recording? (see closing paragraphs)

tab = " "
null = ""
cr = "
"

in vba these are constants, in order
vbTab
vbNullString
vbNewLine

That's fairly straightforward as the commonality is obvious.

# home_email <> null:<:homeelmail:home_email:stop:>

the string variables you have joined using : would be like var1 & var2 & var3

In that code I quoted, the colon is basically a separator.
Testing for data in a field was odd. The only way that
I could do it was IF not equal to nothing. Bizarre.

That bit translates as you seem to grasp:
If the home email field in the data source contains data
insert the prefix string "Home E-mail:" then the field data,
then a period/full stop, then a space. Then proceed to
the next line of code. If I recall correctly, the default was
that if the test returned FALSE to the IF question, the
rest of the instruction was skipped and the next instruction
was processed.

I believe this would be

if not home_email = vbnullstring then home_email = homeemail & home_email & "."  ' some comment in green

You're as close as it gets, except that I don't understand
the last comment part.
Do full stops/periods have to be coded as "." rather than
something like vbStop/Period?

in vb a colon : in a line of codes indicate separate statements

a = b : c = b

is same as

a = b
c = b

As responded to above - you're bang on.

hope some of this helps a little
keep going with it, once you get started, it will become easier
more help will be forthcoming as you ask the right questions lol

OK. #1 What the hell is "dim?'

(Apart from me, I mean.)

Now, writing this macro. What I have not the slightest
idea about is - simply - what do I type?

In 'longhand,' it would be something like this:

Go to data source
Get the data in row 2 in the column with the header name <Name 1>
Do something (?) with that data, including formatting to bold and a vbTab suffix
Proceed along row 2 to the column with the header name <Name 2> [using a cell reference, perhaps?]
If the cell contains data, do something (?) with that data, adding the suffix vbSpace [is that right?]
If the cell is empty, skip to the next cell in row 2
. . . . . .
When the last column in the SourceData range is reached, vbNewLine
and repeat code sequence until last row in the SourceData is reached.

Then
End process and present word processor document (where all this
is supposed to be going) for editing before manual printing.

Does that make some sense? I hope so.

[westconn1]

trying no answer questions in no particular order
the comment line was just a demo of comment as the vb code in the forum somwhat duplicates the vba ide

variables should be declared, with their type
the most common is Dim proceedure level variable, declared in a sub or function,
others are public and static,
do you have vba help installed, if not i would recommend you get your office disk and do that immediately, then youcan look these up as i am not going to write a full manual
example

dim i as interger, mystr as string, l as long, objdoc as object

any variable not declared or typed will be a variant, it is good practice to put Option Explicit at the top of each code module, then undeclared variables will not be allowed

dim i, j as long

each variable must be typed, in the above only j is a long, i is a variant as it is not typed

as this is in an excel spreadsheet excel is a good enough word processor to complete your task
the easiest way to achieve your result, of printing in a format, is to rearrange the data on the sheet to suit then just use the printout method to print the worksheet
some programmers actually put data into an excel sheet just to format their printing (not my choice)
if there is to much data to easily format like that, find the required name field as i suggested in some post above, then copy that row to a temporary sheet, keep adding the records using find until all the ones matching some criteria have been added then printout the temp sheet

there is no vbstop or vbspace, space is a vb function mystring = space(10) is the same as mystr = " "
you can set your own constant like
const mystop = "." or just use a variable

dim mystop as string
mystop = "."

with the macro, the idea is to record it, not write it
you follow steps to do something manually, for example, try the printout as a test, start the macro recorder, then print the worksheet, stop the macro then go find the code that was recorded
i never use the recorded code now, but i still record macros to find out how the code works and what arguments are used (that is how i answer many of the posts)

[BManke]

Hi!

the "\\" is just there so you see the comment more easily. No functionality other than that. you can leave them out if you dont like them.

I'd like to continue by trying to simply copy the data over from one datarange to the other. Next step will be to filter and change the data in the process of copying. But first: just copy, to make sure the basic routine runs correctly.

First I must redirect you to naming the ranges. I made a mistake there with the targetrange's size: The targetrange should be only including the headers. I wrote: "same size" that's wrong. Just the headerline.

Next you need to add a reference.
This can be done in one of the menus under "References..." - (in my German Excel version it's in the menu "Extras" it might be called "Tools" or something in other language version...just look for "references..." in all menus, should be the first item"

The reference we need is down the list called: 'Microsoft ActivX Data Objects 2.8 Library'
If you dont have 2.8, then use 2.5 (I believe 2.6 and 2.7 had some issues)

Now back to the coding

In your module add a new sub called TransferData and add the code below.
We will be re-using this code for actually solving your problem in the next step.

Option Explicit

Sub TransferData()
'\\Purpose: Copy data from one datarange to another
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim lngRecordsAffected As Long
Dim strConn As String
Dim strSourceFile As String
Dim strSourceRange As String
Dim strTargetRange As String
Dim strQry As String

strSourceFile = "C:\MyExcel1.xls"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSourceFile & ";Extended Properties=""Excel 8.0;HDR=YES"";"
'check if it got assembled correctly
Debug.Print strConn

'open a connection to that file
Set cnn = New ADODB.Connection
cnn.ConnectionString = strConn
cnn.Open

'prepare the SQL command for data manipulation -- this will be the only part that needs changed to add actual functionality
strSourceRange = "SourceRange"
strTargetRange = "TargetRange"
strQry = "INSERT INTO " & strTargetRange & _
         " SELECT * FROM " & strSourceRange
'check if it got assembled correctly
Debug.Print strQry

'prepare execution of the SQL command with a command object
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = strQry
cmd.Execute lngRecordsAffected
MsgBox lngRecordsAffected & " records copied"

'clean up (only causes error when everything is clean already, so we tell Excel to ignore errors)
On Error Resume Next
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
On Error GoTo 0
End Sub

This is one approach of doing it. It has the advantage of being efficient for certain datamanipulations. It has the disadvantage that it introduces SQL, which you might not be familiar with. I'll show you a different approach later, that you can use for fine-tuning your data later yourself.

[BManke]

Umm. I just read your last post... do you only want to reformat? I had the impression that you want to extract certain data based on criteria.

[PerryNZ]

Briefly and quickly: is there an easy way to determine
if I have MS ActiveX installed on my PC?
(I usually don't select the 'install everything' or 'full install'
or 'typical' option.)

[westconn1]

which activex, if you know the name of the file you can look in your system32 directory as the most likely place,
or alternatively you can try to add a reference to it in your excel ide
menu > tools > references

[PerryNZ]

Umm. I just read your last post... do you only want to reformat? I had the impression that you want to extract certain data based on criteria.

Your impression is correct. The criteria is probably modest.
1) don't include records marked as redundant (in keyword filed)
2) close up [disregard] empty fields (all empty fields)
3) I forget - there is another criteria that doesn't quickly come to mind.

The formatting relates to the finished print job

Name second name, more details, contact details
-------other details . . . .

Name second name, more details, contact details
-------other details . . . .

(The dashes represent white space. VBulletin closes
spaces up, otherwise. Essentially it's like a column of
names with an indent tab, so the details text is inset.)

[PerryNZ]

I forgot to say: yes, I do have VBA HELP installed
and working.

There's a directX sub-folder in system32
and (in the system32 folder list):
activeds.dll
activeds.tlb
actmovie.exe
msacm.dll
msacm32.dll
msacm32.drv

There is no word 'reference' in the Excel tools dialogue box.

In Tools > Options > Settings is a check box
R1C1 reference style, that is not ticked. Is
that of significance?

IDE drives is something I'm aware of. IDE for excel? No.

[westconn1]

IDE stands for i forget at the moment,
but is the development enviroment where you can write and test your code
found it

The windows that are displayed when you start VB are collectively known as the Visual Basic Integrated Development Environment (IDE).

while you can add a reference to word, i don't believe you need to do that as excell will allow you to print columns very nicely
menu > tools > references > microsoft word 9.0 object library
yours may not be version 9.0

[BManke]

no that r1c1 thing is something different. you have activex if you have windows xp.

you need to hit alt F11 and then in the menus of the the coding environment (the vb editor) there is the references thing somewhere. not in the standard excel menus

[PerryNZ]

That makes one big difference.
Yes, it's there.


I run W2kP. Not XP.

I don't want to print columns. Rather, run-on text. And
text without lots of empty gaps where there was no data
matching the <fieldname> in the excel spreadsheet.

This - I hope - will be a useful exercise for me. When
I get this understood and operational, I have two
other such projects. One's a 'database' of quotes;
the other's another address list. Less fields, but still
subject to a conditional print after testing certain field
values.

[BManke]

So did you add the reference to the microsoft activex dataobjects, and did my code work for you?

[PerryNZ]

So did you add the reference to the microsoft activex dataobjects, and did my code work for you?

I've had Internet connectivity problems - just solved an hour
ago. So I can now get get back to this with better continuity.
(Modem lost it marbles - took a while to figure that out.)

[BManke]

sure.
first go back to this post:
http://www.vbforums.com/showpost.php...3&postcount=24

and try if you can get the code there to work

[PerryNZ]

ERROR
Cannot expand named range @

cmd.Execute lngRecordsAffected
MsgBox lngRecordsAffected & " records copied"

I think that the error message is generated at end of this line
cmd.Execute lngRecordsAffected

[BManke]

I think that the error message is generated at end of this line
cmd.Execute lngRecordsAffected

That is correct.

Actually no bad result. Everything seems to work until the very end. But: Excel seems to identify something below the TargetRange. In order to not overwrite it, the execution of your command is cancelled. If you cant see anything and still get the error, select the whole sheet except the header and hit delete.

[PerryNZ]

Status

|cmd.Execute lngRecordsAffected
(cursor flashing at beginning of line, yellow arrow in margin)

Press f8 here and the excel sheet (called copy) is populated with the
copied data.

The yellow high-light of the line [cmd.Execute lngRecordsAffected] and
yellow margin arrow disappear.

After many seconds delay, the following error message is generated:

The field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data.

Is it trying to repeat the instruction, perhaps?

I’ve deleted all data from the excel sheet called copy and re-run the
routine several times. The result is always the same.

[BManke]

you hve those debug.writeline statements in there, right? could you paste me the output of this in for the sqlQry variable?

[BManke]

maybe you can even figure it out yourself: for some reason, most probably some flaw in the sql statement you are using or the design of the targetrange, or simply the jet enginge getting confused, excel tries to write everything in one cell.

try to change the sql statement.

instead of the asterisks you can name the fields to write to and to read from.
The basic syntax is like this:

INSERT INTO targettable (field1, field2, ...) SELECT field1, field2, ... FROM sourcetable