Disabling external data refresh using vba
 

I am importing text files into excel automatically, but
after the first time I do it, I don't want the data to
update again. The excel file is opened automatically
using a macro, so I tried to use a macro to turn it off,
but could not find anything that effected it. Does anyone
have any idea's on how I can do this?

Disabling external data refresh using vba
 

You should be able to get all the code for this by doing the operation
manually with the macro recorder turned on.

HTH,
Shockley


"BryanL" wrote in message
...
I am importing text files into excel automatically, but
after the first time I do it, I don't want the data to
update again. The excel file is opened automatically
using a macro, so I tried to use a macro to turn it off,
but could not find anything that effected it. Does anyone
have any idea's on how I can do this?

Disabling external data refresh using vba
 

When using the macro recorder, it shows opening the file, but does not
show disabling the external data importer. Does anyone know if this is
even possible?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Disabling external data refresh using vba
 

Sorry, I didn't understand your question. Would it work to protect the
workbook after the first refresh?

Regards,
Shockley


"Bryan L" wrote in message
...


When using the macro recorder, it shows opening the file, but does not
show disabling the external data importer. Does anyone know if this is
even possible?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Disabling external data refresh using vba
 

Protecting the workbook stops the data from being imported, which is
what I was looking for; however, it also stops the macro from
completeing, because it asked for user intervention to keep going. Is
there a way to suppress this error message, or another way to stop the
data from being imported?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Disabling external data refresh using vba
 

Bryan,

How about adding a defined Name (e.g. nImportFlag) to the workbook. Then you
can store a value in the name that indicates whether the text has been
imported. This value will be saved with the workbook and can be checked by
the macro at the very beginning to stop any future imports.

Troy


Sub Test()

Dim bImported As Boolean

'Trap an error if it doesn't exist.
On Error Resume Next
bImported = CBool(Mid$(ThisWorkbook.Names("nImportFlag").Value , 2))
On Error GoTo 0

If bImported = True Then
MsgBox "Text file was already imported."
'Exit the routine.
Exit Sub
Else
'Run the import code... (put your code here).
MsgBox "Importing the text file."

'Save the flag indicating the import was successful.
ThisWorkbook.Names.Add Name:="nImportFlag", RefersToR1C1:=True
MsgBox "Saved the Imported flag."
End If

End Sub




"shockley" wrote in message
...
If you can change the code in the macro that does the importing, there are
many ways to handle this, the simplest being to just have it check to see
if
the workbook is protected. If you can't change the code in the macro then
it gets more complex, and I don't know of a way to do it with vba,
although
there probably is a way.

Shockley


"Bryan L" wrote in message
...
Protecting the workbook stops the data from being imported, which is
what I was looking for; however, it also stops the macro from
completeing, because it asked for user intervention to keep going. Is
there a way to suppress this error message, or another way to stop the
data from being imported?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Disabling external data refresh using vba
 

The test files are being imported through a macro, the macro is just
opening the file, the workbook itself is autoimporting the text file on
open. I've gone to the visual basic editor in Excel and can find no
code for this auto-updating. I searched through the commands for vba,
but could find nothing that related to the importing of external data
ranges. Also, I don't know if it makes a difference but protecting the
entire workbook did not stop the autoimporting, I had to protect the
individual sheets.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Disabling external data refresh using vba
 

Bryan,

I need to ask some basic questions since I can't see what is happening on
your computer.

1) What is the sequence of steps that you take to open Excel and to open the
file or files?
2) What is the fullname of the test files that you are opening (e.g.
test1.xls, text2.txt, tst3.csv)?
3) What is the name of the Excel file that you open (or do you just open the
test files directly)?
4) What version of Excel are you using?
5) Do you have "Macro Virus Protection" checked on in the Tools | Options -
General Tab?
5b) Do you receive a warning message when you open the file (The workbook
you are opening contains macros... Disable Macros Enable Macros Do
Not Open)?
6) Is there other VBA code, but you just can't figure out how it is
importing the file?

Troy


"Bryan L" wrote in message
...


The test files are being imported through a macro, the macro is just
opening the file, the workbook itself is autoimporting the text file on
open. I've gone to the visual basic editor in Excel and can find no
code for this auto-updating. I searched through the commands for vba,
but could find nothing that related to the importing of external data
ranges. Also, I don't know if it makes a difference but protecting the
entire workbook did not stop the autoimporting, I had to protect the
individual sheets.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Disabling external data refresh using vba
 

