Replies

Legare,

The macro executes from a button on the active sheet.

If I modify your code to remove the trailing backslash, the file is named 2001_02.xls under the G:Daily Operating ReportTable Games folder. Otherwise, the code still bombs at “ActiveWorkbook.SaveAs (sFName)” even with the message box.

The error message “file could not be accessed” indicates possible problems with 1) folder does not exist, 2) folder not read-only, 3) There are not illegal char in filename and 4) path not longer than 218 char.

Legare,
At first I tried
=CONCATENATE(RIGHT(AP23,2),IF(AP19<10,CONCATENATE(0,AP19),AP19),IF(AQ21<10,CONCATENATE(0,AQ21),AQ21),"Tables")
but when that didn't work, I changed it to "dly_TG_rpt" as a test.
I'm trying to achieve a date format with text in a filename, like "yymmddTables.xls". The if() should handle a date less than ten and add a leading zero. With this format the files should sort very nicely in our directory. (We save everything forever).
Thx again for your time spent with this one.

Legare,
Thanks for all the time you’ve spent on this. Sorry it took so long to get back to you. Here’s the code. The macro copies a sheet to a new file, converts the formulas to values and the saves the file with the default filename email_Dly_TG_Rpt.xls (commented out here).

Sub Extract_Dly_TG_rpt_for_email()
‘
‘ Extract_Dly_TG_rpt_for_email Macro
‘ Macro recorded 4/17/00 by Bob Buckley-CP Fin Plan
‘

‘
ActiveSheet.Previous.Select
Sheets(“Daily Table Games Report”).Select
Sheets(“Daily Table Games Report”).Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(“A1”).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(“L2”).Select
Application.CutCopyMode = False
ChDir “G:Daily Operating ReportTable Games2001_02”
‘ ActiveWorkbook.SaveAs FileName:= _
‘ “G:Daily Operating ReportTable Games!_email_Dly_TG_Rpt.xls”, FileFormat:= _
‘ xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:= _
‘ False, CreateBackup:=False

Dim sFName As String
sFName = “G:Daily Operating ReportTable Games2001_02” & Range(“Ap18”)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (sFName)
Application.DisplayAlerts = True

End Sub

Legare,
THX for such a timely response. I still need your help so don’t leave just yet. I inserted your code into an existing macro, which simply saves the new sheet to a default name. I placed your code just below the instruction to change directory:
ChDir “G:Daily Operating ReportTable Games2001_02”.
I substituted the full path (including closing backslash) and populated the cell reference with a formula to concatenate a date abbreviation. But the code halts at the SaveAs (with or without DisplayAlerts) and produces an error message. I know that the folder exists, and that it is not read only. The filename does not contain illegal characters and the file/path is less than 218 characters.
Any suggestions?

Is Zone Alarm useful for pc’s with merely a 56k modem? Seems like much of the discussion (here and elsewhere) is around cable and DSL.

To: Rory
I just tried the ScreenPointer = 11, but get an error message. I moved ScreenPointer to the start of the macro BEFORE Application.DisplayAlerts = False and Application.ScreenUpdating = False. I get an “object required” error and the ScreenPointer = 11 is highlighted in the VBEditor. What do you suggest I should try next.

To: gwhitfield
This looks like what I should be using instead of poking around from one row to the next, switching ranges left and right. The macro takes a standardized report and trims it 15 different ways to be mailed to various departments. Occassionally, additional row(s) are added (or subtracted), causing a rewrite to the macro. I’ll experiment with rngFrom/rngTo once I get this mod checked out. Bet it is faster, too. Thx.

Thanks ScreenUpdating was just what I needed. Would you provide an example of how to use StatusBar? I am looking to alert a user that a macro is running in the background.

They are windows. The macro I’ve recorded steps through copy-paste values, deletion/hiding of rows, print range selection, previews, page-break previews, normal views and so on.
I’ve already added DisplayAlerts=False so the user isn’t required to hit Enter 15 (or so) times to overwrite all the files created by the macro.
I have no experience with Dialog boxes and none have been included here. Eventually, I would like to have one here informing the user, but that’s not as important yet as turning the display off.