force upate to datbase using save as

  • Thread starter Thread starter jason
  • Start date Start date
J

jason

I am trying to implement a button on a form (used
template wizard to link form to an excel list/database)
that will both save as "myfilename" and also force
the "create new record". So far, I can get it to save,
but the only way it will pop up the "template file - save
to database" input is by using the standard toolbar save
button. A save or save as macro will not bring up the
box.

I would also like it to pick the "create new record"
radio button, and click the "ok" box.

Is there a way to do this?

If not is there a way to make my button act exactly like
the standard toolbar button?

btw- I created a macro by clicking the std save toolbar
button, but it does not act the same, ie, I get no update
database.

tia
jason
 
I am still looking for this answer. I have found info on
forms, but still can't get the darn thing to update.

I am obviously inexperienced with programming, so I would
even take a hint at how to make a custom button act
exactly the same as the std toolbar save button.

Anyway thanks again.
Jason
 
Thanks steve, but that returned a compile error -
expected function or variable, and the .save = is
highlighted.

Here is some more detail:
using excel 2000. I used template wizard to create a
user input spreadsheet. Each new record needs a
sequential number assigned. The list/database holds all
of the info for all the records, but each record is also
saved as its own file. Creating and updating an
individual record needs to update the list/database.

the workflow is:
-open the template for a new blank input sheet.
-clicks the first button to find the last record in the
database add 1, paste the number in the input sheet
-click second button and the worksheet is saved as the
number from the previous step
-enter data unique to the record
-click a button to update the list/database ***not
working***
-email the the spreadsheet with the unique data to a
responsible party (or email a link to the file on the
server)
-responsible party adds info, then clicks a button to
save to the orig. file and updtae the database (not
implemented yet).

Like I said before using the save button from the std
toolbar causes the database update window to open, but
coded saves do not.

Thanks again for any suggestions.
Jason
 
Jason,

My ouch... should be

Sub saver()
ActiveWorkbook.Save
End Sub

You don't need the "= True" on the end.

If you had used
ActiveWorkbook.Close = True
than you need the = True.
Change it to False and you close without saving.

steve

Try recording a macro and see if that works.
 
Jason,

Most of your code looks pretty good. But I have added some questions marked
with ?? and comments !!

(watch out for word wrap!!!!!!!!!!!!!!!)

Do you have Option Explicit at the top of you module(s). This helps force
Excel to compile you code and locate problems. It is also a good idea to
step through the code (use F8) and see what is happening line by line.

