Getting Bug-eyed! Macros no longer run properly when workbook is opened.

E

Eldraad

Here is the problem that is knawing on my mind...

I created a macro using the Record Macro function to import a specifi
text report when the workbook is opened. It worked fine. I had t
change the address in the macro so it could find the file when I was a
work. The file will be placed in the My Documents folder just like i
is on my home machine. The only difference is mine is i
/Administrator/My Documents and the one for work has /Circulation/M
Documents. I made the change to the macro and now it can not find th
text file. I changed the address back to my home system (at home) an
it STILL can't find the text file. I get the following Erro
Message...

Excel cannot find the text file to refresh this external data range.

Check to make sure the file has not been renamed (etc.)


Here is the copy of the macro that WORKS:

Sub air_weight_convert()
'
' air_weight_convert Macro
' Macro recorded 7/23/2004 by Eldraad
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Administrator\M
Documents\airweight.txt", _
Destination:=Range("A1"))
.Name = "airweight_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(6, 48, 12, 9, 6, 14, 2, 5
25)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub



And here is a copy of the one that does NOT work:

Sub air_weight_convert()
'
' air_weight_convert Macro
' Macro recorded 7/23/2004 by Eldraad
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Administrator\M
Documents\airweight.txt", _
Destination:=Range("A1"))
.Name = "airweight_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(6, 48, 12, 9, 6, 14, 2, 5
25)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

What is the difference and way does it no longer start?

