Posts by HowardC

Re: Excel File Names do not appear until workbook selecled

I tried these settings, but when I hover over the taskbar, one workbook name is displayed. All the others appear as "Excel" I use a macro to open up several workbooks

It would be appreciated if you could assist. I am using office 2013 and Windows 10

Re: Excel File Names do not appear until workbook selecled

Thanks for the help, much appreciated

I am using Windows 10 and Office 2013

When I Open several workbooks at once and select the Excel Icon on the task bar, the workbooks appear as "Excel", but no names are displayed. When I click any of the workbooks showing as Excel only , the name will then display for the workbook selected

I would like to know how to overcome this, so that when opening several workbooks, the excel workbook names will appear

I have attached a sample of what the files look like when clicking on the excel icon on the task bar

This appears to be a Windows problem rather than an Office problem

It would be appreciated if someone could kindly assist me in resolving this

Re: Macro to Extract reminder dates into Outlook calender

Thanks for the reply. I created sub folder names in the calendar and code now works perfectly

Re: Macro to Extract reminder dates into Outlook calender

Thanks for the help. I have added the reference to outlook using the Tools/references menu. When running the macro, I get "the attempted operation failed. An object could not be found" and the code below is highlighted

Set subFolder = CalFolder.Folders(arrCal)

It would be appreciated if you could kindly assist me in resolving this

Re: Macro to Extract reminder dates into Outlook calender

Thanks for the code much appreciated

I have tried to use the code and get a compile error and the code below is highlighted

Dim olApp As Outlook.Application

I only amended Sheets("Sheet1").Select to Sheets("Outlook reminder").Select

It would be appreciated if could amend the code and advise

I have a spreadsheet containing reminder dates to renew certificates 21 working days before these are due, excluding weekends

I would like a macro to export these to Outlook Calendar and to update the Outlook Calendar

I have attached a sample of my workbook

I have also posted on Windowsecrets Website two days ago http://windowssecrets.com/foru…ates-into-Outlok-calender

It would be appreciated if someone could kindly assist me

Re: Formula Help to compare codes on two workbooks

Thanks for you input & advise

I have codes in Column F on a Report “Factory Report sample.1”and want to compare these to codes In col F in Creditors Report Sample.1 Where the codes in Col F on workbook “Factory Report sample.1” matches to Col F onworkbook Creditors Report Sample.1
I have set up a formula to show “In Stock” and where they do not match to show “not in stock”

I am not sure if this formula is showing the correct result

It would be appreciated if someone could assist me by setting up a formula thatwould yield the correct result

Re: Macro to move all files including Sub-Folders

Thanks for the help much appreciated

Re: Macro to move all files including Sub-Folders

Thanks for the reply

I have adapted the code as folows, but as C:\old Downloads already exists, no files and sub-folders are moved

It would be appreciated if you could kindly assist me

Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String


    FromPath = "C:\downloads"  '<< Change
    ToPath = "C:\Old downloads"    '<< Change
    'Note: It is not possible to use a folder that exist in ToPath


    If Right(FromPath, 1) = "\" Then
        FromPath = Left(FromPath, Len(FromPath) - 1)
    End If


    If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
    End If


    Set FSO = CreateObject("scripting.filesystemobject")


    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If


    If FSO.FolderExists(ToPath) = True Then
        MsgBox ToPath & " exist, not possible to move to a existing folder"
        Exit Sub
    End If


    FSO.MoveFolder Source:=FromPath, Destination:=ToPath
    MsgBox "The folder is moved from " & FromPath & " to " & ToPath


End Sub

I would like a macro to move all files in C:\downloads as well as the sub-folders to C:\Old Downloads

If a duplicate file exists, in C:\old downloads, it can be overwritten

It would be be appreciated if someone could kindly assist

Re: Copy CSV fles from Sub-foldes to folder

Thanks for the help, code works perfectly

I would like VBA code to copy CSV files from the sub-folders within C:\downloads to C:\downloads

For Eg within C:\downloads\workshop , C:\downloads\parts there are CSV files, which I would like copied to C:\Downloads using VBA

Your assistance is most appreciated

http://www.mrexcel.com/forum/e…b-folder-into-folder.html

Re: Attaching maximum of 10 files

Hi JohnThanks for your help. When running your macro, nothing happens i.e outlook is not activated for max 10 files to be attached per emailIt would be appreciated if you would test and amend codeMany thanksHoward

I have code which attaches all zip files in a folder as well as the sub-folders and emails these

I would like the code amended to attach a maximum of 10 zip files at a time i.e the first 10 zip files in the folder and sub-folder on the first email, the next 10 (if less then the balance) etc

It would be appreciated if someone could kindly amend the code

