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
-----Original Message-----
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).R ange
("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.
"jason" <jht@oneboxdotcom wrote in message
...
OK here is the first macro (find next record, add 1,
populate the cell, it also highlights a cell to red and
back):
Sub findnext()
'
' findnext Macro
' Macro recorded 8/8/2003
'
'
Workbooks.Open Filename:="S:\ISO9001-2000\CAR\CAR
Database.xls"
Sheets("CAR Database").Select
Range("A2").Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
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
ActiveWorkbook.Save
ActiveWindow.Close
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
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.
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.
-----Original Message-----
Jason,
Post the code that isn't working. And define what you
want it to do.
(your outline is good but leaves me a little
uncertain).
steve
"jason" <jht@oneboxdotcom wrote in message
...
Steve,
Thanks again, but this only saves the file.
Clicking
the
disk icon (save) or the file-save menu command gives
you
a box with radio buttons (Template file - save to
database) and allows you to create a new record,
update
existing recors, continue without updating.
I have recorded macros using those methods, and have
not
gotten the same box to come up. There is definitely
some
hidden command in the built in command that is not
happening in the macro. (ie the built in button
launches
a function, but the macro does not)
I have searched through the vb help, this board, ms
support, tried random commands in vb, and cannot
find
any
refernce to another command that causes
this "template
file - update to database"
It is now a matter of my stubbornness. I can easily
type
in "hey user, click the save button up there at the
top
now" but that doesn't follow the logic that I set up
for
this. I really want button, button, type, button,
button, all in a nice column.
Thanks again.
-----Original Message-----
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" <jht@oneboxdotcom wrote in message
...
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
-----Original Message-----
Jason,
Put this code into a standard module and
customize
your
button and assign
this macro.
Sub saver()
ActiveWorkbook.Save = True
End Sub
"jason" <jht@onebox wrote in message
...
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
-----Original Message-----
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
.
.
.
.
.