Posts by Craig Ottley

Craig Ottley · Mar 19th 2007

Re: Calendar - Limiting Day Selection

try this Sandy:

Code

Private Sub Calendar1_Click()
    
Dim chDate As Date
     
     chDate = CDate(Calendar1.Value)
    
    'Test to see if calendar click is a monday
    If Weekday(chDate, vbMonday) = 1 Then 'if it is then carry on
        Range("V2").Select
        ActiveCell = Calendar1.Value
        ActiveCell.NumberFormat = "dd/mm/yy"
        frmSchStartDate.Hide


    Else
        'if not a monday then keep calendar open or which ever option you want
        MsgBox "please select a monday?"


    End If


End Sub

Display More

Craig Ottley · Mar 19th 2007

Re: Row Deletion Code, Error Message, Validation

Try This

Code

Sub DelItem()


Dim iRow As Long


iRow = Application.InputBox("Which Row Would you like to Delete?", "Row Delete", , , , , , 1)
    
    On Error GoTo ErrHandle
    
       Cells(iRow, 1).EntireRow.Delete
        
ErrHandle:


    Exit Sub
    
End Sub

Display More

Craig Ottley · Mar 19th 2007

Re: Result Conditional Upon Preceding Values

Is this simliar to buying and selling stock shares? finding high and lows in the buying price?

Craig Ottley · Mar 19th 2007

Re: Access Executable With Parameters

what exactly are you trying to execute?

Craig Ottley · Mar 19th 2007

Re: Access Executable With Parameters

hmmm you probably can try searching for DTS Tasks that work in Access i know its works in SQL but i haven't tried it in Access... i'll have a look at it over the next couple of days and i'll keep you posted...

the answer is most likely yes. but it'll be alot of work

Craig Ottley · Mar 19th 2007

Re: Run Access Macro

Try This

Code

Public strDB As String
Public A As Object


Public Function RunAccessMacro()
   
Application.ScreenUpdating = False


Set A = CreateObject("Access.Application")


    strDB = "C:\Data\Encrypted\TA.mdb"
        
        A.OpenCurrentDataBase strDB
            A.Visible = False
                A.DoCmd.RunMacro "LinkAndBuildTbls"
        A.Quit
        
Set A = Nothing
Application.ScreenUpdating = True
   
End Function

Display More

you'll probably need the references
Microsoft DAO 3.6 or above library

Craig Ottley · Mar 19th 2007

Re: Access Executable With Parameters

Not to sure what you mean by excutable? could you explain a bit more detail?

Craig Ottley · Mar 19th 2007

Re: Data Will Not Refresh

looks like there is no calculation set in that spread sheet.. Try putting this at the botton of your code

Code

Application.Calculation = xlCalculationAutomatic

this will turn on autmatic calculation

Craig Ottley · Mar 19th 2007

Re: Equals Left, Formula

The Easiest way i can think of is to convert text to columns...

Select the column with the names in then click on Data.. there you should see Text to Columns... select the Delimited Radio button and on the next screen tick Space... this will put the first name in one column and the second in another?

Craig Ottley · Mar 19th 2007

Re: Vlookup/ Match

i've inserted a VLOOKUP formula in the attached spreadsheet which will check the accounts...

this should get you rolling...

Craig Ottley · Mar 19th 2007

Re: Force Page Breaks In Vba?

How are you 'Pulling' your data from Access. If you query the database via a SQL Statement you could set two seperate queries one to pull through all your desired columns and the other to import required fields only... it would look something like this

Code

Option Explicit


Public cnnACC As ADODB.Connection
Public Rs As ADODB.Recordset
Public LnCountfield As Long, LnCount As Long
Public strDB As String
Public strConn As String


Public Function AccessToExcel()
   
    Dim Ws As Worksheet
    Dim WsA As Worksheet
    Dim lrange As Range
    Dim stSQL1 As String
    
    Application.ScreenUpdating = False
    
    Set cnnACC = New ADODB.Connection
    Set Rs = New ADODB.Recordset
    
    Set Ws = Sheets("Main")


    
    strDB = "\\ntch05\database.mdb"
    
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=" & strDB & ";"
  
    cnnACC.Open strConn
          
    stSQL1 = "SELECT tbl_raw.[Product],[Bkref],[User],[Gross Profit %age], [Net Nargin %age incl VAT ],[Gross price]" _
            & " FROM tbl_raw" _
            & " WHERE [Gross Profit %age] <> [Net Nargin %age incl VAT ] ;"


With Rs
     .Open stSQL1, cnnACC, adOpenStatic, adLockOptimistic
    '
End With


        
LnCountfield = Rs.Fields.Count


Sheets.Add.Name = "Agent"


Set WsA = Sheets("Agent")


For LnCount = 0 To LnCountfield - 1


Cells(1, LnCount + 1).Value = Rs.Fields(LnCount).Name


Next LnCount


WsA.Cells(2, 1).CopyFromRecordset Rs


    
    Rs.Close
    Set Rs = Nothing
    cnnACC.Close
    Set cnnACC = Nothing
    
    Set Ws = Nothing
    Set WsA = Nothing