I am still confused as to where the data is entered and stored. Which
workbook is which? (pardon my denseness, I am the kind that needs to "touch
& feel")

Now you got me hooked, so let's keep going with this.

steve

Sub findnext()
'
' findnext Macro
' Macro recorded 8/8/2003
'
Workbooks.Open Filename:="S:\ISO9001-2000\CAR\CAR Database.xls"
Sheets("CAR Database").Select


' ?? What range are you sorting? Looks like a single cell.

' !! Also you can shorten it to Range("A2").Sort and get rid of the select

Range("A2").Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

' !! again, get rid of select

' note that if the copy range is more than one cell, you still only need to
specify Range("G4")

Range("A2").Copy _

Destination:=Workbooks("QAD8001.xls").Sheets(1).Range("G4").PasteSpecial
Paste:= _ Paste:=xlPasteValues


' ?? Not sure what is going on here but you can use the above idea.
Range("A2").Select
Selection.Copy
Windows("QAD8001").Activate
Range("G4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Windows("CAR Database.xls").Activate
Application.CutCopyMode = False

' !! you can replace these 2 lines with

ActiveWindow.Close = True


ActiveWorkbook.Save
ActiveWindow.Close


' !! not sure which book you are working on here (guess it is QAD8001)

' !! again you don't need to select

With Range("F1")

..Interior.ColorIndex = 3
..Font.Bold = True

End With


Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B4").Select

End Sub




The next macro saves the input sheet to a filename that
equals the record number:

Sub updatesave()
'
' updatesave Macro
' Macro recorded 8/8/2003
'

ChDir "S:\ISO9001-2000\CAR\CAR Forms-completed"
Dim SaveName As String
SaveName = ActiveSheet.Range("b4").Text
ActiveWorkbook.SaveAs Filename:= _
"S:\ISO9001-2000\CAR\CAR Forms-completed\" & _
SaveName & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Range("B5").Select
Range("F1").Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
Range("F1").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = 33
Range("B5").Select


End Sub


?? Can you give anything more than "won't work"?

Other than that, I have the update macro, which won't
work. I have tried activworkbook.save, .saveas, and some
other things I can't remember now.


?? Do you mean that the data doesn't get pasted/added?


The thing that will not happen with my coded saves is the
update to the database (where the data from the named
cells gets added to the database spreadsheet as a new
record or an update to an existing record). The actual
saving of the file is fine.

I have recorded new macros using the default menu, and
toolbar buttons. When I record the macro, I get
the "update" feature, but when I play the macro, it just
does a simple save, with no "update" feature.

Thanks again (steve, I really appreciate you sticking
with me on this). If I can add any other details, let me
know.
 
Ok steve, you are a trooper. Here goes:

The poo poo code is because it was built using the macro
recorder. I really don't know better, and it works, so I
left it alone.

QAD8001.xls is the spreadsheet that is created from the
template QAD800.xlt

QAD800.xlt is tied to CAR Database.xls using the template
wizard. It has cells for data input that tie into the
appropriate columns in CAR Database.xls

When you open the the template (file-new-QAD800) it
creates an .xls called QAD8001.xls You then enter data
on this spreadsheet. Without any code or macros, if you
click the default save, save as, or close--you get
the "save template file to database" pop-up box (and it
also opens CAR Database.xls in the background). If you
click the "add record" radio button (and OK) it will add
a line to CAR Database.xls with the data that is in the
named/linked fields (from QAD8001.xls).

This is the desired result - add a record.

Also desired is to save that instance of QAD8001.xls as a
seperate file with the CAR# as the file name. This will
let other users (and myself) add data in the future, add
pictures, etc., still update the required fields to the
database, and not overwrite other records/spreadsheets.

Without the save as 1234.xls I will get gobs of
QAD8001.xls's or QAD80011.xls, QAD800111.xls, etc.

So, on to the code:

No, I have no option explicit clauses. But, all of the
code I posted here works fine. I am not trying to debug
any of it. Sorry for the confusion.

The find next macro:
The range I selected (using macro recorder) was a2..o2002
it sorts on column A (which is a unique CAR number
20030001, 20030002, 20030003, etc.) descending - so that
the highest number is at the top.

Then it copies that number to QAD8001, pastes it.
QAD8001 adds 1 to the number and pastes it in the CAR
field. So every time I run the macro, I get the last
number used and create a new number for the new record.
(BTW-if the "update template to database" doesn't occur,
the new number is not put back into CAR Database.xls)


After all that, I jump back to CAR Database.xls, to close
it out. I really did not want to save it after sorting,
but I didn't want a "yes, no, cancel" to come up when you
tried to close it without saving.

At this point you only have QAD8001.xls open.

The 2nd macro works fine. It takes the CAR# and does a
save as with that number as the file name. I now have a
series of files named 2003001.xls, 2003002.xls,
2003003.xls, etc.

Each of those files represents 1 row of data in CAR
database.xls.

When you open 2003002.xls and change or add data, the hit
the std toolbar save button the "update template to
database" window pops up. If you select update record,
it puts the current info into the 2003002 row of CAR
database.xls.

When you open the same file and use a macro (simply
activeworkbook.save) it does not update any info in CAR
Database.xls you merely get a saved 2003002.xls.

This is the crux of my problem. When I say "It doesn't
work" in my earlier post, I mean my attempts at creating
a macro that performs --exactly-- the same functions as
the std built in toolbar save button are unsuccessful.



Steve, I hope some of what I say here is understandable.
I don't want you to give up in frustration because I am
not communicating properly. From my seat, it is very
easy to know what I want, because I have been at this for
about 3 weeks (and before that with access data web
pages, and prior to that with plain access).

The bottom line is I need an easy way to input, then
email a corrective action, then get a response and
comment on it, all the while maintaining both a database
of all the records, and each individual record as a
seperate file.

I will be happy to elaborate further on anything and
everything...
Thanks,
Jason
 
Jason,

Not frustrating - challenging!

Now lets comment on your comments and see where we can go.
See my comments within your comments...
(read all the way down to "end==============")
The poo poo code is because it was built using the macro
recorder. I really don't know better, and it works, so I
left it alone.
Click to expand...
Recording is great but usally adds more than you need.
Especially with the select stuff. You'll do yourself a big favor by
changing those per my previous emails.
QAD8001.xls is the spreadsheet that is created from the
template QAD800.xlt

QAD800.xlt is tied to CAR Database.xls using the template
wizard. It has cells for data input that tie into the
appropriate columns in CAR Database.xls

When you open the the template (file-new-QAD800) it
creates an .xls called QAD8001.xls You then enter data
on this spreadsheet. Without any code or macros, if you
click the default save, save as, or close--you get
the "save template file to database" pop-up box (and it
also opens CAR Database.xls in the background). If you
click the "add record" radio button (and OK) it will add
a line to CAR Database.xls with the data that is in the
named/linked fields (from QAD8001.xls).

This is the desired result - add a record.
Click to expand...
Is this working? If not we can make some simple code to copy data to the
database. Sounds like you only want to copy a single line, correct me if
I'm wrong.
Also desired is to save that instance of QAD8001.xls as a
seperate file with the CAR# as the file name. This will
let other users (and myself) add data in the future, add
pictures, etc., still update the required fields to the
database, and not overwrite other records/spreadsheets.

Without the save as 1234.xls I will get gobs of
QAD8001.xls's or QAD80011.xls, QAD800111.xls, etc.
Click to expand...
From your previous posts it sounds like this isn't happening. It should be
straight forward to build in a save-as into the code and extract the name
from anywhere in the sheet.
So, on to the code:

No, I have no option explicit clauses. But, all of the
code I posted here works fine. I am not trying to debug
any of it. Sorry for the confusion.
Click to expand...
Strongly recommend Option Explicit as a general rule. It helps catch typo's
and other stuff.
The find next macro:
The range I selected (using macro recorder) was a2..o2002
it sorts on column A (which is a unique CAR number
20030001, 20030002, 20030003, etc.) descending - so that
the highest number is at the top.

Then it copies that number to QAD8001, pastes it.
QAD8001 adds 1 to the number and pastes it in the CAR
field. So every time I run the macro, I get the last
number used and create a new number for the new record.
(BTW-if the "update template to database" doesn't occur,
the new number is not put back into CAR Database.xls)


After all that, I jump back to CAR Database.xls, to close
it out. I really did not want to save it after sorting,
but I didn't want a "yes, no, cancel" to come up when you
tried to close it without saving.
Click to expand...
Workbook("CAR Database.xls").Close = False
should do this
At this point you only have QAD8001.xls open.

The 2nd macro works fine. It takes the CAR# and does a
save as with that number as the file name. I now have a
series of files named 2003001.xls, 2003002.xls,
2003003.xls, etc.

Each of those files represents 1 row of data in CAR
database.xls.

When you open 2003002.xls and change or add data, the hit
the std toolbar save button the "update template to
database" window pops up. If you select update record,
it puts the current info into the 2003002 row of CAR
database.xls.

When you open the same file and use a macro (simply
activeworkbook.save) it does not update any info in CAR
Database.xls you merely get a saved 2003002.xls.
Click to expand...
This sounds normal. You'll need to call up an update function if you want
to change the database. But here you have me confused because you said
earlier that you want to close the database without saving???
This is the crux of my problem. When I say "It doesn't
work" in my earlier post, I mean my attempts at creating
a macro that performs --exactly-- the same functions as
the std built in toolbar save button are unsuccessful.



Steve, I hope some of what I say here is understandable.
I don't want you to give up in frustration because I am
not communicating properly. From my seat, it is very
easy to know what I want, because I have been at this for
about 3 weeks (and before that with access data web
pages, and prior to that with plain access).

The bottom line is I need an easy way to input, then
email a corrective action, then get a response and
comment on it, all the while maintaining both a database
of all the records, and each individual record as a
seperate file.
Click to expand...

Suggest that you build a number of macros. Have each macro just do a small
portion of what you want. Than we can make sure each module works. Once
that is a go, it is just a matter to call what you want when you want it.
I will be happy to elaborate further on anything and
everything...
Thanks,
Jason
Click to expand...

Now here's my interpretation of what you want.

Open the template
Add data to the template. (One line, or many???)
Save-As using a Car # from cell A1(?) for the name
Copy the data to the master database.
Save (?) the database.
email something to someone.

Get back to me

steve

end=================================================
 
Steve,

I'll start from the summary:

my comments have ****


Now here's my interpretation of what you want.
Open the template
***Yes***

Add data to the template. (One line, or many???)
Click to expand...

***The template has 15 data entry points that correspond
to columns in the database. They are arranged in a
single column on the template*****
Save-As using a Car # from cell A1(?) for the name
Click to expand...

***yes, kind of. sort database-descending, pull top
value of col A paste to template. Add 1 to value, paste
in cell B4 of template. save as file name = B4***
Copy the data to the master database.
Click to expand...

***In my mind, this is not really a "copy". Somehow,
excel is tracking which record is associated with which
template file. For example, I have created a record and
the car# is 20030005. I created another record and the
car# is 20030006. If I open up the template file for
20030005 and change the number to 20030006, it will not
over write the existing 20030006. You end up with 2
records that have 20030006 car#'s with all of their other
data remaining intact.

This step is really an update to the database record or
the creation of a new record, definitely NOT a "copy"
function.
Save (?) the database.
Click to expand...

***yes-sort of. the process of updating the database
seems to be- open, write record, save, close. This all
happens in the background (you can see CAR database open
for a couple of seconds, then close) as a part of
the "update template file to database" operation.***
email something to someone.
Click to expand...

***yes. it can be either a link to the 2003xxxx.xls file
on the network, or the file itself.****


On to the code. I will try out what you suggest, as far
as selection, option explicit, and the like.


My code works, but it does not work how I want it to work.

I want it to save the file (this works)
I want it to update the database (this does not work)

I want my code to perform exactly the same set of
functions that the built in toolbar buttons perform.

ie- with 1 click of the std toolbar save button, I get a
save AND an update.

(when the above is recorded as a marco in "my button")-1
click gets me just a save.

This is my big problem. There is some underlying/hidden
functionality that the MSEXCEL save button offers that
does not get recorded in the macro (when you record that
button being pushed)

My quest is to find that command:

activeworkbook.updatetemplatefiletodatabase

Steve thanks again. I will have to pick this up in the
morning tomorrow, as it is time for me to go home. If I
have time tonight, I will see if you have replied.

##############################################end########
 
Jason,

a.. Sounds like 15 lines (records) in a single column.
***The template has 15 data entry points that correspond
to columns in the database. They are arranged in a
single column on the template*****
Click to expand...


a.. Sounds straight forward
1.. Sort database
2.. Workbooks(template).Sheets(1).Range("B4").Value = _
Workbooks(database).Sheets(1).Range("A1).Value + 1

3.. Workbooks(template).SaveAs Filename:= _
Sheets(1).Range("B4").Value & ".xls"

Code is rough and needs to be amended to your needs.
Save-As using a Car # from cell A1(?) for the name

***yes, kind of. sort database-descending, pull top
value of col A paste to template. Add 1 to value, paste
in cell B4 of template. save as file name = B4***
Click to expand...


a.. This is confusing. If the data first goes into the template, than it
needs to
be transferred to the database. This is usually a form of copy. Unless
there
is something else happening that automatically updates the database.
Copy the data to the master database.

***In my mind, this is not really a "copy". Somehow,
excel is tracking which record is associated with which
template file. For example, I have created a record and
the car# is 20030005. I created another record and the
car# is 20030006. If I open up the template file for
20030005 and change the number to 20030006, it will not
over write the existing 20030006. You end up with 2
records that have 20030006 car#'s with all of their other
data remaining intact.

This step is really an update to the database record or
the creation of a new record, definitely NOT a "copy"
function.


***yes-sort of. the process of updating the database
seems to be- open, write record, save, close. This all
happens in the background (you can see CAR database open
for a couple of seconds, then close) as a part of
the "update template file to database" operation.***


***yes. it can be either a link to the 2003xxxx.xls file
on the network, or the file itself.****


On to the code. I will try out what you suggest, as far
as selection, option explicit, and the like.


My code works, but it does not work how I want it to work.

I want it to save the file (this works)
Click to expand...
b.. Need to have a closer look at your code.
I want it to update the database (this does not work)

I want my code to perform exactly the same set of
functions that the built in toolbar buttons perform.
Click to expand...

c.. There must be some formulas or code behind the workbook
if you are also getting an update with the save.
ie- with 1 click of the std toolbar save button, I get a
save AND an update.

(when the above is recorded as a marco in "my button")-1
click gets me just a save.
Click to expand...

d.. There isn't any underlying functionality behind the save, except
maybe a calculate (you can build that into your code). So unless
it is more than the calculate, than there must be some more code
some where. An event macro in ThisWorkbook maybe.
This is my big problem. There is some underlying/hidden
functionality that the MSEXCEL save button offers that
does not get recorded in the macro (when you record that
button being pushed)

My quest is to find that command:

activeworkbook.updatetemplatefiletodatabase
Click to expand...
Maybe you can send the workbooks to me via email (not to the group)
and I can get an up close and personal feel to this. (Hopefully it will
get
past any firewall they might have here.
steve
 
Back
Top