I'm using excel 2002, from the office xp suite

The name of the excel file is :

10-05-03_Hookston_3480_Sunday.xls
but it changes on a daily basis

The names of the imported files a
001.txt
003.txt
256.txt
These file names never change, and cannot be changed.

The macro that is run does the following:
Opens a Hookston Template (Hookston_3480_Template.xls)
Saves the Hookston Template with whatever the date is in the file name
(10-05-03_Hookston_3480_Sunday.xls,
10-10-03_Hookston_3480_Friday.xls, etc)
Closes the workbook and excel.

The importing of the files is all done by excel, it was setup by doing
the following:
From an excel workbook
Click Data
Click Import External Data
Click Import Data
Select file you wish to import
-If it's a txt file it will ask about delimiting and all that so that it
is formated correctly
Click Finish

If you then write click on the data and go to
External Data Range Properties, one of the options is
Refresh Data on file open.

When we realized that by opening the files after there creation date was
changing the files, the first thing I did was try to get to the External
Data Range Properites using VBA, but could not find any commands that
let me alter those properties. After hearing some of the responses I
changed the opening macro, it now does the following:

Opens a Hookston Template (Hookston_3480_Template.xls)
Cycles through all data sheets and protects them.
(I found that if I protect the whole workbook at once, the text files
are still imported)
Saves the Hookston Template with whatever the date is in the file name
(10-05-03_Hookston_3480_Sunday.xls,
10-10-03_Hookston_3480_Friday.xls, etc)
Closes the workbook and excel.

This would be fine if the only time the after the workbook is created it
was done by a person looking for something, but it is not. Every couple
of days another macro is ran, it does the following:

Opens up 10-05-03_Hookston_3480_Sunday.xls (or whatever day)
Cycles through all Charts Sheets in the workbook
Prints out Charts
Closes workbook, Closes excel

So the original problem we were having is that when the workbook was
opened to print out a previous days charts, the data was changed, and
incorrect charts were printed. Now with the protection when the data
goes to update and error message pops up saying that "The sheet is
protected, so the data won't update, do you want to continue?"
This message requires someone to click continue, then the macro
completes, printing out the charts and closing excel.
This process needs to have no human intervention.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Disabling external data refresh using vba
 

Bryan,

Ok, I think I've got the picture now. You are using the External Data
feature of Excel to create a link to some text files. You currently have the
"Refresh data on file open" checked (this can be found by right-clicking on
the table of data and selecting Data Range Properties). Leaving this checked
will cause the data from the text file to be refreshed each time the Excel
file opens (i.e. later printing). Which is not the desired operation.

By the way, the object for the external data imports is "QueryTable". You
probably were looking for something by the name of "DataTable", which of
course is completely different . ;-)

You have a couple of options.

--- Option 1:
- Leave the default state of the "Refresh data on file open" checked.
- After the text file is imported, let the property:
QueryTables("theTableName").RefreshOnFileOpen = False
- Save the Excel file which now contains the newly imported text data.

--- Option 2:
- Leave the default state of the "Refresh data on file open" unchecked.
- When the Excel file opens, have a line of VBA code:
bRefresh = QueryTables("theTableName").Refresh
'Check if the Refresh was successful...
If bRefresh = False Then MsgBox "Error: Could not refresh the data table"

This will cause the data in Excel to refresh one time and will not change
the "Refresh data on file open" boolean, which is False.
- Save the Excel file.

Let me know if that works. Cheers.

Troy


"Bryan L" wrote in message
...
I'm using excel 2002, from the office xp suite

The name of the excel file is :

10-05-03_Hookston_3480_Sunday.xls
but it changes on a daily basis

The names of the imported files a
001.txt
003.txt
256.txt
These file names never change, and cannot be changed.

The macro that is run does the following:
Opens a Hookston Template (Hookston_3480_Template.xls)
Saves the Hookston Template with whatever the date is in the file name
(10-05-03_Hookston_3480_Sunday.xls,
10-10-03_Hookston_3480_Friday.xls, etc)
Closes the workbook and excel.

The importing of the files is all done by excel, it was setup by doing
the following:
From an excel workbook
Click Data
Click Import External Data
Click Import Data
Select file you wish to import
-If it's a txt file it will ask about delimiting and all that so that it
is formated correctly
Click Finish