Application.ScreenUpdating = True


End Function

Display More

that could be a better way of doing it?

Craig Ottley · Mar 19th 2007

Re: Force Page Breaks In Vba?

have you got a sample sheet?

Craig Ottley · Mar 19th 2007

Re: Hide All Calculations?

you can do this by selecting the cells you want to hide the formula. select format cells and then click on the protection tab... tick Hidden... you'll then have to protect your worksheet under Tools Protection--> protect sheet then tick on the box what you want the users to be able to do...

that should solve your problem

Craig Ottley · Mar 19th 2007

Re: Mail Merging For Invoices

there is a way you can do it using a microsoft reference method

Code

'Need Reference - Microsoft CDO for NTS 1.xx Library 


Sub SendMail(strTo, strFrom, strSubject, strCopy)


    Const cdoSendUsingPort = 25
    Const cdoBasic = 1
    Dim objCDOConfig As Object, objCDOMessage As Object
    Dim strSch As String, strAttach As String
    Dim objAttach As Object, objAttachColl As Object


    strSch = "http://schemas.microsoft.com/cdo/configuration/"
    Set objCDOConfig = CreateObject("CDO.Configuration")
    With objCDOConfig.Fields
        .Item(strSch & "sendusing") = cdoSendUsingPort
        .Item(strSch & "smtpserver") = "132.147.160.34"
        'Only used if SMTP server requires Authentication
        '.Item(strSch & "SMTPAuthenticate") = cdoBasic
        '.Item(strSch & "SendUserName") = "[email protected]"
        '.Item(strSch & "SendPassword") = "PasswordToGoHere"
        '.Update
    End With




    Set objCDOMessage = CreateObject("CDO.Message")
'    Set objAttach = objCDOMessage.Attachments.Add
    'Set objAttach = objAttachColl.Add '("test", 0, CdoFileData, "\\NTCH05" & Format$(Date, "ddmmmyy") & ".pdf") ' add an attachment
    
    With objCDOMessage
        Set .Configuration = objCDOConfig
        .From = Trim(strFrom)
        .Sender = Trim(strFrom)
        .To = Trim(strTo)
        .Subject = Trim(strSubject)
        .TextBody = Trim(strCopy)
        '.HTMLBody = Trim(strCopy)
        .Send
    End With
    Set objCDOMessage = Nothing
    Set objCDOConfig = Nothing
    
End Sub

Display More

then you build your mail by stringing together all your cells in a different sub

Code

Call SendMail("<<SEND TO>>", "<<SEND FROM>>", "<<SUBJECT LINE>>", <<message>>)

it's a long winded way of doing it but it works fine...

Craig Ottley · Mar 19th 2007

Re: Sorting Data From Olap Pivot Table

to the best of my Knowledge you can not sort OLAP cubes in excel, i've had to create macros to get the data out of the cube and then process the sorting...

Craig Ottley · Mar 19th 2007

Re: Extracting Date Related Records In Pivot Table

you can do this as a Cube... all you need to do is assign other Fields to your data and then you can update the Cube by selecting your Variables...

i've attached an example to work off... hope this helps

Craig Ottley · Mar 19th 2007

Re: Filter Rows Based On Multiple Criteria

i take it every time you change the date in Cell A5 you want the sheet to auto update it self?

Craig Ottley · Mar 19th 2007

Re: End Of Line Charachter (unix) Csv Import

It would be better to handle this in Access... you can step through each field in each record checking all 600,000 rows

Craig Ottley · Mar 19th 2007

Re: Search For Text In A Specific Column

Code

Set rCl = rToSearch.finf(sFind, LookIn:=xlValues)

Change finf to find

Code

Set rCl = rToSearch.find(sFind, LookIn:=xlValues)

it also makes a different if your using a different version of excel.

if your using xls 2003 the format is different and you'll have to add this

Code

rtoSearch.Find(What:=sFind, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , SearchFormat:=False).Activate

Craig Ottley · Mar 19th 2007

Re: Handling Error In Vb

Try this

Code

Const MyDirLoad As String = "C:\LH_JV_Upload_Expense_Recognition_.xls"


Public Sub copyTo()
    
    Application.ScreenUpdating = False


    Dim copyFrom, copyTo As Workbook
    Dim lSheet1 As Worksheet
    Dim nextRow As Long
    
    Set copyFrom = ThisWorkbook
    Set copyTo = Workbooks.Open(MyDirLoad)
        Set lSheet1 = copyFrom.Sheets("sheet1")
        Set lsheet2 = copyTo.Sheets("sheet2")
    
            lSheet1.Activate
            
                Range("H26:H60").Copy
            
            lsheet2.Activate
            
                 nextRow = lsheet2.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row
            
            Cells(nextRow, 5).Select
            
                  ActiveSheet.Paste
            
            Application.CutCopyMode = False 'Clears clipboard
  
    copyTo.Close True 'Save changes = yes
    
    Set copyFrom = Nothing
    Set copyTo = Nothing
        Set lSheet1 = Nothing
        Set lsheet2 = Nothing
            
    Application.ScreenUpdating = True
    
End Sub

Display More