Sub CreateEmail()


    Dim outApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim Filename As String
    Set outApp = CreateObject("Outlook.Application")
    Set OutMail = outApp.CreateItem(0)


      strbody = "Hi " & Join(Application.Transpose(Range("D1:D5").Value)) & vbNewLine & vbNewLine
            strbody = strbody & "Attached Please find latest Management Account" & vbNewLine & vbNewLine
            strbody = strbody & "Regards" & vbNewLine & vbNewLine


    On Error Resume Next
    With OutMail
           .to = Join(Application.Transpose(Range("E1:E5").Value), ";")
        .CC = ""
        .BCC = ""
        .Subject = "Sales"
        .Body = strbody
        Path = "C:\downloads\"




        
    Dim fso, oFolder, oSubfolder, oFile, col As Collection
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set col = New Collection
    col.Add fso.GetFolder(Path)
    Do While col.Count > 0
        Set oFolder = col(1)
        col.Remove 1
        For Each oSubfolder In oFolder.SubFolders
            col.Add oSubfolder
        Next oSubfolder
        For Each oFile In oFolder.Files
          If InStr(1, oFile, "Backup", 1) > 0 Then
        
        Else
                       If CStr(oFile) Like "*.zip" Then
                .Attachments.Add CStr(oFile)
            End If
               End If
        Next oFile
    Loop
        .Display
    End With




    On Error GoTo 0
    Set OutMail = Nothing
    Set outApp = Nothing
    Set fso = Nothing
End Sub

I have code that allow me to attach a particular sheet from one workbook to outlook

The code works perfectly. However, I have several workbooks that have the same sheet name "Fixed assets Recon" that needs to be attached

I am usure how to amend the code so that the same sheet name can be attached from mseveral workbooks

It would be appreciated if someone could kindly assist

Sub SendFiles()
Application.DisplayAlerts = False


    Dim lCount As Long
    Dim vFilenames As Variant
    Dim sPath As String
    Dim lFilecount As Long
    Dim sFullName As String
    sPath = "C:\Fixed assets\"
    ChDrive sPath
    ChDir sPath
    vFilenames = Application.GetOpenFilename("Microsoft Excel files (.xls),.xls", , "Please select the file(s) to open", , True)
    If TypeName(vFilenames) = "Boolean" Then Exit Sub
    For lCount = LBound(vFilenames) To UBound(vFilenames)
        Workbooks.Open vFilenames(lCount)
        Sheets("Fixed assets Recon").Copy
               
        ActiveWorkbook.SaveAs Replace(vFilenames(lCount), "*.xls", ".xls") & ".xls", FileFormat:=xlExcel8
        
        
                        
        
               vFilenames(lCount) = ActiveWorkbook.FullName
                    For Each sht In Sheets(Array("Fixed assets Recon"))
        Sheets(sht.Name).UsedRange.Copy
        Sheets(sht.Name).Range("a1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Next
        Application.ScreenUpdating = True
        ActiveWorkbook.Close True
        Application.DisplayAlerts = True
        Application.CutCopyMode = True
    Next
    Mailfiles "[email protected]", vFilenames
        For lCount = LBound(vFilenames) To UBound(vFilenames)
Kill vFilenames(lCount)
Next
   ActiveWorkbook.Close False
End Sub


Sub Mailfiles(sMailAddress As String, vFiles As Variant)
    Dim oMailItem As Object
    Dim oOLapp As Object
    Dim lCt As Long


    Set oOLapp = CreateObject("Outlook.application")
    Set oMailItem = oOLapp.CreateItem(0)
    With oMailItem
        .To = sMailAddress
        .Subject = "Movement accounts"
        .body = .body & "Hi Carl"
                      .body = "Attached please find Fixed Asset Recons as at " & Format(Month(Date) - 1 & " " & Year(Date), "mmmm yyyy") & vbNewLine & vbNewLine
                      
                       
       
              
              .body = .body & "Regards" & vbNewLine & vbNewLine
.body = .body & "Howard"


           
                         
              
                For lCt = LBound(vFiles) To UBound(vFiles)
            .attachments.Add CStr(vFiles(lCt))
        Next
        .Display
        Set oOLapp = Nothing
        Set oMailItem = Nothing
    End With
   
      
End Sub

I posted on Mr Excel.com yesterday

http://www.mrexcel.com/forum/e…-mseveral-wqorkbooks.html

Re: Formula to compute ageing on Statement

Hi Luke

I will recheck, but think that my calc may be incorrect

Re: Formula to compute ageing on Statement

Hi Luke

Thanks for your reply.

I have attached two examples.One without payments. See my manual ageing below your calculation as well as one example with payments. for ageing purposes, the payments must go off against the oldest invoices and whatever balance is left against the outstanding invoices

Re: Formula to compute ageing on Statement

Hi Luke

Thanks very much for the help. Just one thing I forgot to mention. Credit notes must also be taken into account in the ageing and will be aged according to the date of the credit note , taking into account the date of the statement

It would be appreciated if you would kindly amend your formula to take this into account