If you then write click on the data and go to
External Data Range Properties, one of the options is
Refresh Data on file open.

When we realized that by opening the files after there creation date was
changing the files, the first thing I did was try to get to the External
Data Range Properites using VBA, but could not find any commands that
let me alter those properties. After hearing some of the responses I
changed the opening macro, it now does the following:

Opens a Hookston Template (Hookston_3480_Template.xls)
Cycles through all data sheets and protects them.
(I found that if I protect the whole workbook at once, the text files
are still imported)
Saves the Hookston Template with whatever the date is in the file name
(10-05-03_Hookston_3480_Sunday.xls,
10-10-03_Hookston_3480_Friday.xls, etc)
Closes the workbook and excel.

This would be fine if the only time the after the workbook is created it
was done by a person looking for something, but it is not. Every couple
of days another macro is ran, it does the following:

Opens up 10-05-03_Hookston_3480_Sunday.xls (or whatever day)
Cycles through all Charts Sheets in the workbook
Prints out Charts
Closes workbook, Closes excel

So the original problem we were having is that when the workbook was
opened to print out a previous days charts, the data was changed, and
incorrect charts were printed. Now with the protection when the data
goes to update and error message pops up saying that "The sheet is
protected, so the data won't update, do you want to continue?"
This message requires someone to click continue, then the macro
completes, printing out the charts and closing excel.
This process needs to have no human intervention.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Disabling external data refresh using vba
 

Bryan,

Ok, I think I've got the picture now. You are using the External Data
feature of Excel to create a link to some text files. You currently have the
"Refresh data on file open" checked (this can be found by right-clicking on
the table of data and selecting Data Range Properties). Leaving this checked
will cause the data from the text file to be refreshed each time the Excel
file opens (i.e. later printing). Which is not the desired operation.

By the way, the object for the external data imports is "QueryTable". You
probably were looking for something by the name of "DataTable", which of
course is completely different . ;-)

You have a couple of options.

--- Option 1:
- Leave the default state of the "Refresh data on file open" checked.
- After the text file is imported, let the property:
QueryTables("theTableName").RefreshOnFileOpen = False
- Save the Excel file which now contains the newly imported text data.

--- Option 2:
- Leave the default state of the "Refresh data on file open" unchecked.
- When the Excel file opens, have a line of VBA code:
bRefresh = QueryTables("theTableName").Refresh
'Check if the Refresh was successful...
If bRefresh = False Then MsgBox "Error: Could not refresh the data table"

This will cause the data in Excel to refresh one time and will not change
the "Refresh data on file open" boolean, which is False.
- Save the Excel file.

Let me know if that works. Cheers.

Troy


"Bryan L" wrote in message
...
I'm using excel 2002, from the office xp suite

The name of the excel file is :

10-05-03_Hookston_3480_Sunday.xls
but it changes on a daily basis

The names of the imported files a
001.txt
003.txt
256.txt
These file names never change, and cannot be changed.

The macro that is run does the following:
Opens a Hookston Template (Hookston_3480_Template.xls)
Saves the Hookston Template with whatever the date is in the file name
(10-05-03_Hookston_3480_Sunday.xls,
10-10-03_Hookston_3480_Friday.xls, etc)
Closes the workbook and excel.

The importing of the files is all done by excel, it was setup by doing
the following:
From an excel workbook
Click Data
Click Import External Data
Click Import Data
Select file you wish to import
-If it's a txt file it will ask about delimiting and all that so that it
is formated correctly
Click Finish

If you then write click on the data and go to
External Data Range Properties, one of the options is
Refresh Data on file open.

When we realized that by opening the files after there creation date was
changing the files, the first thing I did was try to get to the External
Data Range Properites using VBA, but could not find any commands that
let me alter those properties. After hearing some of the responses I
changed the opening macro, it now does the following:

Opens a Hookston Template (Hookston_3480_Template.xls)
Cycles through all data sheets and protects them.
(I found that if I protect the whole workbook at once, the text files
are still imported)
Saves the Hookston Template with whatever the date is in the file name
(10-05-03_Hookston_3480_Sunday.xls,
10-10-03_Hookston_3480_Friday.xls, etc)
Closes the workbook and excel.

