Posts by fifijazz

  • Closing Excel With VBA

    Here you go


  • time macros

    Not sure if this is exactly what you are looking for, but what I do is use an auto_open macro in the workbook I want to run automatically each day. Then I use Windows Task Scheduler to open the workbook at 6am every morning. This will open a new instance of Excel, though, so if you have Excel already open, you could get error messages when Excel tries to re-open files in your XLStart folder. The way I handle this is that I save and close all open workbooks to temporary files and close Excel every day as follows:


    This is in my in Personal.xls ThisWorkbook:


    Private Sub Workbook_Open()
    'Application.OnTime TimeValue("5:00"), "My_Close"


    End Sub


    This is in a standard module within Personal.xls:


    Sub My_Close()
    Dim wb As Workbook
    Dim i As Integer



    i = 1
    Application.DisplayAlerts = False
    For Each wb In Workbooks

    If Left(wb.Name, 6) = "orders" Then
    wb.Close savechanges:=False
    End If
    wb.SaveAs Filename:="c:\TossOrKeep" & i & ".xls"

    i = i + 1

    Next


    Application.Quit
    Set wb = GetObject("C:\Documents and Settings\Myname\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS")
    Set wb = Nothing
    End Sub

  • Excel into Access

    Copy the used range of your worksheets then paste them into Access. You'll have to create the Joins manually, though. If you get Import Errors when you paste, most likely you have numerical entries in an alpha field or vice versa. If you have a field that contains text that some times looks like a number, make sure that the very first record contains an entry that is clearly text, e.g., use the record containing "12345AB" as the first record, not the record containing "23456".


    hth

  • Prevent Duplicates Using Data Validation

    After highlighting your input range, let's say A1:A500. Choose Data>Data Validation from the menu. On the Settings tab: Allow = Custom.


    In the formula box: =COUNTIF(A1,A$1:A$500)=1. (Watch the $ signs.) Type your warning message on the Error Alert tab.


    [rdv]*[/rdv]

  • Cell Display/Cell Format Assistance

    Don't know why you are having this problem summing your column, but the COUNTA function should give you the result you want. COUNTA "doesn't care" whether there the cell contents are text or numbers.


    As to your second question, you could use the IF function: something like
    =IF(SUM(monthly_Stat_range)=0,"",SUM(monthly_Stat_range))

  • Sending an email

    Here you go.



  • Read data from Closed .xls file

    You could use MS Query to get the data. You will have to name the database range in the source file. Then in the destination file, choose Data>Get External Data>New Database Query. You can let the query wizard create your query (easy), or do it manually (infinitely more flexible). If you've used Access at all, the process for building a query in MS Query will not be foreign to you.

  • Date Formulas

    If your quotes are in col a estimator in b, and date in col c, then:
    =SUMPRODUCT((MONTH(C5:C10)=8)*(B5:C10="Bill")) will count all of the estimates Bill did in August. If you want to total the estimates then:
    =SUMPRODUCT((MONTH(D5:D10)=8)*(C5:C10="Bill")*(A5:A10))

  • cell linking causing pop-up to occur

    For starters, go to Edit>Links and make sure it is set to automatic. Don't know why you are having to point to the file every time, but if you are linking to the file via an assigned drive letter, I would try going the long way round and use the full path name (\\fileserver\share\directory\myfile.xls).


    hth

  • Excel Calculation Needed

    Hope you are feeling better. This is probably not really what you meant, but if you truly want to return both the quantities and total values with just 8 formulas, you'll need to create a string something like: SUMPRODUCT((M8:M50="quarter")*(N8:N50>0)) & " discounted quarters totalling " & "£" & SUMPRODUCT((M8:M50="quarter")*(N8:N50>0)*(1-N8:N50)*(O8:O50))

  • Add Rows Columns to a Worksheet

    Click the Autosum button. (You should see a yellow Tooltip that says "AutoSum" when you are over the correct command button.) If you don't go to the Tools Menu, choose Options, click the general tab, check the Function tooltips box, click ok.

  • User friendly Macros?

    The only other way that I know of is to digitally sign the macro. Then the other users would still have to designate you as a "trusted source". Sorry, I'm not sure exactly how it works, having never tried it. Take a look at "Troubleshoot security and protection in Excel Help" if you want to evaluate this method.


    hth

  • 3 tables

    There is a nice example of this type of form in the Northwind sample database that comes with Access. (mine is located in C:\Program Files\Microsoft Office\Office10\Samples).


    As to your second question (if I understand it correctly), I would concatenate the name with whatever other field or fields will create a unique key field for searching.