Replies

John,

This Workbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    TestShowSave
End Sub

Private Sub Workbook_Open()
    TestHideSave
End Sub

Module1

Sub TestHideSave()
    Dim FileMenu As CommandBarControl
    Set FileMenu = CommandBars(1).FindControl(ID:=30002)
    Application.OnKey "^{s}", ""
    FileMenu.Controls("&Save").Visible = False
End Sub

Sub TestShowSave()
    Dim FileMenu As CommandBarControl
    Set FileMenu = CommandBars(1).FindControl(ID:=30002)
    Application.OnKey "^{s}"
    FileMenu.Controls("&Save").Visible = True
End Sub

Brent

John,

Place these in the This Workbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    TestShowSave 
End Sub

Private Sub Workbook_Open()
    TestHideSave
End Sub

These would go in a module:

Sub TestHideSave()
    Dim FileMenu As CommandBarControl
    Set FileMenu = CommandBars(1).FindControl(ID:=30002)
    FileMenu.Controls("&Save").Visible = False
End Sub

Sub TestShowSave()
    Dim FileMenu As CommandBarControl
    Set FileMenu = CommandBars(1).FindControl(ID:=30002)
    FileMenu.Controls("&Save").Visible = True
End Sub

You still need to trap the Ctrl + S key combo.

This worked in a module but when I placed the code in the This Workbook mod it had a fit so I put the code back in a seperate mod and call them from the This Workbook. I have to go help someone move…I will check back tomorrow to see how it is going.

you would write a macro disabling the desried command and place it in the workbook open event and then a macro to restore excel and place that one in the workbook close event.

both of these sites are full of excellent information on all things excel.

http://www.cpearson.com/excel.htm
http://www.j-walk.com/ss/excel/index.htm

Brent

I would be inclined to think that it would be a matter of memory available as opposed to a limit on macros in a workbook or sheet.

I hang my head in shame…what a dufus.

Thank you for your response Legare.

…never mind. sheeezzzzz

    With TextBox2
        .SetFocus
        .SelStart = 0
        .SelLength = Len(TextBox2.Text)
    End With

This line was at the bottom of the Excel 2000 Help item on watermarks:

Background patterns do not print and are not retained in individual worksheets or items that you save as Web pages. However, if you publish an entire workbook as a Web page, the background is retained.

this will open up the print preview window.

ActiveDocument.PrintPreview

I am not sure if this would help.

I don’t wish to pose any copywrite issues, so in the VBE – Help – Answer Wizard tab type in Ambiguous Name Detected and you will get a response of “Avoiding Naming Conflicts”

Hope this helps.

YEHAW!!!

Thanks to Wassim and Brook for your suggestions. I hacked and searched and hacked some more. Ripped hair out and searched and read and hacked till I was sure there was no way out. Then I tweaked a bunch of different examples that I found and came up with this:

Sub SampleADO()
    Dim cnConn As ADODB.Connection
    Dim rsRecd As ADODB.Recordset
    Dim rgTargetCell As Range
    Dim stConn As String
    Dim sADOCopyTest As String

    sADOCopyTest = "C:WINDOWSDesktopWorkADO test.xls"
    
    stConn = "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & sADOCopyTest
    Set cnConn = New ADODB.Connection
    
    cnConn.Open stConn
    Set rsRecd = cnConn.Execute("[TestRange]")
    Set rgTargetCell = ActiveCell
    
    rgTargetCell.CopyFromRecordset rsRecd
            
    rsRecd.Close
    cnConn.Close
    Set rsRecd = Nothing
    Set cnConn = Nothing
End Sub

It may not be perdy but it WORKS!

I have tried at least 5 different routines that I found on the net and in books and I keep getting the same error.
I searched for and located the odbcjt.32.dll on my machine.
I have the driver and the file name / path was copied from the properties window.

ayyeeeeeeeeeee I cant take it anymore…….

Thanks for the response and the link. I don’t have 2.5 on my rig though. I have found some information on a couple of sites that I am going to review…

Wassim,

I have been working on this in between meetings and have not made too much progress. This is the code I am running:

Sub TestADORetrieval()
    GetData "C:WINDOWSDesktopWorkADO test.xls", "Missed Sch Adj", & _
	"AL4missADJ", Sheet1.Cells(1, 4), False
End Sub

' reference set to Microsoft ActiveX Data Objects 2.1 Library
Private Sub GetData(SrcFile$, SrcSheet$, SrcRange$, rTgt As Range, fHdr As Boolean)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim a&
    Dim cnct$
    
    
    '   Initialize a variable for the connection string
    cnct$ = "Driver={Microsoft Excel Driver(*.xls)};DBQ=" & SrcFile$
    
    '   Initialize connection object
    Set cn = New ADODB.Connection
    
    With cn
        '   Open the database connection
        .Open cnct$

I am not too very familiar wtih ADO and am at a loss. I have tried inserting “.Provider=MSDASQL;” in the connection string ahead of the driver arg, tried to create a DSN and have accomplished nothing. When the line in red runs I get this error message:

Application.Dialogs(xlDialogPrint).Show

duhooohhh

Fixed up the print code from work.