This would be fine if the only time the after the workbook is created it
was done by a person looking for something, but it is not. Every couple
of days another macro is ran, it does the following:

Opens up 10-05-03_Hookston_3480_Sunday.xls (or whatever day)
Cycles through all Charts Sheets in the workbook
Prints out Charts
Closes workbook, Closes excel

So the original problem we were having is that when the workbook was
opened to print out a previous days charts, the data was changed, and
incorrect charts were printed. Now with the protection when the data
goes to update and error message pops up saying that "The sheet is
protected, so the data won't update, do you want to continue?"
This message requires someone to click continue, then the macro
completes, printing out the charts and closing excel.
This process needs to have no human intervention.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Disabling external data refresh using vba
 

I forgot to mention in Option 2, you need to check to see if this is the
first time to refresh.

Troy

"TroyW" wrote in message
...
Bryan,

Ok, I think I've got the picture now. You are using the External Data
feature of Excel to create a link to some text files. You currently have
the
"Refresh data on file open" checked (this can be found by right-clicking
on
the table of data and selecting Data Range Properties). Leaving this
checked
will cause the data from the text file to be refreshed each time the Excel
file opens (i.e. later printing). Which is not the desired operation.

By the way, the object for the external data imports is "QueryTable". You
probably were looking for something by the name of "DataTable", which of
course is completely different . ;-)

You have a couple of options.

--- Option 1:
- Leave the default state of the "Refresh data on file open" checked.
- After the text file is imported, let the property:
QueryTables("theTableName").RefreshOnFileOpen = False
- Save the Excel file which now contains the newly imported text data.

--- Option 2:
- Leave the default state of the "Refresh data on file open" unchecked.
- When the Excel file opens, have a line of VBA code:
bRefresh = QueryTables("theTableName").Refresh
'Check if the Refresh was successful...
If bRefresh = False Then MsgBox "Error: Could not refresh the data
table"

This will cause the data in Excel to refresh one time and will not change
the "Refresh data on file open" boolean, which is False.
- Save the Excel file.

Let me know if that works. Cheers.

Troy


"Bryan L" wrote in message
...
I'm using excel 2002, from the office xp suite

The name of the excel file is :

10-05-03_Hookston_3480_Sunday.xls
but it changes on a daily basis

The names of the imported files a
001.txt
003.txt
256.txt
These file names never change, and cannot be changed.

The macro that is run does the following:
Opens a Hookston Template (Hookston_3480_Template.xls)
Saves the Hookston Template with whatever the date is in the file name
(10-05-03_Hookston_3480_Sunday.xls,
10-10-03_Hookston_3480_Friday.xls, etc)
Closes the workbook and excel.

The importing of the files is all done by excel, it was setup by doing
the following:
From an excel workbook
Click Data
Click Import External Data
Click Import Data
Select file you wish to import
-If it's a txt file it will ask about delimiting and all that so that it
is formated correctly
Click Finish

If you then write click on the data and go to
External Data Range Properties, one of the options is
Refresh Data on file open.

When we realized that by opening the files after there creation date was
changing the files, the first thing I did was try to get to the External
Data Range Properites using VBA, but could not find any commands that
let me alter those properties. After hearing some of the responses I
changed the opening macro, it now does the following:

Opens a Hookston Template (Hookston_3480_Template.xls)
Cycles through all data sheets and protects them.
(I found that if I protect the whole workbook at once, the text files
are still imported)
Saves the Hookston Template with whatever the date is in the file name
(10-05-03_Hookston_3480_Sunday.xls,
10-10-03_Hookston_3480_Friday.xls, etc)
Closes the workbook and excel.

This would be fine if the only time the after the workbook is created it
was done by a person looking for something, but it is not. Every couple
of days another macro is ran, it does the following:

Opens up 10-05-03_Hookston_3480_Sunday.xls (or whatever day)
Cycles through all Charts Sheets in the workbook
Prints out Charts
Closes workbook, Closes excel

So the original problem we were having is that when the workbook was
opened to print out a previous days charts, the data was changed, and
incorrect charts were printed. Now with the protection when the data
goes to update and error message pops up saying that "The sheet is
protected, so the data won't update, do you want to continue?"
This message requires someone to click continue, then the macro
completes, printing out the charts and closing excel.
This process needs to have no human intervention.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!