Overwriting excel files

[Pwyd]

Is there any way to destructively over-write an excel file that already exists?

I've imported data from an excel file, and want to "update" it. Since
transferspreadsheet is a tad complex, i figured it would be easier to just
over-write the old spreadsheet with the new, updated one. Is this doable?
When i've tried in the past, the macro ends when excel claims the
worksheet/file already exists.

 

[bhicks11 via AccessMonster.com]

TRANSFERSPREADSHEET

Access creates a new spreadsheet when you export data from Access. If the
file name is the same as the name of an existing spreadsheet, Access replaces
the existing spreadsheet, unless you're exporting to an Excel version 5.0 or
later workbook. In that case, Access copies the exported data to the next
available new worksheet in the workbook.

So, I would delete the original first with something like this:

Call Shell("del.exe /c Del " & mfile , vbMaximizedFocus)

Bonnie
http://www.dataplus-svc.com

 

[Pwyd]

I have a problem with that, the same as the transferspreadsheet method: the
location of the file has to be static

Any other ideas?

 

[Jerry Whittle]

You can do it in a function.

Function fKillFile()
Kill "N:\Excel Worksheet.xls"
End Function

 

[Pwyd]

Perhaps i'm a bit slow, i don't see how that solves the problem, unless the
"kill" function call works differently -- the file always needs to be in the
same place for the kill to work: Each machine that this database is put on
that wants to make use of it will either have to save to the same worksheet
in the same place on a shared drive (not what i wanted) or i'll have to make
each database have its own function with a string representing the location
of their file. I didn't want to do that. What will happen when i leave, and
a new machine needs a copy?

 

[bhicks11 via AccessMonster.com]

When you transferspreadsheet, how do you know the file name and path? It is
at this time that you can capture the file name and path to a memory variable
that you run the delete command with, just before you transfer?

Have I missed something? You must have that to write the file?

Bonnie
http://www.dataplus-svc.com

Perhaps i'm a bit slow, i don't see how that solves the problem, unless the
"kill" function call works differently -- the file always needs to be in the
same place for the kill to work: Each machine that this database is put on
that wants to make use of it will either have to save to the same worksheet
in the same place on a shared drive (not what i wanted) or i'll have to make
each database have its own function with a string representing the location
of their file. I didn't want to do that. What will happen when i leave, and
a new machine needs a copy?

You can do it in a function.

[quoted text clipped - 9 lines]

 

[Pwyd]

I wasn't using transferspreadsheet for exactly the same reason.
I wanted something that wasn't static.

When you transferspreadsheet, how do you know the file name and path? It is
at this time that you can capture the file name and path to a memory variable
that you run the delete command with, just before you transfer?

Have I missed something? You must have that to write the file?

Bonnie
http://www.dataplus-svc.com

Perhaps i'm a bit slow, i don't see how that solves the problem, unless the
"kill" function call works differently -- the file always needs to be in the
same place for the kill to work: Each machine that this database is put on
that wants to make use of it will either have to save to the same worksheet
in the same place on a shared drive (not what i wanted) or i'll have to make
each database have its own function with a string representing the location
of their file. I didn't want to do that. What will happen when i leave, and
a new machine needs a copy?

You can do it in a function.

[quoted text clipped - 9 lines]

When i've tried in the past, the macro ends when excel claims the
worksheet/file already exists.

 

[Pwyd]

Besides. Even if i wanted to go that route, there are two other seperate
problems with that: one, the imported data is being counted, is in a
different location than the spreadsheet thats used to hold the counts. Its
done through a macro and a standard import command. There is nowhere to
"grab" any location, it's not done programmatically.

I wasn't using transferspreadsheet for exactly the same reason.
I wanted something that wasn't static.

When you transferspreadsheet, how do you know the file name and path? It is
at this time that you can capture the file name and path to a memory variable
that you run the delete command with, just before you transfer?

Have I missed something? You must have that to write the file?

Bonnie
http://www.dataplus-svc.com

Perhaps i'm a bit slow, i don't see how that solves the problem, unless the
"kill" function call works differently -- the file always needs to be in the
same place for the kill to work: Each machine that this database is put on
that wants to make use of it will either have to save to the same worksheet
in the same place on a shared drive (not what i wanted) or i'll have to make
each database have its own function with a string representing the location
of their file. I didn't want to do that. What will happen when i leave, and
a new machine needs a copy?

You can do it in a function.

[quoted text clipped - 9 lines]
When i've tried in the past, the macro ends when excel claims the
worksheet/file already exists.

 

[bhicks11 via AccessMonster.com]

I may be daft but somewhere, sometime you must know the file name and path or
you couldn't be overwriting it??

