Posts by davisto

Hi Derk,

I'm still pulling what's left of my hair out over this issue. Setting Warnings = to false in access doesn't stop the overwrite screen from coming up. Stops all of the other annoying warnings though.
Any other words of wisdom are welcome
:o

How would I go about triggering the macro right from the Scheduler?

For Access it's simple enough, For example ...
"C: Program FilesMicrosoft OfficeOfficeMsaccess.exe" "C:My DocumentsWorkAutomation2Operator.mdb" /x Clear D_PDT Tables

The switch /x calls the macro perfectly.

Not so much in Excel.

Is it even possible?

Tos

You're right. It works just fine for Excel VBA (I just checked it too) but I'm calling the spreadsheet from an Access Module.
That means that at some point Access will try to create the file named ErrorReport.xls but see that it already exists in the directory. After I get past this Overwrite screen, Access will open Excel and then my ErrorReport will open up.

Is there a similar command in Access 97 VBA? I've been looking all over.

PS
After running a few trials I have discovered that sendkeys does not actually work.

Thanks for the tip.

I'm not actually finding Application.DisplayAlerts to be a VB command.

I'm using Access 97.

One thing I tried in the meantime is to use the SendKeys to send "enter" thus overwriting the document ...

I know, I know, that's a very bad thing to do but I was sinking in quicksand. I'm still open to suggestions though.

Thanks!
TD

I'm running a macro that saves a report as an excel spreadsheet. This macro is called by the task manager daily. (it's automated because I can't physically be there to run it myself)

Whenever the macro gets to the command that translates the report to an .xls a dialog pops up asking me if I would like to overwrite the existing file. Of course I would like to overwrite the existing file.

Please tell me that there is a macro command or a work around that will allow me to bypass this. I can't be there to press {OK} nor can I be there to delete the existing file from the directory everyday.

Thanks
TD

In the end I decided to go with this ...

Sub dateCheck()

'standardize the date column
'makes it easier to match criteria later
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"

'Dim datedigits As String
Dim A3 As Date

'G2 is an arbitrary cell. The entire column will be hidden later.
Range("G2") = Range("A3")

Dim Bcell As Range
For Each Bcell In Range("a3", "g500")
If Left(Bcell, 3) = Left(Range("G2"), 3) Then
Bcell.EntireRow.Interior.ColorIndex = 15
Bcell.EntireRow.Font.Bold = True
Bcell.EntireRow.Font.Size = 9

End If
Next Bcell

Columns("B:B").ColumnWidth = 9
Columns("G:G").Select
Selection.EntireColumn.Hidden = True
End Sub

I was asked to highlight everything that DOES NOT meet the criteria (ie not today's date) but for some reason, when I tried to use <> in the if statement EVERYTHING was highlighted.
I guess my boss will just have to settle with the opposite of what she asked for.

THanks again.

D

Thanks a lot guys!

I appreciate all of the help so far.

Thom thanks for the link. That should help me with my excel VBA.
= )

Looking for a little syntax help.

I'm writing an excel module that will scan each cell for a date. If the date is not equal to today's date then I would like to highlight the entire row grey.

So far I was thinking something along the lines of the follwing:
Because this is my first time doing vba (I was a java programmer) I will have to use psuedo code.

Sub todayStandsOut()
If Sheet1.(Range("Current Cell")) != date(today) Then
Rows("current row").Interior.ColorIndex = 15
End If
End Sub

Please help!
Also If anyone knows an excel vba command reference site or listing that would be helpful as well.

Thanks