This is really strting to make me nuts! What could it be? Refresh o
file open is true and everything seems the same, yet One workbook work
and the other doesn't. The ONLy difference is the name of the workboo
(that was changed from "My Woking PO" to "Automatic PO".

Any ideas? All are welcome at this point
 
E

Eldraad

Oddly enough, the macros work when I use them from Tools/Macros an
selecting the macro I want and hitting run. But not automatically whe
the workbook is opened. It tries but with the error message
mentioned.

Nothing special was done when I recorded these. I just set up th
macro recorder and imported my text. It saved it where ever it save
the things although I THINK I selected All Open workbooks.

Why the second one does not auto run and the first does I do not kno
 
E

Eldraad

Hello Frank,

As I mentioned above I simply recorded the macros and they went wher
ever macros go when recorded. They were both recorded the same way ye
the second one does not run when that workbook is opened.

When checking in the VB editor the left dropdowns are both at "General
with nothing else to view (other than my macro that is).

I have tried to create a new module with the workbook_open event at th
top and inserting my macro but I dont know enough about it to get i
working right. The examples I found really didn't tell me how to ru
the macro with the workbook_open event. There are 5 macros that use
to start when the workbook was opened. None start now, I have to ru
them individually. I just gave the example of one of them in thi
thread
 
E

Earl Kiosterud

Eldraad,

I'm not clear. At one point you're saying that it runs, but can't find the
file, giving the error message. At another, you're saying it doesn't auto
run at all (in which case you'd get no message, because it ain't running no
macro).

Is there a call to air_weight_convert() from either auto_open in a regular
module, or in Workbook_Open in the ThisWorkbook module?

Say more.
 
E

Eldraad

Earl Kiosterud, thanks for the reply!

Okay, let me see if I can say it in a fashion that means
something...lol.

Hard to do...

I made a workbook. in each of the five last worksheets I recorded a
simple macro. In the "Statistical" worksheet I recorded a macro that
would look in the "My Documents" folder, find a text file called
"statistical" and import it into that worksheet. I did the same for
the remaining four worksheets recording a macro in the worksheet that
would hold the specific text file. That was ALL I did. No putting
things in a personal workbook or anything other than what is the
default setting.

When I Opened the workbook (after all of the recording) my last 5
worksheets were populated with the proper reports. I erased each of
the worksheets being careful to say NO when it asked if I wanted to
delete the thing that was querrying the external range. I tested it
for 5 days with different data in each of the text files to be sure
that I was able to pull out the information, from each report, that I
was looking for.

I then copied the workbook. Renamed the copy and took it to work where
it was to be used. There, I had to change the address in each of the
macros to the location of the "My Documents" folder on the computer we
use.

From that point on it no longer found the txt files. If you look at
the original post I show the same macro from the Original working
workbook (which STILL works) and the same maco from the renamed
workbook (which does NOT work). I CAN run the macro manually through
tools/macro/macros and the files are found just fine.

Bottom line: I have 5 macros that I want to run when the workbook is
opened (each one filling the proper worksheet). I see no difference
(except for the address) between the two macros that I supplied. One
works the other does not. THAT is driving me nuts. I will be
satisfied with an answer that allows me to run the macros when the
workbook is opened...but since I am sooo new at this it may have to be
step by step instructions using the supplied macro. Or at least an
answer telling me why one is working and the other is not! I AM
getting nutty here..sorry if I am losing it...lol.
 
E

Earl Kiosterud

You're welcome to send the file. This may be one that doesn't resolve
easily using this newsgroup format. The address (mvpearl), highly encrypted
<g>, is below.

The macro suggests your using XL 2000, which I don't have, but it should run
on 2002. 2002 would record such a text file action differently. Which are
you using?
 
E

Eldraad

Lord, I thought I had mentioned which version Excel I was running an
now I see I had not...stupid me!

It is Excel 2002 + SP1 on Windows XP Pro.

I went in last night and cleared the 5 worksheets this time deletin
the information AND telling it to delete the thing that is refreshin
the pages. By the way, where is this process located? I thought i
was the refreshonopen=true in the (macro) module but that isn't reall
the case.

I opened the visual Basic editor and did a view code for "thi
workbook" I then inserted the names of the macros thinking this migh
get them to run when the workbook is opened. It does, but the result
are bad with parts of one worksheet spilling into others. It looks lik
each worksheet is trying to open all of the macros I guess
 
E

Earl Kiosterud

Elraad,

Yes, the RefreshOnFileOpen does cause it to refresh when the workbook is
opened -- no code needed. You should probably be using either that, or the
Refresh command, instead of the original import. In code, it looks like:

Range("B2").QueryTable.Refresh BackgroundQuery:=False

Does the Refresh command work when run manually?

If you're going to be moving this from machine to machine, the refresh
won't work, of course, since the locations are different.
 
E

Eldraad

Earl Kiosterud I thank you for all of your help and suggestions!

I think I stumbled on a solution of sorts.

There was NO problem with the code shown at the start of the thread.
found that out by playing with The Visual Basic Editor. I copied th
code for each "macro" (or was it really an event?) and pasted it int
the "sheet" that was to hold the information imported by tha
particular code (in the VBA project ). I then deleted each macro tha
showed up in the macro selection and made sure they were gone from th
modules they were in. Saved. Closed Excel. Opened excel. Sam
problem!

In a fit of frustration I transplanted one of the macros into
different work sheet (deleting the macro that belonged there) an
saved/closed/reopened the workbook. Same error! In fact, same erro
message for a macro that was no longer in the workbook! That told m
that SOMEHOW there must be something like a cache file that kep
calling the macro, and supplying the old code I had deleted.

I Deleted the information on each page that held a macro INCLUDING tha
invisible link that wants to locate the external data.
Saved/closed/reopened and no errors! No import of data either! LOL.
That was okay by me. I then ran the macro associated with each or th
five worksheets (and they worked fine). I cleared each page this tim
saying NO if I wanted to delete the external link.
Saved/closed/reopened and BAM! Everything is just where it should be.
I have got the macros to work when the workbook is started each time
have tried. Data Overwrite is working and all is well.

My question is: Where was this query for external data coming from?
found no duplicate code using the VB Editor. IS there some type o
cache file that can do what happened?

Again, Thanks EVERYONE for you help!

Eldraa
 
E

Earl Kiosterud

Elraad,

I'm not real sure what you have set up here. But I'll mention this: In
Excel 2002, when you do a Data - Get External Data with a text file, the
wizard prompts you for the file name and location, how to format the
columns, and all that stuff. And it remembers it. That range of cells
becomes associated with the query. The only macro command to re-read the
data is the .Refresh method. Or you can right-click anywhere in the range
of cells, and choose Refresh. This may be what's happening when you talk of
an apparent cache or something along those lines.

As you step through that wizard, at one point you can click a "Properties"
button. In that, you can specify "Refresh data on file open," which will
cause the query to run each time the file is open. There is also a "Prompt
for file name on refresh."

Perhaps you can use all this in your application, and do away with macros
entirely.
 
E

Eldraad

That is about exactly what I did. But it seems to get confused if it
can not find the text file where it is expected and then throws up the
errors, even after I correct the path. Thats when I had to delete the
information on the worksheets to re-establish the data links. As you
saw from the information posted I had the refreshonfileopen set to
true. Later I did try the textpromptonrefresh but it didn't do
anything as the error messages were still piling up
 
Top