Bonnie

http://www.dataplus-svc.com

Besides. Even if i wanted to go that route, there are two other seperate
problems with that: one, the imported data is being counted, is in a
different location than the spreadsheet thats used to hold the counts. Its
done through a macro and a standard import command. There is nowhere to
"grab" any location, it's not done programmatically.

I wasn't using transferspreadsheet for exactly the same reason.
I wanted something that wasn't static.

[quoted text clipped - 22 lines]

 

[Pwyd]

No, thats exactly my point. Each person uses a different copy, and a
different filename, which they choose. So to what you're stating, i would
have to force them to all use the same filename, and the same location.
Thats just not possible. At the very most, even their local workstations are
through a network, their personal folders alone would change the location of
the filename. Thats why i can't use transferspreadsheet, and why i wanted to
use just the plain old "export" command. I already use the "import" command
to get an old copy of the data i want to update, because the user can walk it
where it needs to go.

I may be daft but somewhere, sometime you must know the file name and path or
you couldn't be overwriting it??

Bonnie

http://www.dataplus-svc.com

Besides. Even if i wanted to go that route, there are two other seperate
problems with that: one, the imported data is being counted, is in a
different location than the spreadsheet thats used to hold the counts. Its
done through a macro and a standard import command. There is nowhere to
"grab" any location, it's not done programmatically.

I wasn't using transferspreadsheet for exactly the same reason.
I wanted something that wasn't static.

[quoted text clipped - 22 lines]

When i've tried in the past, the macro ends when excel claims the
worksheet/file already exists.

 

[bhicks11 via AccessMonster.com]

Sorry, I do not know a way to do what you are asking. I do something similar
with transferspreadsheet on a network but I capture the user name for a
unique file.

Bonnie

http://www.dataplus-svc.com

No, thats exactly my point. Each person uses a different copy, and a
different filename, which they choose. So to what you're stating, i would
have to force them to all use the same filename, and the same location.
Thats just not possible. At the very most, even their local workstations are
through a network, their personal folders alone would change the location of
the filename. Thats why i can't use transferspreadsheet, and why i wanted to
use just the plain old "export" command. I already use the "import" command
to get an old copy of the data i want to update, because the user can walk it
where it needs to go.

I may be daft but somewhere, sometime you must know the file name and path or
you couldn't be overwriting it??

[quoted text clipped - 14 lines]

 

[Pwyd]

I wish i could. Although i can capture their shortname and use that, the
filename would be of my choosing, not theirs. In this case, that's not
acceptable.

Sorry, I do not know a way to do what you are asking. I do something similar
with transferspreadsheet on a network but I capture the user name for a
unique file.

Bonnie

http://www.dataplus-svc.com

No, thats exactly my point. Each person uses a different copy, and a
different filename, which they choose. So to what you're stating, i would
have to force them to all use the same filename, and the same location.
Thats just not possible. At the very most, even their local workstations are
through a network, their personal folders alone would change the location of
the filename. Thats why i can't use transferspreadsheet, and why i wanted to
use just the plain old "export" command. I already use the "import" command
to get an old copy of the data i want to update, because the user can walk it
where it needs to go.

I may be daft but somewhere, sometime you must know the file name and path or
you couldn't be overwriting it??

[quoted text clipped - 14 lines]

When i've tried in the past, the macro ends when excel claims the
worksheet/file already exists.

 

[Pwyd]

is there a way to grab the name of the file they import during the standard
"import" command?

I wish i could. Although i can capture their shortname and use that, the
filename would be of my choosing, not theirs. In this case, that's not
acceptable.

Sorry, I do not know a way to do what you are asking. I do something similar
with transferspreadsheet on a network but I capture the user name for a
unique file.

Bonnie

http://www.dataplus-svc.com

No, thats exactly my point. Each person uses a different copy, and a
different filename, which they choose. So to what you're stating, i would
have to force them to all use the same filename, and the same location.
Thats just not possible. At the very most, even their local workstations are
through a network, their personal folders alone would change the location of
the filename. Thats why i can't use transferspreadsheet, and why i wanted to
use just the plain old "export" command. I already use the "import" command
to get an old copy of the data i want to update, because the user can walk it
where it needs to go.

I may be daft but somewhere, sometime you must know the file name and path or
you couldn't be overwriting it??
[quoted text clipped - 14 lines]
When i've tried in the past, the macro ends when excel claims the
worksheet/file already exists.

 

[pietlinden]

Perhaps i'm a bit slow, i don't see how that solves the problem, unless the
"kill" function call works differently -- the file always needs to be in the
same place for the kill to work:  Each machine that this database is put on
that wants to make use of it will either have to save to the same worksheet
in the same place on a shared drive (not what i wanted) or i'll have to make
each database have its own function with a string representing the location
of their file.  I didn't want to do that.  What will happen when i leave, and
a new machine needs a copy?

So you create a database property that stores the location of the
Excel file. Since it exists in each individual database front end,
each user can modify the value for himself and put the file wherever
he wants.

Macros? Hmm... never use 'em... too limited (as you're finding out.)

 

[bhicks11 via AccessMonster.com]

The problem seems to be that you want the user to manually do everything but
you want to take out and delete the file after or during the time the user
exports. I think you can do it all programmatically or have the user also
delete the file. I always figure out a process the involved clicking a
button, especially for activities that happen over and over. That involves
programming.

Bonnie
http://www.dataplus-svc.com

is there a way to grab the name of the file they import during the standard
"import" command?

I wish i could. Although i can capture their shortname and use that, the
filename would be of my choosing, not theirs. In this case, that's not

[quoted text clipped - 23 lines]

 

[Pwyd]

Love to. But its been years since i've used VBA, my coding skills in that
language are limited, i'd only taken one undergraduate course. I'd love to
use a modified import to have the user point to the location of the file,
store it, then later use that location to delete the original file and
replace it with the new one; however, i simply do not possess the coding
skill to do that anymore, and the command structure access includes isn't
malleable enough to build a work around with what it provides.

 

[bhicks11 via AccessMonster.com]

Only one undergraduate course - Wow!

I'm a self-taught high school drop out. Ha.

Bonnie
http://www.dataplus-svc.com

Love to. But its been years since i've used VBA, my coding skills in that
language are limited, i'd only taken one undergraduate course. I'd love to
use a modified import to have the user point to the location of the file,
store it, then later use that location to delete the original file and
replace it with the new one; however, i simply do not possess the coding
skill to do that anymore, and the command structure access includes isn't
malleable enough to build a work around with what it provides.
[quoted text clipped - 28 lines]

Macros? Hmm... never use 'em... too limited (as you're finding out.)

 

[Pwyd]

Nog. but i didn't use it in the intervening 8 or 9 years. I know java, c++,
and about a thousand more proprietary languages, but not VBA, anymore. I
can work with and debug already-written code, but i no longer have the
knowledge to write my own.

Only one undergraduate course - Wow!

I'm a self-taught high school drop out. Ha.

Bonnie
http://www.dataplus-svc.com

Love to. But its been years since i've used VBA, my coding skills in that
language are limited, i'd only taken one undergraduate course. I'd love to
use a modified import to have the user point to the location of the file,
store it, then later use that location to delete the original file and
replace it with the new one; however, i simply do not possess the coding
skill to do that anymore, and the command structure access includes isn't
malleable enough to build a work around with what it provides.

Perhaps i'm a bit slow, i don't see how that solves the problem, unless the
"kill" function call works differently -- the file always needs to be in the

[quoted text clipped - 28 lines]

Macros? Hmm... never use 'em... too limited (as you're finding out.)

 

[bhicks11 via AccessMonster.com]

If you want to get started I would suggest making a macro to do everything
you can in a simplified process - save the macro as a module (which will give
you the VBA code to start with) and then see what you need to add/change.
Use the Help in VBA and when you get stuck - come back here. By the way, to
edit your module you just click it in the module tab.

Bonnie
http://www.dataplus-svc.com

Nog. but i didn't use it in the intervening 8 or 9 years. I know java, c++,
and about a thousand more proprietary languages, but not VBA, anymore. I
can work with and debug already-written code, but i no longer have the
knowledge to write my own.

Only one undergraduate course - Wow!

[quoted text clipped - 16 lines]

 

[Pwyd]

actually, i wasn't aware you could do that. I know you can change queries
into SQL with the view settings, i didn't know you could just re-save your
macros, etc, as modules. Neat. let me give that a try.

If you want to get started I would suggest making a macro to do everything
you can in a simplified process - save the macro as a module (which will give
you the VBA code to start with) and then see what you need to add/change.
Use the Help in VBA and when you get stuck - come back here. By the way, to
edit your module you just click it in the module tab.

Bonnie
http://www.dataplus-svc.com

Nog. but i didn't use it in the intervening 8 or 9 years. I know java, c++,
and about a thousand more proprietary languages, but not VBA, anymore. I
can work with and debug already-written code, but i no longer have the
knowledge to write my own.

Only one undergraduate course - Wow!

[quoted text clipped - 16 lines]

Macros? Hmm... never use 'em... too limited (as you're finding out.)