'' ''####### ''# ## # # ##### ## #### ##### # #### ''# # # ## # # # # # # # # # ''##### # # # # # # # # #### # # # ''# ###### # # # # ###### # # # # ''# # # # ## # # # # # # # # # ''# # # # # # # # #### # # #### '' ''###### ### ### ''# # ##### #### #### ##### ## # # ### ### ''# # # # # # # # # # # # ## ## ### ### ''###### # # # # # # # # # # ## # # # ''# ##### # # # ### ##### ###### # # ''# # # # # # # # # # # # # ### ### ''# # # #### #### # # # # # # ### ### '' Dim fext As String ''Press any key to continue . . . 'Procedure to launch Sub FileListRec() TOGscrnUP SDeleter Dim msg, Style, Title, Help, Ctxt, Response, MyString Dim sStartPath As String '' ''Set S = sStartPath = ThisWorkbook.Worksheets("Files").Range("DIR_SC").Value '2020 vintage fext = ThisWorkbook.Worksheets("Files").Range("Ffilter").Value ''folderspec = S.Range("f_dir").Value If sStartPath = "" Then Exit Sub msg = "Do you want to search " & vbCrLf & sStartPath ' Define message. Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Searching Subfolders is this OK" ' Define topic ' context. ' Display message. Response = MsgBox(msg, Style, Title) If Response = vbYes Then ' User chose Yes. MyString = "Yes" ' Perform some action. Application.StatusBar = "Starting" 'nothing to do let it roll on Else ' User chose No. MyString = "No" ' Perform some action. Application.StatusBar = "Exiting" Clear_SB2011 Exit Sub End If HeaderFileListR_F (sStartPath) 'First step ShowFileListR_F (sStartPath) Columns("a:g").AutoFit [a1].AutoFormat Format:=xlRangeAutoFormatList3, Number:=True, Font:= _ True, Alignment:=True, Border:=True, Pattern:=True, Width:=True srow = realRows(ActiveSheet) Cells(srow + 2, 8).Value = "Done " & Now ActiveWindow.Zoom = 60 TOGscrnUP Clear_SB2011 MsgBox "Filesearch end", , Now End Sub Sub HeaderFileListR_F(folderspec) ThisWorkbook.Sheets.Add namecol = 2 typecol = 3 sizecol = 4 datecol1 = 5 lnamecol = 1 Cells(1, 8).Value = "Directory " & folderspec Cells(1, lnamecol) = "fullname" Cells(1, namecol) = "name" Cells(1, typecol) = "type" Cells(1, sizecol) = "size" Cells(1, datecol1) = "date" End Sub Sub ShowFileListR_F(folderspec) 'Extend'Extend'Extend On Error GoTo errorRep 'n type size date Dim fs, f, f1, fc, s ulogXV folderspec & ",RF" Debug.Print folderspec Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.Files 'Dim dDir As Folder For Each dDir In f.SubFolders ShowFileListR_F (dDir.path) ' Here is the recursion Next namecol = 2 typecol = 3 sizecol = 4 datecol1 = 5 lnamecol = 1 srow = realRows(ActiveSheet) For Each f1 In fc 'iff1.Type ="whatever in f_Type" then do stuff below<<<< 11-1-12 If LCase(f1.Name) Like "*" & fext & "*" Then 'set to * very time we fire up! srow = srow + 1 Cells(srow, lnamecol) = f1.path Cells(srow, namecol) = f1.Name Cells(srow, typecol) = f1.Type Cells(srow, sizecol) = f1.Size & " bytes." Cells(srow, datecol1) = f1.DateCreated 'not good for hidden/sys files? End If Next Exit Sub errorRep: If Err.Number = 70 Then Exit Sub MsgBox Err.Description & "( " & Err.Number & ")" [Af].Value = Err.Description & "( " & Err.Number & ")" Set fs = Nothing Set f = Nothing Set fc = Nothing End Sub 'I 've prepare an example to you (with FSO). This is a recursive function, which returns last found file. It quite simple and you can use this code: 'Procedure to launch Sub FileSearch() Exit Sub Dim sStartPath As String Dim sWhat As String Dim result As String sStartPath = "S:\Chris Wright\" 'Where? sWhat = "KPI_sup.xlsm" 'What? result = DigIn(sStartPath, sWhat) 'First step MsgBox result End Sub Function DigIn(sPath As String, sWhat As String) As String Exit Function Dim fs As New FileSystemObject Dim dDirs As folder Dim dDir As folder Dim fFile As file Set dDirs = fs.GetFolder(sPath) For Each dDir In dDirs.SubFolders DigIn = DigIn(dDir.path, sWhat) ' Here is the recursion Next For Each fFile In dDirs.Files If fFile.ShortName = sWhat Then ' File checking DigIn = fFile.path Exit Function ' You can remove that if you don't want to wait for all files End If Next End Function 'DHL utilities 'DHL utilities ' still time to do more for people AND get paid for it 'screensaver primative Sub SP() '14/08/2019 09:43:26 Dim cbook As Workbook Set cbook = Workbooks.Add [A8].Activate For iw = 1 To 3 ActiveCell.Offset(0, 1).Activate newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 3 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Next cbook.Close False End Sub 'shell ("explorer G:\Finance\MIS\Joint Reporting\Prods") Sub ListEnvironVariables() Dim strEnviron As String Dim i As Long For i = 1 To 255 strEnviron = Environ(i) If LenB(strEnviron) = 0& Then Exit For Debug.Print strEnviron Next End Sub 'new usename / compname ' WScript.Echo "Hello" ' WScript.Echo ' ' Set objNet = WScript.CreateObject("WScript.Network") ' WScript.Echo "Your Computer Name is " & objNet.computerName ' WScript.Echo "Your Username is " & objNet.username Function getUsername() As String 'Hallo getUsername = "" 'sieh dir das mal an... 'Der Anmeldename am Netzwerk getUsername = Environ("UserName") ''der computerName ''= Environ("ComputerName") '''Der Name der in Excel eingetragen ist '''= Application.UserName End Function Function getComputername() As String 'Hallo getComputername = "" 'sieh dir das mal an... 'Der Anmeldename am Netzwerk getComputername = Environ("ComputerName") End Function '' ''Attribute VB_Name = "modUpdateData" ''Option Explicit Sub Update_Data_SQL_test() ''Attribute Update_Data_SQL.VB_ProcData.VB_Invoke_Func = "U\n14" '================================================================================================ 'procedure builds information for recreating DSN-Free connection for data sources in 'Data' tab 'and sends the queries to SQL Server to retrieve data '================================================================================================ Const PROC_NAME As String = "Update Data SQL" Dim WKB As Workbook Dim eScreenUpd As Boolean Dim eEvents As Boolean Dim eCalcs As Long Dim strStartDate As String Dim strEndDate As String Dim strFinYear As String Dim strPeriod As String Dim strEndDateLessToday As String Dim strRepWeek As String Dim strConnName As String Dim strServerName As String Dim strUsername As String Dim strPassword As String Dim strSQL As String Dim strMsg As String Dim strStatus As String '' On Error GoTo ErrorHandler With Application eScreenUpd = .ScreenUpdating eEvents = .EnableEvents eCalcs = .Calculation .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With Set WKB = ThisWorkbook 'set financial year variable With WKB.Sheets("Settings") strFinYear = "2019/20" ''.Range("cell_FinYear") '=== START REBUILDING AND REFRESHING DATA SOURCES ============================ '=== 'Settings' Tab ========================================================== '--- First Day Of Fin Year---------------------------------------------------- strConnName = "First_Day_Of_Fin_Year" strServerName = "GBDSCWSSC0249" strUsername = "webapps" strPassword = "webapps" strSQL = "SELECT TOP 1 " strSQL = strSQL & vbNewLine & " [The_Date] " strSQL = strSQL & vbNewLine & "FROM [MC_Definitions].[dbo].[Time] " strSQL = strSQL & vbNewLine & "WHERE [MC_Year] = '" & strFinYear & "' " strSQL = strSQL & vbNewLine & "ORDER BY [The_Date] " '' If ADDConnection(WKB, strConnName, strServerName, strUsername, strPassword, strSQL, strStatus) = False Then '' strMsg = strMsg & "|" & strConnName & "| " & strStatus & vbNewLine '' End If '----------------------------------------------------------------------------- ' '--- Bank Holidays ----------------------------------------------------------- strConnName = "Bank_Holidays" strServerName = "GBDSCWSSC0249" strUsername = "webapps" strPassword = "webapps" strSQL = "SELECT [The_Date] AS 'Site Closed Days','BH' AS 'BH' " strSQL = strSQL & vbNewLine & "FROM [MC_Definitions].[dbo].[Time] " strSQL = strSQL & vbNewLine & "WHERE [UK_Bank_Holiday] = 1 AND [MC_Year] = '" & strFinYear & "';" If ADDConnection(WKB, strConnName, strServerName, strUsername, strPassword, strSQL, strStatus) = False Then strMsg = strMsg & "|" & strConnName & "| " & strStatus & vbNewLine End If '--- Bank Holidays ----------------------------------------------------------- strConnName = "CWswipes" strServerName = "GBDSCWSSC0249" strUsername = "webapps" strPassword = "webapps" strSQL = "SELECT *" strSQL = strSQL & vbNewLine & "FROM [MC_MISReports].[dbo].[NET2_InOut_Register] " strSQL = strSQL & vbNewLine & " where [Name] like 'Wright%' order by [in] desc ;" If ADDConnection(WKB, strConnName, strServerName, strUsername, strPassword, strSQL, strStatus) = False Then strMsg = strMsg & "|" & strConnName & "| " & strStatus & vbNewLine End If End Sub Public Function ADDConnection(WKB As Workbook, ConnName As String, SQL_ServerName As String, SQL_Username As String, _ SQL_Password As String, SQL_Statement As String, Optional ProcStatus As String) As Boolean '================================================================================================ 'Function purpose is to use existing ODBC connection by its name and rebuild it to be DSN-Free. 'SQL Server login and password needs to be use and SELECT statement can be dynamically re-apply. 'Author: Slawomir Walczynski 'Date Created: 13/04/2016 'ADDing connect version testing 2019 Chris Wright '================================================================================================ Const PROC_NAME As String = "Add Connection" Dim connectSTR As String Dim CommandType As String Dim CommandText As String On Error GoTo ErrorHandler connectDESCR = "test adding connX" connectSTR = "ODBC;" & _ "DRIVER=SQL Server;" & _ "SERVER=" & SQL_ServerName & ";" & _ "UID=" & SQL_Username & ";" & _ "PWD=" & SQL_Password CommandType = xlCmdSql CommandText = SQL_Statement WKB.Connections.Add ConnName, connectDESCR, connectSTR, CommandText, CommandType ADDConnection = True CleanExit: Exit Function ErrorHandler: If Not IsMissing(ProcStatus) Then ProcStatus = "'" & PROC_NAME & "' Error (" & Err.Number & "): " & Err.Description Debug.Print ProcStatus End If ADDConnection = False Resume CleanExit End Function 'https://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/ _ So, the final program should look like this: Sub sbADOExample() Dim sSQLQry As String Dim ReturnArray Dim Conn As New ADODB.Connection Dim mrs As New ADODB.Recordset Dim DBPath As String, sconnect As String DBPath = ThisWorkbook.FullName 'You can provide the full path of your external file as shown below 'DBPath ="C:\InputData.xlsx" ' wrong connection strings' wrong connection strings' wrong connection strings' wrong connection strings' wrong connection strings' wrong connection string sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';" 'If any issue with MSDASQL Provider, Try the Microsoft.Jet.OLEDB: 'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" Conn.Open sconnect sSQLSting = "SELECT * From [Sheet1$]" ' Your SQL Statement (Table Name= Sheet Name=[Sheet1$]) mrs.Open sSQLSting, Conn '=>Load the Data into an array 'ReturnArray = mrs.GetRows ''OR'' '=>Paste the data into a sheet Sheet2.Range("A2").CopyFromRecordset mrs 'Close Recordset mrs.Close 'Close Connection Conn.Close End Sub Sub CreateToolbars() Dim CustBar As CommandBar Dim CustButton1, CustButton2, CustButton3, CustButton4 Dim x, y '-- If the toolbar already exists, then delete existing For Each CustBar In Application.CommandBars If CustBar.Name = "Reconciler Tools" Then CustBar.Delete End If Next '--Create First Toolbar Set CustBar = Application.CommandBars.Add("Reconciler Tools") '--Add buttons to toolbar. With Application Set CustButton1 = CustBar.Controls _ .Add(Type:=msoControlButton) With CustButton1 .FaceId = 643 .Caption = "&Reformat Register Data" .OnAction = "ProcessRawRegister" .Style = 3 ' msoButtonIconAndCaption End With Set CustButton2 = CustBar.Controls _ .Add(Type:=msoControlButton) With CustButton2 .FaceId = 129 .Caption = "&Copy Both Datasets To Reconciler" .OnAction = "BounceDataToReconciler" .Style = 3 ' msoButtonIconAndCaption End With Set CustButton3 = CustBar.Controls _ .Add(Type:=msoControlButton) With CustButton3 .FaceId = 37 .Caption = "Reset Filters" .OnAction = "ResetFilters" .Style = 3 ' msoButtonIconAndCaption End With ' Set CustButton4 = CustBar.Controls _ ' .Add(Type:=msoControlButton) ' With CustButton4 ' .FaceId = 264 ' .Caption = "&Admin" ' .OnAction = "ValidateSDAdmin" ' .Style = 3 ' msoButtonIconAndCaption ' End With CustBar.Visible = True CustBar.Position = msoBarFloating End With End Sub Sub DestroyToolbars() Dim CustBar As CommandBar For Each CustBar In Application.CommandBars If CustBar.Name = "Reconciler Tools" Then CustBar.Delete 'ElseIf CustBar.Name = "Admin Tools" Then ' CustBar.Delete End If Next End Sub Sub tile_grid() Dim ch As Shape chcount = ActiveSheet.Shapes.Count ' howmany charts ? 'take chart1 as anchor chart and we dont touch it l = ActiveSheet.Shapes(1).Left t = ActiveSheet.Shapes(1).Top h = ActiveSheet.Shapes(1).Height w = ActiveSheet.Shapes(1).Width ' SET all charts same as C1 For Each ch In ActiveSheet.Shapes ch.Width = w ch.Height = h Next 'desired number of charts accross (8 for ODRL) tw_limit = 8 ' squar-ish array? tw_limit = 4 '3 values + boxpolt for examine routine 'BME 'initalise tile positions ==> 0-based tw_limit = Int(Sqr(chcount)) trow = 0 tcolumn = 0 s = 2 ' start with chart2 Do While s < chcount + 1 'expect to run out of charts tcolumn = tcolumn + 1 If tcolumn = tw_limit Then tcolumn = 0 trow = trow + 1 End If With ActiveSheet.Shapes(s) 'placing chart .Left = l + tcolumn * 1.1 * w .Top = t + trow * 1.1 * h End With s = s + 1 ' keep overall count going Loop End Sub Sub tiles_intoPP() Dim ch As Shape chcount = ActiveSheet.Shapes.Count ' howmany charts ? Dim ppa As New PowerPoint.Application 'ppa As PowerPoint.Application Set pps = ppa.NewPresentation 'take chart1 as anchor chart and we dont touch it l = ActiveSheet.Shapes(1).Left t = ActiveSheet.Shapes(1).Top h = ActiveSheet.Shapes(1).Height w = ActiveSheet.Shapes(1).Width ' SET all charts same as C1 For Each ch In ActiveSheet.Shapes ch.Width = w ch.Height = h ''''''''''''''''''''''''''pns = pps. Next Exit Sub 'desired number of charts accross (8 for ODRL) tw_limit = 8 ' tw_limit = Int(Sqr(chcount)) ' squar-ish array? tw_limit = 3 ' 'initalise tile positions ==> 0-based trow = 0 tcolumn = 0 s = 2 ' start with chart2 Do While s < chcount + 1 'expect to run out of charts tcolumn = tcolumn + 1 If tcolumn = tw_limit Then tcolumn = 0 trow = trow + 1 End If With ActiveSheet.Shapes(s) 'placing chart .Left = l + tcolumn * 1.1 * w .Top = t + trow * 1.1 * h End With s = s + 1 ' keep overall count going Loop End Sub Sub tilesmash() Exit Sub '''casc s on last shape'''lays them in a line chcount = ActiveSheet.Shapes.Count For s = 1 To 9 ind = s * 4 - 1 ActiveSheet.Shapes("Picture " & s * 4 - 1).Delete Next End Sub ''dos calls '' Microsoft Windows [Version 6.1.7601] '' Copyright (c) 2009 Microsoft Corporation. All rights reserved. '' '' C:\Users\Christopher.Wright\Documents>dir '' Volume in drive C is WIN 7 '' Volume Serial Number is 56BB-BA7B '' '' Directory of C:\Users\Christopher.Wright\Documents '' '' 09/06/2016 17:06 . '' 09/06/2016 17:06 .. '' 06/06/2016 09:19 Add-in Express '' 08/06/2016 15:34 20,941 Book1.xlsx '' 1 File(s) 20,941 bytes '' 3 Dir(s) 370,838,491,136 bytes free '' '' C:\Users\Christopher.Wright\Documents>md housekeepingCdrive '' '' C:\Users\Christopher.Wright\Documents>dir '' Volume in drive C is WIN 7 '' Volume Serial Number is 56BB-BA7B '' '' Directory of C:\Users\Christopher.Wright\Documents '' '' 10/06/2016 10:33 . '' 10/06/2016 10:33 .. '' 06/06/2016 09:19 Add-in Express '' 08/06/2016 15:34 20,941 Book1.xlsx '' 10/06/2016 10:33 housekeepingCdrive '' 1 File(s) 20,941 bytes '' 4 Dir(s) 370,835,324,928 bytes free '' '' C:\Users\Christopher.Wright\Documents>h: '' '' H:\Housekeeping>copy *.* c: '' NGtimes.xlsx '' Backup of k5_uty.xlk '' Backup of NGtimes.xlk '' k5_uty.xlsm '' KK_codebank.xlsm '' SSCRPresentation1.pptx '' CWdiskfiles.xlsx '' calendar4.xls '' 8 file(s) copied. '' '' H:\Housekeeping>c: '' '' C:\Users\Christopher.Wright\Documents>dir '' Volume in drive C is WIN 7 '' Volume Serial Number is 56BB-BA7B '' '' Directory of C:\Users\Christopher.Wright\Documents '' '' 10/06/2016 10:33 . '' 10/06/2016 10:33 .. '' 06/06/2016 09:19 Add-in Express '' 09/06/2016 16:44 5,662,126 Backup of k5_uty.xlk '' 10/06/2016 09:47 401,920 Backup of NGtimes.xlk '' 08/06/2016 15:34 20,941 Book1.xlsx '' 10/06/2016 09:57 616,960 calendar4.xls '' 07/06/2016 12:22 128,399 CWdiskfiles.xlsx '' 10/06/2016 10:33 housekeepingCdrive '' 09/06/2016 17:12 5,662,162 k5_uty.xlsm '' 21/08/2015 10:20 1,008,990 KK_codebank.xlsm '' 10/06/2016 10:11 401,920 NGtimes.xlsx '' 07/06/2016 16:13 31,779 SSCRPresentation1.pptx '' 9 File(s) 13,935,197 bytes '' 4 Dir(s) 370,821,390,336 bytes free '' '' C:\Users\Christopher.Wright\Documents>del *.* /p '' C:\Users\Christopher.Wright\Documents\Backup of k5_uty.xlk, Delete (Y/N)? y '' C:\Users\Christopher.Wright\Documents\Backup of NGtimes.xlk, Delete (Y/N)? y '' C:\Users\Christopher.Wright\Documents\Book1.xlsx, Delete (Y/N)? y '' C:\Users\Christopher.Wright\Documents\calendar4.xls, Delete (Y/N)? y '' C:\Users\Christopher.Wright\Documents\CWdiskfiles.xlsx, Delete (Y/N)? y '' C:\Users\Christopher.Wright\Documents\k5_uty.xlsm, Delete (Y/N)? y '' C:\Users\Christopher.Wright\Documents\KK_codebank.xlsm, Delete (Y/N)? y '' C:\Users\Christopher.Wright\Documents\NGtimes.xlsx, Delete (Y/N)? y '' C:\Users\Christopher.Wright\Documents\SSCRPresentation1.pptx, Delete (Y/N)? y '' '' C:\Users\Christopher.Wright\Documents>cd housekeepingCdrive '' '' C:\Users\Christopher.Wright\Documents\housekeepingCdrive>h: '' '' H:\Housekeeping>copy *.* c: '' NGtimes.xlsx '' Backup of k5_uty.xlk '' Backup of NGtimes.xlk '' k5_uty.xlsm '' KK_codebank.xlsm '' SSCRPresentation1.pptx '' CWdiskfiles.xlsx '' calendar4.xls '' 8 file(s) copied. '' '' H:\Housekeeping>^A 'Shell "cmd.exe /k dir C:\Users\Christopher.Wright\Documents ", vbNormalFocus ''d = Shell "cmd.exe /k copy H:\Housekeeping\*.* C:\Users\Christopher.Wright\Documents\housekeepingCdrive ", vbNormalFocus Sub dos_backupNG() 'one command and out 10/06/2016 16:11:23 d = Shell("cmd.exe /c copy H:\Housekeeping\*.* C:\Users\Christopher.Wright\Documents\housekeepingCdrive ", vbNormalFocus) d = Shell("cmd.exe /c copy C:\Users\Christopher.Wright\Documents\workCDRIVE\*.* C:\Users\Christopher.Wright\Documents\work1", vbNormalFocus) 'Shell "cmd.exe /c cd C:\Users\Christopher.Wright\Documents\housekeepingCdrive", vbNormalFocus ' Shell "cmd.exe /c cd H:\Housekeeping\*.*", vbNormalFocus End Sub '''shcts 2 dir names?? 'C:\Users\Christopher.Wright\Desktop\SHORTCUTS>type *.lnk > merges_shortcut.txt 'enc 3600 Type drop ' Define user-defined type. Name As String * 1024 End Type Type drop2500 ' Define user-defined type. Stuff As String * 2500 End Type Type drop3600 ' Define user-defined type. p1 As String * 600 p2 As String * 600 p3 As String * 600 p4 As String * 600 p5 As String * 600 p6 As String * 600 End Type Sub CPfileLP2() Dim myrecord As drop2500 Dim myout As drop2500 Dim SourceFile As String ThisWorkbook.Save SourceFile = ThisWorkbook.FullName d$ = afterChar(SourceFile, "\") 'destfile = "e:\SSL\" & d$ 'destfile = "C:\Documents and Settings\User\My Documents\Chris\cw library\" _ & "Library_backup_" & d$ destfile = ThisWorkbook.Sheets("macrodata").[backupLX1].Value destfile = ThisWorkbook.FullName & ".360" Debug.Print SourceFile; " "; destfile ''On Error Resume Next Open SourceFile For Random As #1 Len = Len(myrecord) Open destfile For Random As #2 Len = Len(myout) RecordNumber = 0 Do Until EOF(1) RecordNumber = RecordNumber + 1: If RecordNumber > 100000 Then Exit Do Get #1, RecordNumber, myrecord chunk2500 = myrecord.Stuff 'myrecord.Stuff = chunk2500 ' Create a string of blanks. Get #2, RecordNumber, myout myout.Stuff = chunk2500 ' Create a string of blanks. Put #2, RecordNumber, myout ' Write record to file. Loop Close #2 ' destfile Close Close #1 ' sourcefile Close MsgBox SourceFile & vbCrLf & destfile End Sub Sub CPfileLP() Dim myrecord As drop2500 Dim myout As drop2500 Dim SourceFile As String ThisWorkbook.Save SourceFile = ThisWorkbook.FullName d$ = afterChar(SourceFile, "\") 'destfile = "e:\SSL\" & d$ 'destfile = "C:\Documents and Settings\User\My Documents\Chris\cw library\" _ & "Library_backup_" & d$ destfile = ThisWorkbook.FullName & ".360" '.ThisWorkbook.Sheets("Files").[backupLX1].Value Debug.Print SourceFile; " "; destfile ''On Error Resume Next Open SourceFile For Random As #1 Len = Len(myrecord) Open destfile For Random As #2 Len = Len(myout) RecordNumber = 0 Do Until EOF(1) RecordNumber = RecordNumber + 1: If RecordNumber > 100000 Then Exit Do Get #1, RecordNumber, myrecord chunk2500 = myrecord.Stuff 'myrecord.Stuff = chunk2500 ' Create a string of blanks. Get #2, RecordNumber, myout myout.Stuff = chunk2500 ' Create a string of blanks. Put #2, RecordNumber, myout ' Write record to file. Loop Close #2 ' destfile Close Close #1 ' sourcefile Close MsgBox SourceFile & vbCrLf & destfile End Sub Sub blot_file() Dim myrecord As drop dt$ = "f:\test2011.txt" On Error Resume Next Open dt$ For Random As #1 Len = Len(myrecord) corex = 0 Do Until EOF(1) corex = corex + 1: If corex > 100000 Then Exit Do Get #1, corex, myrecord m$ = myrecord.Name Loop For RecordNumber = 1 To corex myrecord.Name = String(1024, 167) ' Create a string of blanks. Put #1, RecordNumber, myrecord ' Write record to file. Next RecordNumber Close #1 ' file Close End Sub Sub CPfileLP_gen() Dim myrecord As drop2500 Dim myout As drop2500 Dim SourceFile As String SourceFile = [Af].Value d$ = afterChar(SourceFile, "\") destfile = "g:\Documents\" & d$ destfile = "H:\housekeeping" & d$ 'sainsburys version" destfile = "g:\" & d$ 'JCI version" Debug.Print SourceFile; destfile ''On Error Resume Next Open SourceFile For Random As #1 Len = Len(myrecord) Open destfile For Random As #2 Len = Len(myout) RecordNumber = 0 Do Until EOF(1) RecordNumber = RecordNumber + 1: If RecordNumber > 100000 Then Exit Do Get #1, RecordNumber, myrecord chunk2500 = myrecord.Stuff 'chunk2500 = FrameIt(chunk2500) 'myrecord.Stuff = chunk2500 ' Create a string of blanks. Get #2, RecordNumber, myout myout.Stuff = chunk2500 ' Create a string of blanks. Put #2, RecordNumber, myout ' Write record to file. Loop Close #2 ' destfile Close Close #1 ' sourcefile Close MsgBox SourceFile & vbCrLf & destfile End Sub Sub maketest1() Dim myrecord As drop3600 'Dim sourcefile As String 'sourcefile = [nf].Value d$ = "216.txt" 'afterchar(sourcefile, "\") destfile = "g:\Documents\" & d$ 'Debug.Print sourcefile; destfile ''On Error Resume Next ' Open sourcefile For Random As #1 Len = Len(myrecord) Open destfile For Random As #2 Len = Len(myrecord) 'RecordNumber = 0 For sx = 1 To 6 With myrecord .p1 = String(600, "1") .p2 = String(600, "2") .p3 = String(600, "3") .p4 = String(600, "4") .p5 = String(600, "5") .p6 = String(600, "6") End With Put #2, sx, myrecord ' Write record to file. Next Close #2 ' destfile Close MsgBox destfile & " Done" End Sub Sub CPfileLP_gen_C6() '''''''''''''''''''''' Dim myrecord As drop3600 Dim myout As drop3600 Dim SourceFile As String SourceFile = [Af].Value d$ = afterChar(SourceFile, "\") c6destdoc = "g:\Documents\" c6destdoc = "C:\Documents and Settings\cwrighc6\Desktop\" 'jci version destfile = c6destdoc & d$ & ".c6" Debug.Print SourceFile; destfile ''On Error Resume Next Open SourceFile For Random As #1 Len = Len(myrecord) Open destfile For Random As #2 Len = Len(myout) RecordNumber = 0 Do Until EOF(1) RecordNumber = RecordNumber + 1: If RecordNumber > 1000000 Then Exit Do Get #1, RecordNumber, myrecord Get #2, RecordNumber, myout 'odds cycle Forward myout.p3 = myrecord.p1 myout.p5 = myrecord.p3 myout.p1 = myrecord.p5 'evens cycle back myout.p6 = myrecord.p2 myout.p2 = myrecord.p4 myout.p4 = myrecord.p6 Put #2, RecordNumber, myout ' Write record to file. Loop Close #2 ' destfile Close Close #1 ' sourcefile Close MsgBox SourceFile & vbCrLf & destfile '& " C6" End Sub Sub filesplitter_Sain() 'filesplitting fun Dim myrecord As drop2500 Dim myout As drop2500 Dim SourceFile As String Dim RecordNumber As Long Dim OutRecordNumber As Long Dim OutFileLimit As Long Dim FileCount As Integer ThisWorkbook.Activate Sheets("Macrodata").Activate SourceFile = [Af].Value d$ = afterChar(SourceFile, "\") 'destfile = "g:\Documents\" & d$ destfile = "H:\housekeeping\" & d$ 'sainsburys version" destfile = "C:\Users\chwright.ISSREGION\" & d$ 'ISS version" Debug.Print SourceFile; destfile ''On Error Resume Next Open SourceFile For Random As #1 Len = Len(myrecord) Open destfile & ".CNW00" For Random As #2 Len = Len(myout) Debug.Print SourceFile & vbCrLf & destfile & FileCount RecordNumber = 0 FileCount = 0 OutFileLimit = Int(1000000 / Len(myout)) OutRecordNumber = 0 Do Until EOF(1) RecordNumber = RecordNumber + 1: If RecordNumber > 10000000 Then Exit Do If RecordNumber - FileCount * OutFileLimit > OutFileLimit Then 'file is full next file 'close current file Close #2 ' destfile Close 'inc FileCount FileCount = FileCount + 1 Open destfile & ".CNW" & Format(FileCount, "00") For Random As #2 Len = Len(myout) ' zero ofc OutRecordNumber = 0 Debug.Print SourceFile & vbCrLf & destfile & Format(FileCount, " 00") End If Get #1, RecordNumber, myrecord 'take fromold record chunk2500 = myrecord.Stuff OutRecordNumber = OutRecordNumber + 1 Get #2, OutRecordNumber, myout myout.Stuff = chunk2500 ' Put #2, OutRecordNumber, myout ' Write record to file. Loop Close #2 ' destfile Close Close #1 ' sourcefile Close End Sub Sub files__Joiner_Sain() 'filesplitting fun Dim myrecord As drop2500 Dim myout As drop2500 Dim SourceFile As String Dim savefirstINfilename As String Dim RecordNumber As Long Dim InRecordNumber As Long Dim OutFileLimit As Long Dim FileCount As Integer Splits_dir = "H:\housekeeping\" Splits_dir = "C:\Users\chwright.ISSREGION\" '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< HARMInDER ::CHANGE JUST THIS LINE !! Splits_ext = "*.CNW*" d$ = Dir(Splits_dir & Splits_ext) savefirstINfilename = d$ FileCount = 0 Do Until d$ = "" FileCount = FileCount + 1 d$ = Dir Loop destfile = beforeChar(savefirstINfilename, ".") Open Splits_dir & destfile For Random As #2 Len = Len(myout) For ifile = 0 To FileCount - 1 SourceFile = Splits_dir & destfile & ".CNW" & Format(ifile, "00") Open SourceFile For Random As #1 Len = Len(myrecord) ''On Error Resume Next Do Until EOF(1) RecordNumber = RecordNumber + 1: If RecordNumber > 10000000 Then Exit Do InRecordNumber = InRecordNumber + 1 Get #1, InRecordNumber, myrecord 'take fromold record chunk2500 = myrecord.Stuff Get #2, RecordNumber, myout myout.Stuff = chunk2500 ' Put #2, RecordNumber, myout ' Write record to file. Loop 'finshed one file InRecordNumber = 0 Close #1 ' sourcefile Close Next Close #2 ' destfile Close End Sub 'immmediat 2018 '12/07/2016 16:39:26 'Debug.Print Date '12/07/2016 '? activesheet.Name 'Debug.Print ActiveWorkbook.Path 'C:\Users\Christopher.Wright\Documents\housekeepingCdrive 'Debug.Print format(Now,"dddd dd mmmm yyyy hh:mm") 'Debug.Print format(Now,chr$(39)&"dddd dd mmmm yyyy hh:mm") '''''? format(now,"re\m dddd dd mmmm yyyy :- hh:mm") ''? activeworkbook.FullName 'debug.print "application.quit" 'ActiveWorkbook.Close 'ActiveWorkbook.Close False 'Workbooks.Close 'selection.value=selection.value 'selection.entirerow.delete 'selection.Replace What:=".",Replacement:="/" '? application.WorksheetFunction.Pi ' ? activecell.value Sub FLazycoder() 'too far ?? ... line = "Debug.Print format(Now," & Chr$(34) & _ Chr$(39) & "dddd dd mmmm yyyy hh:mm" & Chr$(34) & ")" Debug.Print line End Sub Sub Nowcoder() 'better Debug.Print Format(Now, "'dddd dd mmmm yyyy hh:mm") End Sub Sub morejunk() Calculate 'NB calcs allbooks ThisWorkbook.Save ActiveWorkbook.Save End Sub Sub saveMEjunk() ThisWorkbook.Save ThisWorkbook.Close False End Sub Sub awbjunk() For Each w In Workbooks If w.Name <> ThisWorkbook.Name Then w.Close savechanges:=True End If Next w End Sub ' -================================= ' JW tests Function FileExists(Fname) As Boolean ' Returns TRUE if the file exists cw change On Error GoTo outf: FileExists = False Dim x As String x = Dir(Fname) If x <> "" Then FileExists = True outf: End Function Function FileNameOnly(pname) As String ' Returns the filename from a path/filename string Dim i As Integer, length As Integer, temp As String length = Len(pname) temp = "" For i = length To 1 Step -1 If Mid(pname, i, 1) = Application.PathSeparator Then FileNameOnly = temp Exit Function End If temp = Mid(pname, i, 1) & temp Next i FileNameOnly = pname End Function Function PathExists(pname) As Boolean ' Returns TRUE if the path exists Dim x As String On Error Resume Next x = GetAttr(pname) And 0 If Err = 0 Then PathExists = True _ Else PathExists = False End Function Function RangeNameExists(nname) As Boolean ' Returns TRUE if the range name exists Dim N As Name RangeNameExists = False For Each N In ActiveWorkbook.Names If UCase(N.Name) = UCase(nname) Then RangeNameExists = True Exit Function End If Next N End Function Function SheetExists(sname) As Boolean ' Returns TRUE if sheet exists in the active workbook Dim x As Object On Error Resume Next Set x = ActiveWorkbook.Sheets(sname) If Err = 0 Then SheetExists = True _ Else SheetExists = False End Function Function WorkbookIsOpen(wbname) As Boolean ' Returns TRUE if the workbook is open Dim x As Workbook On Error Resume Next Set x = Workbooks(wbname) If Err = 0 Then WorkbookIsOpen = True _ Else WorkbookIsOpen = False End Function '''''''''''''''''''''''''''''' 'KK utys Private Declare Function getComputername Lib "kernel32" _ Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long Private Declare Function getUsername Lib "advapi32.dll" _ Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Public Function NameOfComputer() As String Dim computerName As String Dim ComputerNameLen As Long Dim result As Long ComputerNameLen = 256 computerName = Space(ComputerNameLen) result = getComputername(computerName, ComputerNameLen) If result <> 0 Then NameOfComputer = Left(computerName, ComputerNameLen) Else NameOfComputer = "Unknown" End If End Function Public Function username() As String Dim Buffer As String * 100 Dim BuffLen As Long BuffLen = 100 getUsername Buffer, BuffLen username = Left(Buffer, BuffLen - 1) End Function Sub RenamesFiles____loads_of_them() 'rflag4 use this one Chris On Error GoTo handleError Set fs = CreateObject("Scripting.FileSystemObject") Set c22namerange = Selection '[C198:D347] numCfiles = c22namerange.Rows.Count errcount = 0 For ri = 1 To numCfiles oldfilename = c22namerange.Cells(ri, 1).Value newfilename = c22namerange.Cells(ri, 2).Value Debug.Print oldfilename & vbCrLf & newfilename 'correct method! fs.movefile oldfilename, newfilename 'correct method! ulog oldfilename & " => " & newfilename Next Set fs = Nothing MsgBox "finished " & numCfiles & " files" & vbCrLf & _ errcount & " errors" Exit Sub handleError: errcount = errcount + 1 Select Case Err Case 53 ulog "file not found " & newfilename Resume Next Case 58 'already got it ulog "file already there " & newfilename Resume Next Case Else MsgBox Err.Description End Select End Sub Function findALL(searchTerm As Variant, Fir As Range) As Range 'returns a range with containing all sucessful matches. 'example _ 'This example finds all cells in the worksheet one that _ c-o-n-t-a-i-n the value searchterm and add to findall region Set findALL = Nothing With Fir 'parameterised for ranges. Set C = .Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlPart) If Not C Is Nothing Then firstAddress = C.Address Set findALL = C Do Set findALL = Application.Union(findALL, C) Set C = .FindNext(C) Loop While Not C Is Nothing And C.Address <> firstAddress End If End With End Function Function realRows(Os As Worksheet) Set lastcell = Os.Cells. _ Find("*", After:=Os.[iv65536], SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious) If lastcell Is Nothing Then realRows = 0 Else realRows = lastcell.Row End If End Function Public Function IsoWeekNum(D1 As Date) As Integer ' Provided by Daniel Maher. Dim D2 As Long D2 = DateSerial(Year(D1 - Weekday(D1 - 1) + 4), 1, 3) IsoWeekNum = Int((D1 - D2 + Weekday(D2) + 5) / 7) End Function Sub fixValX2(Os As Worksheet) Dim buf As Variant With Os 'Get values as an array buf = .UsedRange.Value 'Put the array back into the worksheet 'Clobbers formulae as it should! .UsedRange.Value = buf End With End Sub Function collSemi(bitsrange As Range) SEP = ";" '" or " quot = """" ' or "" for nothing quot = "" collSemi = "" For Each C In bitsrange co = C.Value If Len(co) > 0 Then co = quot & co & quot If collSemi = "" Then collSemi = co Else collSemi = collSemi & SEP & co End If End If Next C End Function Sub ColourChart() LIMIT = 56 ' max no of colours Sheets.Add For ind = 1 To LIMIT Cells(ind, 1).Value = ind ' With Range(Cells(ind, 2), Cells(ind, 5)) '.Value = ind ?? With .Interior .ColorIndex = ind .Pattern = xlSolid End With End With Next ind End Sub Sub view_50Plus() 'menu texts Sheets.Add i = 1 Set currentSheets = Application.ActiveWorkbook.Worksheets For Each ThisSheet In currentSheets ' thissheet.Select ' ActiveWindow.Zoom = 50 With Range("b" & i) .Value = ThisSheet.Name .Font.Size = 14 .EntireColumn.AutoFit End With i = i + 1 ''' killgrid '' ActiveWindow.DisplayGridlines = False '' Range("a1").Select Next ThisSheet End Sub Sub BasicPageSetup() With ActiveSheet.PageSetup .LeftHeader = "Planning team" .CenterHeader = "&A": .RightHeader = "Source: " .LeftFooter = "&8&P/&N": .CenterFooter = "" .RightFooter = "&""Small Fonts,Italic""&6Analyst: Chris Wright :&D at &T" .PrintHeadings = False: .PrintGridlines = False .PrintComments = xlPrintNoComments '.PrintQuality = 600 .Orientation = xlLandscape .Draft = False: .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic: .Order = xlDownThenOver: .BlackAndWhite = False: .Zoom = 100 End With 'NEEDS link to dppe End Sub Sub SDeleter() 'new power version Dim snames As String Dim arr_snames As Variant SEP = "," snames = "" Application.DisplayAlerts = False Set currentSheets = Application.ActiveWorkbook.Worksheets For Each ThisSheet In currentSheets If ThisSheet.Name Like "[sS]heet*" Then snames = snames & SEP & ThisSheet.Name Next ThisSheet If Len(snames) = 0 Then Exit Sub snames = Right(snames, Len(snames) - 1) arr_snames = Split(snames, SEP) Application.ActiveWorkbook.Worksheets(arr_snames).Delete Application.DisplayAlerts = True End Sub Sub linkBeater1() 'killing duff links '''''''''''''''''''''by deleting the old names Dim N As Name Searchstr = "retir" Searchstr = "shed" ' Searchstr = "tprc1fanp001" Searchstr = "$" Searchstr = "Scorecard" Searchstr = "housekeeping" Searchstr = "#REF" 'tprc1fanp001 Set all_names = ActiveWorkbook.Names For Each N In all_names If InStr(1, N.RefersTo, Searchstr, 1) > 0 Then N.Delete End If Next N End Sub Sub linklister() MsgBox ActiveWorkbook.Name aLinks = ActiveWorkbook.LinkSources() If Not IsEmpty(aLinks) Then For i = 1 To UBound(aLinks) Debug.Print "Link " & i & ":" & Chr(13) & aLinks(i) Next i End If End Sub Sub linkbeater2() MsgBox ActiveWorkbook.Name aLinks = ActiveWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(aLinks) Then For i = 1 To UBound(aLinks) MsgBox "Link " & i & ":" & Chr(13) & aLinks(i) Next i End If Exit Sub For Each s In Sheets s.Activate Set C = s.UsedRange.Find("month24") If Not C Is Nothing Then firstAddress = C.Address MsgBox s.Name & "!" & C.Address Do C.Select C.Interior.Pattern = xlPatternGray75 Set C = s.UsedRange.FindNext(C) Loop While Not C Is Nothing And C.Address <> firstAddress End If Next s End Sub Sub AlphaSheet() 'frompcdirect Application.ScreenUpdating = False Count = Sheets.Count For i = 1 To Count - 1 For j = i + 1 To Count If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move Before:=Sheets(i) End If Next Next End Sub Sub colourmad() 'recorded 05/08/2004 by Chris Wright x2531 Randomize ' Initialize random-number generator. ' Generate random value between 1 and 20. Selection.Item(1).CurrentRegion.Select With Selection ' With .Interior .ColorIndex = Int((20 * Rnd) + 1) .Pattern = xlSolid End With With .Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = Int((20 * Rnd) + 1) End With .Font.ColorIndex = xlAutomatic [a26].Select End With End Sub Sub WBNames() 'From: ":)Þ" 'Newsgroups: microsoft.public.Excel.programming 'Sent: Wednesday, April 04, 2001 7:59 AM EDT 'Subject: Re: Display all open workbooks 'wrote in message news:#Rxk#5PvAHA.1872@tkmsftngp03... Dim sh As Worksheet Dim wb As Workbook Dim adn As addin Dim wnd As Window Set sh = ThisWorkbook.Worksheets.Add With sh .Range("A1") = "Workbook Name" .Range("B1") = "Workbook Path" .Range("C1") = "Has Hidden Windows?" .Range("A1:C1").Font.Bold = True .Range("A2").Activate For Each wb In Workbooks ActiveCell.Value = wb.Name ActiveCell.Offset(0, 1).Value = wb.path ActiveCell.Offset(0, 2).Value = "NO" For Each wnd In wb.Windows If wnd.Visible = False Then ActiveCell.Offset(0, 2).Value = "YES" End If Next wnd ActiveCell.Offset(1, 0).Activate Next wb ActiveCell.Offset(1, 0).Activate With ActiveCell .Range("A1") = "Add-In Name" .Range("B1") = "Add-In Path" .Range("C1") = "Loaded?" .Range("A1:C1").Font.Bold = True End With ActiveCell.Offset(1, 0).Activate For Each adn In AddIns ActiveCell.Value = adn.Name ActiveCell.Offset(0, 1).Value = adn.path ActiveCell.Offset(0, 2).Value = "NO" If adn.Installed = True Then _ ActiveCell.Offset(0, 2).Value = "YES" ActiveCell.Offset(1, 0).Activate Next adn .Range("C1").EntireColumn.HorizontalAlignment = xlCenter .Cells.EntireColumn.AutoFit End With End Sub Sub colors56() 'Attribute colors56.VB_Description = "useful non cw marco" 'Attribute colors56.VB_ProcData.VB_Invoke_Func = " \n14" Sheets.Add '57 colors, 0 to 56 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim i As Long Dim str0 As String, str As String For i = 0 To 56 Cells(i + 1, 1).Interior.ColorIndex = i Cells(i + 1, 1).Value = "[Color " & i & "]" Cells(i + 1, 2).Font.ColorIndex = i Cells(i + 1, 2).Value = "[Color " & i & "]" str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6) 'Excel shows nibbles in reverse order so make it as RGB str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2) 'generating 2 columns in the HTML table Cells(i + 1, 3) = "#" & str & "#" & str & "" Cells(i + 1, 4).Formula = "=Hex2dec(""" & Right(str0, 2) & """)" Cells(i + 1, 5).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)" Cells(i + 1, 6).Formula = "=Hex2dec(""" & Left(str0, 2) & """)" Cells(i + 1, 7) = "[Color " & i & ")" Next i done: Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic Application.ScreenUpdating = True End Sub Sub AddCol() Rows(1).Insert Shift:=xlDown UL = [a1].SpecialCells(xlLastCell).Row Columns("A:A").Insert Shift:=xlToRight With Range(Cells(2, 1), Cells(UL, 1)) .FormulaArray = "=row()" buf = .Value 'Put the array back into the worksheet 'Clobbers formulae! .Value = buf End With With [a1] .Value = "rows" .Offset(0, 1).Value = "dataLines" .AutoFormat xlRangeAutoFormatList3 .CurrentRegion.EntireColumn.AutoFit End With End Sub Sub tst2() fixValX2 ActiveSheet 'stripOutDir " test2" End Sub Public Function stripOutDir(fnstring As String) ' WHY NOT USE beforeChar() Dim ch As String bs$ = "\" stripOutDir = "" For Cc = 1 To Len(fnstring) ch = Mid(fnstring, Cc, 1) dlevtemp$ = dlevtemp$ & ch If ch = bs$ Then stripOutDir = stripOutDir & dlevtemp$ dlevtemp$ = "" End If Next Cc If Len(stripOutDir) > 0 Then stripOutDir = Left(stripOutDir, Len(stripOutDir) - 1) End Function Public Function omitter(instring As String, oon As String, oof As String) flag = 0 Ommiter = "" Dim ch For Cc = Len(instring) To 1 Step -1 ' wrong! ch = Left(Right(instring, Cc), 1) If flag = 0 And ch <> " " Then flag = 1 ' leading spaces stripped starts at first non space If ch = oon Or off Then 'found a switch char flag = flag * -1 ' switch affects next char; s-chars not kept Else If flag = 1 Then omitter = omitter & ch End If Next Cc End Function Sub Cshells() 'ChDir WindowsFolder ''some way to use this >>Application.ActivateMicrosoftApp xlMicrosoftMail 'xlMicrosoftAccess umacsheet = "macrodata" 'Shell "explorer ", vbNormalFocus Shell "explorer " & ThisWorkbook.Sheets(umacsheet).[nf].Value, vbNormalFocus ''' End Sub Sub linklisterA() MsgBox ActiveWorkbook.Name Set losh = ThisWorkbook.Sheets.Add aLinks = ActiveWorkbook.LinkSources(xlExcelLinks) For il = 1 To UBound(aLinks) losh.Cells(il, 2) = aLinks(il) Next il End Sub Function beforeChar(inputstring As String, trigger As String) isl = Len(inputstring) beforeChar = "" For Cc = 1 To Len(inputstring) ch = Mid(inputstring, Cc, 1) dlevtemp$ = dlevtemp$ & ch If ch = trigger Then beforeChar = beforeChar & dlevtemp$ dlevtemp$ = "" End If Next Cc beforeChar = Left(beforeChar, Len(beforeChar) - 1) 'beforechar = dlevtemp$ End Function Function afterChar(inputstring As String, trigger As String) isl = Len(inputstring) afterChar = "" For Cc = 1 To Len(inputstring) ch = Mid(inputstring, Cc, 1) dlevtemp$ = dlevtemp$ & ch If ch = trigger Then 'afterChar = afterChar & dlevtemp$ dlevtemp$ = "" End If Next Cc 'afterChar = Left(afterChar, Len(afterChar) - 1) afterChar = dlevtemp$ End Function Sub fixvaluesBad() Application.ScreenUpdating = False Cells.Copy Cells.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Exit Sub ''' ''' ''''For Each c In Application.Selection Set cold = [a1] For Each C In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas) If C.Row <> cold.Row Then Application.StatusBar = C.Address If IsError(C.Value) Then C.Value = "Err" Else If Len(C.Value) > 0 Then If C.Formula <> C.Value Then C.Formula = C.Value End If End If Set cold = C End If Next C Application.ScreenUpdating = True End Sub Sub dropitARR(outvar()) 'WIP Set oc = ActiveCell.Offset(LBound(outvar, 1) - 1, LBound(outvar, 2) - 1) Set oc2 = ActiveCell.Offset(UBound(outvar, 1) - 1, UBound(outvar, 2) - 1) Set orang = Range(oc, oc2) orang.Value = outvar End Sub Sub dropitOFF(outvar()) '2 dimensions only Set ohome = ActiveCell 'Cell loops are SLOW For j = LBound(outvar, 2) To UBound(outvar, 2) For i = LBound(outvar, 1) To UBound(outvar, 1) ohome.Offset(i, j).Value = outvar(i, j) Next i Next j End Sub Sub dropitout1(outvar()) '1 dimension only Set ohome = ActiveCell j = 1 'Cell loops are SLOW baser = 1 - LBound(outvar, 1) For i = LBound(outvar, 1) To UBound(outvar, 1) ohome.Cells(i + baser, j).Value = outvar(i) Next i End Sub Sub dropitout(outvar()) '2dimensions only Set ohome = ActiveCell 'Cell loops are SLOW For j = LBound(outvar, 2) To UBound(outvar, 2) For i = LBound(outvar, 1) To UBound(outvar, 1) ohome.Cells(i, j).Value = outvar(i, j) Next i Next j End Sub Sub dropitout2TWB(outvar(), outSheet As Worksheet) '2dimentions only ur = realRows(outSheet) + 2 Set ohome = outSheet.Cells(ur, 1) For j = LBound(outvar, 2) To UBound(outvar, 2) For i = LBound(outvar, 1) To UBound(outvar, 1) ohome.Cells(i, j).Value = outvar(i, j) Next i Next j End Sub Sub dropVrt(outvar, ohome As Range) '2dimensions only must be a variant array 'Set ohome = ActiveCell ohome.Range(Cells(1, 1), Cells(UBound(outvar, 1), UBound(outvar, 2))).FormulaArray = outvar End Sub Sub ClearSingleArray(outvar()) '1dimension only For i = LBound(outvar) To UBound(outvar) outvar(i) = 0 Next i End Sub Sub clear2dim_old(outvar()) '2dimensions For j = LBound(outvar, 2) To UBound(outvar, 2) For i = LBound(outvar, 1) To UBound(outvar, 1) outvar(i, j) = 0 Next i Next j End Sub Sub bordAllMed(fenceMe As Range) 'Dim indb(4) As XlBordersIndex 'NB std.Order ' indb = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight) 'indb(1) = xlEdgeLeft: indb(2) = xlEdgeTop 'indb(3) = xlEdgeBottom: indb(4) = xlEdgeRight For bind = 0 To 3 With fenceMe.Borders(indb(bind)) .LineStyle = xlContinuous .Weight = xlMedium End With Next End Sub Sub dummy() ' this sub is a bookmark for the uuty hyperlink ' which should drop you righthere MsgBox Now End Sub Sub tr() MsgBox realRows(ThisWorkbook.Sheets(7)) End Sub Function conc_range(datrange As Range) As String 'EVEN counts what was in that last cell!! SEP = " " conc_range = "" For Each a In datrange.Areas 'manages multiareas For Each C In a conc_range = conc_range & C & SEP Next Next a 'take off trailing separator conc_range = Left(conc_range, Len(conc_range) - Len(SEP)) End Function Sub totalrange(datrange As Range) 'puts total of range into last cell 'EVEN counts what was in that last cell!! '' but clobbers it total = Application.WorksheetFunction.Sum(datrange) For Each a In datrange.Areas 'manages multiareas For Each C In a Set resultc = C Next Next a resultc.Value = total End Sub Sub copFR(stcell As Range, totright As Integer) If stcell.Count <> 1 Then Set stcell = stcell.Item(1) radjust = 1 Range(stcell, stcell.Offset(0, totright - radjust)).FillRight End Sub Sub copFL(stcell As Range, totright As Integer) If stcell.Count <> 1 Then Set stcell = stcell.Item(1) radjust = 1 Range(stcell, stcell.Offset(0, -totright + radjust)).FillLeft End Sub Sub TOGscrnUP() Application.ScreenUpdating = Not Application.ScreenUpdating End Sub Sub clearRight(stcell As Range, totright As Integer) If stcell.Count <> 1 Then Set stcell = stcell.Item(1) radjust = 1 Range(stcell, stcell.Offset(0, totright - radjust)).ClearContents End Sub Sub captionfunOLD() Debug.Print Application.ActiveWorkbook.FullName & vbCrLf & _ Application.ActiveWorkbook.Name & vbCrLf & _ Application.ActiveWindow.Caption & vbCrLf & _ Application.Caption End Sub Sub sheethider() 'Private Sub Worksheet_SelectionChange(ByVal Target As Range) pindex = 1 For Each s In ThisWorkbook.Sheets If s.Index <> pindex Then s.Visible = Not s.Visible 'treu Next End Sub Sub ulogXV(Note As String) 'keeping better tabs on myself ... 'MsgBox ThisWorkbook.FullName Dim audit_sheet As Worksheet Set audit_sheet = ThisWorkbook.Sheets("Audit_trail") NextRow = realRows(audit_sheet) + 1 With audit_sheet .Cells(NextRow, 1).Value = Date .Cells(NextRow, 2).Value = Time .Cells(NextRow, 3).Value = Note .Cells(NextRow, 4).Value = whatbooksaround End With Application.StatusBar = Left(Note, 120) End Sub Function coll(bitsrange As Range) Application.Volatile SEP = "" quot = """" ' or "" for nothing quot = "" coll = "" SEP = ";" '"" '" or " For Each C In bitsrange co = C.Value If Len(co) > 0 Then co = quot & co & quot If coll = "" Then coll = co Else coll = coll & SEP & co End If End If Next C End Function Function collc(bitsrange As Range) SEP = "," quot = """" ' or "" for nothing quot = "" collc = "" For Each C In bitsrange co = C.Value If Len(co) > 0 Then If Len(quot) > 0 Then co = quot & co & quot If collc = "" Then collc = co Else collc = collc & SEP & co End If End If Next C End Function Sub TextFromShapes() For Each s In ActiveSheet.Shapes Debug.Print s.TextFrame.Characters.Text Next s End Sub Sub namestepper() SHEETNAME$ = ActiveSheet.Name '"macrodata" For Each N In ActiveWorkbook.Names ''selecting active sheet ..... If InStr(1, N.RefersTo, SHEETNAME$, 1) >= 0 Then Debug.Print N.Name, N.RefersTo N.RefersToRange.Select Breather 2 End If Next End Sub Sub Breather(secs) newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + secs waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime End Sub Sub dppetester() Sheets.Add On Error Resume Next rw = 1 For Each P In ActiveWorkbook.BuiltinDocumentProperties Cells(rw, 1).Value = P.Name Cells(rw, 2).Value = P.Value rw = rw + 1 Next rw = rw + 3 For Each P In ActiveWorkbook.CustomDocumentProperties Cells(rw, 1).Value = P.Name Cells(rw, 2).Value = P.Value rw = rw + 1 Next End Sub Sub dppe() If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub 'On Error GoTo ehandle Set buidp = ThisWorkbook.Worksheets("dppe").Range("builtins") 'Set custp = ThisWorkbook.Worksheets("dppe").Range ("customdocprop") rb = buidp.Rows.Count For Nro = 1 To rb til = buidp.Cells(Nro, 1).Value vil = buidp.Cells(Nro, 2).Value If Len(vil) > 0 Then ActiveWorkbook. _ BuiltinDocumentProperties(til) = vil End If Next Nro End Sub Sub datesfinder() Sheets("date problem").Select 'why not use .Find ?? ' NotBlank_scan(Date, Cells, False).Select MsgBox NotBlank_scan(Date, Cells, True).Row End Sub Function whatbooksaround() As String whatbooksaround = "" Dim w As Workbook For Each w In Workbooks If w.Name <> ThisWorkbook.Name Then whatbooksaround = whatbooksaround & " " & w.FullName End If Next w End Function Sub fixValX() Dim buf As Variant 'Get values as an array buf = ActiveSheet.UsedRange.Value 'Put the array back into the worksheet 'Clobbers formulae! ActiveSheet.UsedRange.Value = buf End Sub Sub comments_fun() 'puts value into a note for the selection _ or gets values from the notes Application.Calculation = xlCalculationManual Set cell_Range = Selection SEP = "" 'or"1st delivery ..." ''"," or ":" For Each C In cell_Range '''''' value TO comment text ''''' With c.AddComment ''''' .Visible = False ''''' .Text SEP & c.Value ''''' End With 'comment TO value text sTemp = C.Comment.Text C.Value = Right(sTemp, Len(sTemp) - 0) C.Comment.Delete Next C MsgBox ("notes added :: all cells") Application.Calculation = xlCalculationAutomatic End Sub Function groupfind(termsArray As Variant) As Range Dim TermNum As Integer On errr GoTo giveupWithNothing Set groupfind = Nothing Set groupfind = findALL(termsArray(0)).Item(1) For TermNum = 1 To UBound(termsArray, 1) Set groupfind = Union(groupfind, findALL(termsArray(TermNum)).Item(1)) 'takes only first thing found Next Exit Function giveupWithNothing: End Function Sub RenamesFile() 'rflag3 cheeky go sub ?? Set fs = CreateObject("Scripting.FileSystemObject") fs.movefile [oldfile].Value, [NewFile].Value 'correct method! Set fs = Nothing End Sub Public Function simpcopy(source As Range, dest As Range) As Boolean simpcopy = False dest.ClearContents If source.Cells.Count <> dest.Cells.Count Then MsgBox "range counts are different", , "simpcopy " Exit Function End If For ic = 1 To source.Cells.Count dest.Item(ic).Value = source.Item(ic).Value Next simpcopy = True End Function Public Function simpcopyNC(source As Range, dest As Range) As Boolean simpcopyNC = False If source.Cells.Count <> dest.Cells.Count Then MsgBox "range counts are different", , "simpcopy NO Clear" Exit Function End If For ic = 1 To source.Cells.Count If Len(source.Item(ic).Value) > 0 Then _ dest.Item(ic).Value = source.Item(ic).Value Next simpcopyNC = True End Function Public Function splat_copy(source As Range, dest As Range) As Boolean splat_copy = False 'source.Parent.Parent.Activate 'source.Parent.Select 'MsgBox source.Address & vbCrLf & dest.Address source.Copy dest 'Selection stays where it is splat_copy = True End Function Function L_U_V(inputRange As Range) As Variant 'bdb2 = InputRange.Value Dim cl As Range, UniqueValues As New Collection, uValue As Variant _ 'InputRange As Range On Error Resume Next ' Set InputRange = Application.InputBox _ ' (prompt:="Select the range to convert", _ ' Type:=8) For Each cl In inputRange UniqueValues.Add cl.Value, CStr(cl.Value) Next cl '''' Sheets.Add Set L_U_V = UniqueValues End Function Sub kill_comments() For Each cl In ActiveSheet.Comments cl.Delete Next cl ''ActiveSheet.Next.Select End Sub Public Function multi_copy(source As Range, dest As Range, clear_dest As Boolean) As Boolean multi_copy = False If clear_dest Then dest.ClearContents If source.Cells.Count <> dest.Cells.Count Then MsgBox "range counts are different", , "multi_copy" Exit Function End If sac = source.Areas.Count dac = dest.Areas.Count da = 1 icd = 1 For Each sc In source.Cells dest.Areas(da).Item(icd) = sc.Value 'If Len(sc.value) > 0 Then ' dest.Item(ic).value = source.Item(ic).value icd = icd + 1 If icd > dest.Areas(da).Cells.Count Then da = da + 1 icd = 1 End If Next multi_copy = True End Function Sub SheetsVisiblePlease() For Each w In Workbooks 'If w.Name Like "Holdover*" Then w.Activate Set currentSheets = Application.ActiveWorkbook.Sheets For Each ThisSheet In currentSheets ThisSheet.Visible = True Next ThisSheet 'End If Next w End Sub Sub WCDeleter(wc_string As String, MessWkbk As Workbook) Dim ThisSheet As Worksheet With Application .DisplayAlerts = False Set currentSheets = MessWkbk.Worksheets For Each ThisSheet In currentSheets If ThisSheet.Name Like wc_string Then ThisSheet.Select ThisSheet.Delete ' End If Next ThisSheet .DisplayAlerts = True End With End Sub Function UUfunction1(data_range As Range) As Variant UUfunction1 = Application.WorksheetFunction.Sum(data_range) End Function Function UUfunction9(data_range As Range) As Variant UUfunction9 = Application.WorksheetFunction.Max(data_range) End Function Sub view_50PlusA() 'menu texts ''Sheets.Add ''i = 1 Set currentSheets = Application.ActiveWorkbook.Worksheets For Each ThisSheet In currentSheets ThisSheet.Select ' ActiveWindow.Zoom = 50 '' With Range("b" & i) '' .value = ThisSheet.Name '' .Font.Size = 14 'For Each h In ThisSheet.Shapes ' h.Delete 'Next h '' '' .EntireColumn.AutoFit '' End With ''i = i + 1 '' ''''' killgrid '''' ActiveWindow.DisplayGridlines = False '''' Range("a1").Select Next ThisSheet End Sub Sub kill_all_hyperlinks() 'not to kill UU hyperlinks If ActiveWorkbook.Name = Application.ThisWorkbook.Name Then Exit Sub For Each w In Worksheets For Each h In w.Hyperlinks h.Delete Next h Next w End Sub Sub coll2commas() Dim comlist As String Dim cl As Range, NonblankValues As New Collection Dim uValue As Variant, inputRange As Range On Error Resume Next comlist = "" Set inputRange = Application.InputBox(prompt:="Select the range to convert", Type:=8) For Each cl In inputRange If Len(cl.Value) > 0 Then NonblankValues.Add cl.Value, CStr(cl.Value) Next cl For Each nb In NonblankValues comlist = comlist & nb & Chr$(44) Next nb comlist = Left(comlist, Len(comlist) - 1) MsgBox comlist End Sub Sub hideWebBar() ' Application.CommandBars("Web").Visible = False End Sub 'Here 's some VBA to replace the Analysis Toolpak functions... 'Option Explicit Public Function Hex2decVBA(ByVal vntHex_Value As Variant) As Variant ''' ''---------------------------------------------------------------------------- ''' Experts Exchange Question: ''' Home \ All Topics \ Applications \ MS Office \ Excel ''' ''http://www.experts-exchange.com/Appl..._21547447.html ''' Creating Column that does hexidecimal math ''' ''' Copyright (c) 2005 Clearlogic Concepts (UK) Limited ''' N.Lee [ http://NigelLee.info ] - 1 September 2005 ''' ''---------------------------------------------------------------------------- Dim intLoop As Integer Dim vntMultiplier As Variant Dim vntReturn As Variant On Error GoTo Err_Hex_To_Decimal vntMultiplier = CDec(1) For intLoop = Len(vntHex_Value) To 1 Step -1 vntReturn = vntReturn + CDec(vntMultiplier) * CDec("&H" & _ Mid$(vntHex_Value, intLoop, 1)) vntMultiplier = CDec(vntMultiplier * 16) Next intLoop Exit_Hex_To_Decimal: On Error Resume Next Hex2decVBA = vntReturn Exit Function Err_Hex_To_Decimal: On Error Resume Next vntReturn = CDec(0) Resume Exit_Hex_To_Decimal End Function Sub MDfindfileSubs() findAfile True End Sub Sub findAfile(WantSubFs As Boolean) 'f1 On Error Resume Next SDeleter ' SEP = "|" 'use to improve a:files etc'subfolders property exists Application.ScreenUpdating = False Set s = ThisWorkbook.Worksheets("macrodata") startdir$ = s.[f_dir].Value fileext = s.Range("f_ext").Value 'startdate = s.Range("f_start").Value 'enddate = s.Range("f_end").Value 'This example uses the NewSearch method to reset the default search _ criteria before beginning a new search. margin_COl = 10 Sheets.Add colf = 3 If fileext <> "*" Then ActiveSheet.Name = ActiveSheet.Name & fileext Else ActiveSheet.Name = ActiveSheet.Name & "ALL" End If ti = 1 Cells(ti, 1).Value = "FullFilename" Cells(ti, 2).Value = "Directory" Cells(ti, colf).Value = "FileDateTime" Cells(ti, colf + 1).Value = "FileLength" Cells(ti, colf + 2).Value = "GetAttr" Cells(ti, colf + 3).Value = "shortname" Cells(ti, colf + 4).Value = "Ext" Cells(1, margin_COl).Value = startdir$ With Application.FileSearch .NewSearch: .LookIn = startdir$: .SearchSubFolders = WantSubFs .Filename = "*." & fileext ' .TextOrProperty = "San*": .MatchAllWordForms = True: ' .FileType =msoFileTypeAllFiles If .Execute() > 0 Then Cells(2, margin_COl).Value = .FoundFiles.Count & " file(s) found." Application.ScreenUpdating = True Application.StatusBar = "Findafile " & .FoundFiles.Count & " found in " & startdir$ & .Filename Application.ScreenUpdating = False For j = 1 To .FoundFiles.Count 'an array would work better here '?????????????????????????????????????????????????????? i = j + 1 'shift due to lost file error Cells(i, 1).Value = .FoundFiles(j) Cells(i, 2).Value = stripOutDir(.FoundFiles(j)) Cells(i, colf).Value = FileDateTime(.FoundFiles(j)) Cells(i, colf + 1).Value = FileLen(.FoundFiles(j)) Cells(i, colf + 2).Value = GetAttr(.FoundFiles(j)) Cells(i, colf + 3).Value = afterChar(.FoundFiles(j), "\") Cells(i, colf + 4).Value = afterChar(Cells(i, colf + 3).Value, ".") Next j Else MsgBox "There were no files found." End If End With 'cornerSort 'killDUPline Cells(1, 1).CurrentRegion.AutoFormat Format:=xlRangeAutoFormatList3 Application.ScreenUpdating = True End Sub Sub DirDirs() SDeleter Application.ScreenUpdating = False Set s = ThisWorkbook.Worksheets("macrodata") 'SHOULD Get parameters from main sheet properly ' Display the names in C:\ that represent directories. Dim alldirs(100) idir = 1 MyPath = s.[diraf].Value ' ' Set the path. save_mp = MyPath MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry. Do While MyName <> "" ' Start the loop. ' Ignore the current directory and the encompassing directory. If MyName <> "." And MyName <> ".." Then ' Use bitwise comparison to make sure MyName is a directory. If (GetAttr(MyPath & "\" & MyName) And vbDirectory) = vbDirectory Then alldirs(idir) = MyPath & MyName Debug.Print MyPath & MyName ' Display entry only if it idir = idir + 1 End If ' it represents a directory. End If MyName = Dir ' Get next entry. Loop '''MsgBox alldirs ThisWorkbook.Sheets.Add Set dirlistsheet = ActiveSheet dirlistsheet.[D1].Value = save_mp & " has " & idir & " directories " [B1].Select Call dropitout1(alldirs) 'not good ??????? 'dirlistsheet.Range(Cells(1, 2), Cells(100, 2)).Value = alldirs Application.ScreenUpdating = True End Sub '=========== Sub DirectFiles() TOGscrnUP SDeleter Set s = ThisWorkbook.Worksheets("macrodata") folderspec = s.Range("f_dir").Value If folderspec = "" Then Exit Sub ShowFileListCCs (folderspec) Cells(2, 8).Value = "Done " & Now Columns("a:g").AutoFit [a1].AutoFormat Format:=xlRangeAutoFormatList3, Number:=True, Font:= _ True, Alignment:=True, Border:=True, Pattern:=True, Width:=True ActiveWindow.Zoom = 70 TOGscrnUP End Sub Sub DirectDirs2() TOGscrnUP SDeleter Set s = ThisWorkbook.Worksheets("macrodata") folderspec = s.Range("f_dir").Value If folderspec = "" Then Exit Sub ShowDIRCCs (folderspec) Cells(2, 8).Value = "Done " & Now Columns("a:g").AutoFit [a1].AutoFormat Format:=xlRangeAutoFormatList3, Number:=True, Font:= _ True, Alignment:=True, Border:=True, Pattern:=True, Width:=True ActiveWindow.Zoom = 70 TOGscrnUP End Sub Sub ShowFileListCCs(folderspec) 'Extend 'Extend 'Extend On Error GoTo errorRep 'n type size date Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.Files namecol = 2 typecol = 3 sizecol = 4 datecol1 = 5 lnamecol = 1 Sheets.Add s2 = "Directory " & folderspec srow = 1 For Each f1 In fc srow = srow + 1 Cells(srow, lnamecol) = f1.path Cells(srow, namecol) = f1.Name Cells(srow, typecol) = f1.Type Cells(srow, sizecol) = f1.Size & " bytes." Cells(srow, datecol1) = f1.DateCreated 'not good for hidden/sys files? Next Cells(1, 8).Value = s2 Cells(1, lnamecol) = "fullname" Cells(1, namecol) = "name" Cells(1, typecol) = "type" Cells(1, sizecol) = "size" Cells(1, datecol1) = "date" Exit Sub errorRep: MsgBox Err.Description & "( " & Err.Number & ")" [Af].Value = Err.Description & "( " & Err.Number & ")" End Sub Sub ShowDIRCCs(folderspec) 'Extend 'Extend 'Extend On Error GoTo errorRep 'n type size date Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.SubFolders namecol = 2 typecol = 3 sizecol = 4 datecol1 = 5 lnamecol = 1 Sheets.Add s2 = "Directory " & folderspec srow = 1 For Each f1 In fc srow = srow + 1 Cells(srow, lnamecol) = f1.path Cells(srow, namecol) = f1.Name Cells(srow, typecol) = f1.Type Cells(srow, sizecol) = f1.Size & " bytes." Cells(srow, datecol1) = f1.DateCreated 'not good for hidden/sys files? Next Cells(1, 8).Value = s2 Cells(1, lnamecol) = "fullname" Cells(1, namecol) = "name" Cells(1, typecol) = "type" Cells(1, sizecol) = "size" Cells(1, datecol1) = "date" Exit Sub errorRep: MsgBox Err.Description & "( " & Err.Number & ")" [Af].Value = Err.Description & "( " & Err.Number & ")" End Sub Sub timesimple() MsgBox Now End Sub Sub kill_AS_hyperlinks() 'not to kill UU hyperlinks 'Exit Sub With ActiveSheet If .Name = "Macrodata" Then Exit Sub For Each h In .Hyperlinks h.Delete Next h End With End Sub Sub gasp() '''''vshort gap '''''''how fast is gasp at 500000 ''''Debug.Print Format(Now() - Int(Now()), "hh:mm:ss") ''''gasp ''''Debug.Print Format(Now() - Int(Now()), "hh:mm:ss") ''''' one second is 10 million For i = 1 To 500000 j = Sqr(i) Next End Sub Sub Hic() ' V V v short gap For i = 1 To 50 j = Sqr(i) Next End Sub Sub DropitoutJS(outvar(), outSheet As Worksheet) '2 dim only Dim outzap As Variant ' array holder ur = realRows(outSheet) + 2 Set ohome = outSheet.Cells(ur, 1) LR = UBound(outvar, 1) lc = UBound(outvar, 2) 'create a variant array of right size on ohome with current sheet contents outzap = ohome.Range(Cells(1, 1), Cells(LR, lc)).Value 'load variant with ov values For j = LBound(outvar, 2) To UBound(outvar, 2) For i = LBound(outvar, 1) To UBound(outvar, 1) outzap(i, j) = outvar(i, j) Next i Next j ''zap!!! overwrite that sheet range ohome.Range(Cells(1, 1), Cells(LR, lc)).Value = outzap End Sub Sub Clear_SB2011() With Application Breather 2 .StatusBar = "End Program >> " & Now Breather 1 .StatusBar = False End With End Sub Function CheckForAutoFilters() As Integer CheckForAutoFilters = -1 With ActiveSheet If .AutoFilterMode = True And .FilterMode = True Then 'MsgBox "They are visible and in use" CheckForAutoFilters = 2 ElseIf .AutoFilterMode = True Then 'MsgBox "They are visible but not in use" CheckForAutoFilters = 1 Else 'MsgBox "They are not visible or in use" CheckForAutoFilters = 0 End If End With End Function Sub HarmoniseNotes() '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> _ 20-Dec-12 '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 'need to redusce number of support modules 'pick from all modules with UT in them plus 'functions 2011 'sainsbury zolo 'COPS ' End Sub ' HarmoniseNotes Sub Findfixer() Dim ff As Range Set ff = Nothing Set ff = ActiveSheet.Cells.Find("*", , , xlPart) End Sub Sub testerDF() Dim dumRange As Range, HasWorked As Boolean HasWorked = False 'findDate(#1/7/2012#, [c3:m31]).Select Set dumRange = [c3:m3,c6:m6,c9:m9,c12:m12,c15:m15,c18:m18,c21:m21,c24:m24,c27:m27,c30:m30,c33:m33] 'dumRange.Select findDate2012(#7/18/2012#, dumRange, HasWorked).Select End Sub Function findDate2012AREAS(Date2Find As Date, findRange As Range, jumpj As Boolean) As Range 'need Jj as an exit fleag .. 'UNFINISHED >>>>>>>>>>>>>>>> STARTED WORKING End Function Function findDate2014(Date2Find As Date, findRange As Range) As Range 'need Jj as an exit fleag ... 'I cant find dates so easily and don't want to re set the form/val parametr of FIND Dim i As Integer Dim j As Integer, dum, jumpj As Boolean Dim FRbuffer As Variant FRbuffer = findRange.Value jumpj = False Set findDate2014 = Nothing For j = LBound(FRbuffer, 2) To UBound(FRbuffer, 2) If Not (jumpj) Then For i = LBound(FRbuffer, 1) To UBound(FRbuffer, 1) If FRbuffer(i, j) = Date2Find Then 'found right I and J but must get them straight out jumpj = True Exit For End If Next i Else Exit For End If Next j If jumpj Then Set findDate2014 = findRange.Cells(i, j - 1) Else 'do nowt '''dum = MsgBox("Cant find :" & Date2Find, , "FindDate error") ''' Set findDate = findRange.Cells(1, 1) End If End Function Function datefinder2013(DSRange As Range) As Range 'finds range /cell contqaining date using array matchdtarget As Date, 'needs evelpopping routine to cope with multi areas Set datefinder2013 = DSRange.Range("a1") End Function Function findDateOLD(Date2Find As Date, findRange As Range) As Range 'I cant find dates so easily and don't want to re set the form/val parametr of FIND Dim i As Integer Dim j As Integer, dum Dim jumpj As Boolean Dim FRbuffer As Variant FRbuffer = findRange.Value jumpj = False For j = LBound(FRbuffer, 2) To UBound(FRbuffer, 2) If Not (jumpj) Then For i = LBound(FRbuffer, 1) To UBound(FRbuffer, 1) If FRbuffer(i, j) = Date2Find Then 'found right I and J but must get them straight out jumpj = True Exit For End If Next i Else Exit For End If Next j If jumpj Then Set findDate = findRange.Cells(i, j - 1) Else dum = MsgBox("Cant find :" & Date2Find, , "FindDate error") Set findDate = findRange.Cells(1, 1) End If End Function Sub setRect1(disText As String) Dim shape_Name As String shape_Name = "Rectangle 2" Me.Shapes(shape_Name).TextFrame.Characters.Text = disText ' useful syntax????????^^^^^^^^^^^^^^^^^^^^^^????????? End Sub Sub kill_custom_styles() '' Macro1 Macro ' Dim sty As Style For Each sty In ActiveWorkbook.Styles ' Debug.Print sty.Name & " " & sty.BuiltIn; If sty.BuiltIn Then Debug.Print " OK" Else sty.Delete Debug.Print " killed" End If Next End Sub Function Num_errors(errshereRange As Range) Application.Volatile Num_errors = 0 For Each C In errshereRange If IsError(C.Value) Then Num_errors = Num_errors + 1 Next C End Function Sub resstingbuttons() Debug.Print Now Dim s As Shape For Each s In Me.Shapes If s.Type = 8 Then bc = bc + 1 s.TextFrame.Characters.Text = "ATC " & bc End If Next s End Sub Sub SelUserRange() Dim UserRange As Range On Error GoTo Canceled Set UserRange = Application.InputBox(prompt:="Please Select Range", Title:="Range Select", Type:=8) [a1].Select Breather 6 UserRange.Select Canceled: End Sub Sub namescrubselective() Dim UserRange As Range Dim N As Name Set UserRange = Application.InputBox(prompt:="Please Select names list", Title:="names Range Select", Type:=8) For Each C In UserRange Set N = ActiveWorkbook.Names(C.Value) N.Delete Next End Sub Sub AnonomiseUserRange() Dim UserRange As Range Dim temp_var As Variant, i As Integer Const ASCIISPAN As Integer = 192 Const ASCIISTART As Integer = 64 Set UserRange = Application.InputBox(prompt:="Please Select Range", Title:="Range Select", Type:=8) [a1].Select Application.ScreenUpdating = False For Each C In UserRange temp_var = splitChars(" " & C.Value) For i = LBound(temp_var) To UBound(temp_var) temp_var(i) = Chr(Int(ASCIISTART * Rnd())) & Chr(ASCIISPAN - Asc(temp_var(i)) + ASCIISTART) Next C.Value = joinChars(temp_var) Next C Application.ScreenUpdating = True End Sub Sub texterrorAGAIN() 'WORKING WELL 24/01/2014 Dim C As Range Dim fstr As String Dim C As Range Set UserRange = Application.InputBox(prompt:="Please Select afflicted Range", Title:="Range Select", Type:=8) [a1].Select 'seekeks out all text formatted cells ans set them to geteral For Each C In UserRange If C.NumberFormat = "@" Then fstr = C.Formula C.ClearContents C.NumberFormat = "General" C.Formula = fstr End If Next C End Sub Sub textwrapping() Dim C As Range Set UserRange = Application.InputBox(prompt:="Please Select Range", Title:="Range Select", Type:=8) [a1].Select For Each C In UserRange If Len(C.Value) > 0 Then C.WrapText = True C.HorizontalAlignment = xlCenter C.VerticalAlignment = xlCenter C.Font.Size = 8 End If Next C End Sub Sub datcopquick() Debug.Print ActiveWorkbook.FullName Debug.Print ActiveSheet.Name ActiveWorkbook.ActiveSheet.[a1:iv1].Copy ThisWorkbook.Sheets("Sheet3").[a1:iv1] ThisWorkbook.Sheets("Sheet3").Rows(1).Insert End Sub Sub loadUV() Dim whatisit As Variant Set whatisit = L_U_V(Selection) ' loads a collection End Sub Sub AnonomiseUserRangeAlphaNUM() Dim UserRange As Range Dim temp_var As Variant, i As Integer Const ASCIILIST As String = "abcdefghijklmnopqrstuvwxyz0123456789000 " Const ASCIISTART As Integer = 64 Dim urangeArray As Variant Set UserRange = Application.InputBox(prompt:="Please Select Range", Title:="Range Select", Type:=8) [a1].Select Application.ScreenUpdating = False urangeArray = UserRange.Value For i = LBound(urangeArray, 1) To UBound(urangeArray, 1) For j = LBound(urangeArray, 2) To UBound(urangeArray, 2) urangeArray(i, j) = " " & urangeArray(i, j) temp_var = splitChars("" & urangeArray(i, j)) For k = LBound(temp_var) To UBound(temp_var) temppos = Int(Len(ASCIILIST) * Rnd()) + 1 temp_var(k) = Mid(ASCIILIST, temppos, 1) Next k urangeArray(i, j) = joinChars(temp_var) Next j 'cols in a row Next i 'rows UserRange.Value = urangeArray Application.ScreenUpdating = True End Sub Public Function omitter2(instring As String, oon As String, oof As String) flag = 0 Ommiter = "" Dim ch For Cc = Len(instring) To 1 Step -1 ch = Left(Right(instring, Cc), 1) If flag = 0 And ch <> " " Then flag = 1 ' leading spaces stripped starts at first non space If ch = oon Or off Then 'found a switch char flag = flag * -1 ' switch affects next char; s-chars not kept Else If flag = 1 Then omitter = omitter & ch End If Next Cc End Function Sub Cshells2() 'ChDir WindowsFolder ''some way to use this >>Application.ActivateMicrosoftApp xlMicrosoftMail 'xlMicrosoftAccess umacsheet = "macrodata" 'Shell "explorer ", vbNormalFocus Shell "explorer " & ThisWorkbook.Sheets(umacsheet).[nf].Value, vbNormalFocus ''' End Sub Sub linklister2() MsgBox ActiveWorkbook.Name Set losh = ThisWorkbook.Sheets.Add aLinks = ActiveWorkbook.LinkSources(xlExcelLinks) For il = 1 To UBound(aLinks) losh.Cells(il, 2) = aLinks(il) Next il End Sub Sub CLEAR_small_islands(Os As Worksheet) For Each C In Os.UsedRange If Len(C.Value) > 0 Then 'ignore empty cells If C.CurrentRegion.Count < 20 Then C.CurrentRegion.Clear End If End If Next C End Sub Sub bordall_12(fenceMe As Range) ' 20/01/2014 'Dim indb(4) As XlBordersIndex 'NB std.Order Dim indb As Variant, bind As Integer indb = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight) 'indb(1) = xlEdgeLeft: indb(2) = xlEdgeTop 'indb(3) = xlEdgeBottom: indb(4) = xlEdgeRight For bind = 0 To 3 With fenceMe.Borders(indb(bind)) Select Case bind Case 1 .LineStyle = xlContinuous .Weight = xlThin Case 2 'double for bottom only... .LineStyle = xlDouble .Weight = xlThick End Select End With Next End Sub Sub k5_killsFiles____loads_of_them() 'compare with DGfiles Dim Filename As String [c2:c3000].Select For Each C In Selection If FileExists(C.Value) Then Filename = C.Value Kill Filename C.Value = "deleted " & Filename End If Next End Sub Sub shortFN4dir() 'code to procress a dir listing on the free com Dim ffname As String, fullnames As Variant '[e1:e].Value = [D1:d10].Value fullnames = [D1:d20000].Value For i = LBound(fullnames, 1) To UBound(fullnames, 1) ffname = fullnames(i, 1) 'fullnames(i, 1) = fileNameRemainNEW(ffname) fullnames(i, 1) = stripOutDir(ffname) Next i [e1:e20000].Value = fullnames End Sub Sub dirTB1__2016() 'DIRECTORY FILES as input 'Extractin file ONLY 'double reads ,keep Dim textline, tdir As String, tfilname As String, dirStore As String Dim findstrStart As String, findstrEnd As String, nPdir As String, comtext As String Dim printLineflag As Boolean Dim tlcount As Integer, SEP As String, bs As String, FileCount As Integer Dim shName As String, startline, lastline tfilname = ThisWorkbook.Sheets("kkontrol").[act].Value tlcount = 0 tdir = "" dirStore = "" printL ineflag = False 'dont PRN straitaway nPdir = "" SEP = "," bs = "\" Sheets.Add shName = "files " & Format(Date, "yyyymmdd") ActiveSheet.Name = shName Rows("1:1").RowHeight = 58.5 Columns("A:A").ColumnWidth = 22.71 FileCount = 0 findstrStart = " Directory of ": findstrEnd = " File(s)" 'fext = "TXT" startline = 1 lastline = startline + 5000 'TEST'TEST'TEST'TEST'TEST'TEST'TEST 'Set outcell = Range("a1") Open tdir & bs & tfilname For Input As #1 ' Open file. Open tdir & bs & "COMDIR_" & tfilname For Output As #2 ' Do While Not EOF(1) ' Loop until end of file. Line Input #1, textline ' Read line into variable. tlcount = tlcount + 1 'count every line FileCount = FileCount + 1 If Len(textline) > 0 Then 'no blanks If InStr(1, textline, findstrStart) > 0 Then 'anywhare in line; 2012 -1 'store the directory'needs strippin dirStore = Right(textline, Len(textline) - Len(" Directory of ")) printLineflag = True ElseIf InStr(1, textline, findstrEnd) > 0 Then ' -2 'fould end line printLineflag = False ElseIf InStr(1, textline, nPdir) > 0 Then ' -3 'do nothing ElseIf printLineflag Then ' -4 comtext = _ Right(textline, Len(textline) - 36) & SEP & _ Application.WorksheetFunction.Substitute(Trim(Mid(textline, 18, 18)), ",", "") & SEP & _ Left(textline, 10) & SEP & Trim(Mid(textline, 11, 7)) Print #2, dirStore & SEP & comtext Debug.Print dirStore & SEP & comtext Cells(FileCount, 1) = Right(textline, Len(textline) - 36) Cells(FileCount, 2) = Application.WorksheetFunction.Substitute(Trim(Mid(textline, 18, 18)), ",", "") Cells(FileCount, 3) = Left(textline, 10) Cells(FileCount, 4) = Trim(Mid(textline, 11, 7)) End If ' -5 End If 'no blank line If tlcount > lastline Then GoTo exit_loop: Loop exit_loop: Close #2 Close #1 ' Close file. MsgBox ("end") End Sub '''tarmac uty Sub get_G_o_o_g_l_e_TARMAC() '27/10/2016 10:48:11 hdelay = 2 sstring = ThisWorkbook.Sheets("linksTARMAC").[my_email].Value ThisWorkbook.Sheets("linksTARMAC").[goolinkTAR].Hyperlinks(1).Follow Breather 3 * hdelay SendKeys sstring Breather hdelay SendKeys "{ENTER}" Exit Sub 'this lot is not needed but you will need to verify with answerOkta sstring = ThisWorkbook.Sheets("linksTARMAC").[answerOkta].Value Breather hdelay * 2 SendKeys "^{TAB}" Breather hdelay SendKeys "{TAB}" Breather hdelay SendKeys "{TAB}" SendKeys sstring Breather hdelay * 2 SendKeys "{ENTER}" sstring = ThisWorkbook.Sheets("linksTARMAC").[my_email].Value Breather hdelay SendKeys sstring ' email again Breather hdelay * 2 SendKeys "{TAB}" sstring = ThisWorkbook.Sheets("linksTARMAC").[FJE12345].Value 'what is this ... Breather hdelay SendKeys sstring ' email again Breather hdelay * 2 SendKeys "{ENTER}" End Sub Sub indirectRanger() ' many restrictions ... 'names OK ,columns OK,ROWS OR ' A1 references only cll refs only ' non contiguous WORKS!! Debug.Print Selection.Value Range(Selection.Value).Activate End Sub Sub SetUpTable() 'from MS example SDeleter Worksheets.Add '("Sheet1").Activate For TheYear = 1 To 5 Cells(1, TheYear + 1).Value = 1990 + TheYear Next TheYear For TheQuarter = 1 To 4 Cells(TheQuarter + 1, 1).Value = "Q" & TheQuarter Next TheQuarter 'note range arguments Range("b2", "f5").Formula = "=Rand()" End Sub Function lotteryArray() As Variant Dim lcol, lchk, lotto(6), mpos, isl As Integer Randomize restart: lotto(1) = Int((59 * Rnd) + 1) Lotstr = Lotstr & " " & lotto(1) For lcol = 2 To 6 samenum: lotto(lcol) = Int((59 * Rnd) + 1) For lchk = 1 To lcol - 1 If lotto(lcol) = lotto(lchk) Then GoTo samenum Next lchk Lotstr = Lotstr & " " & lotto(lcol) 'why?? not sorted yet! Next lcol '6 different numbers at this stage For isl = 1 To 6 'pass loop For imin = 1 To 5 'isl ? swaps in a pass ''''Debug.Print isl; h; imin; h; lotto(imin); h; lotto(imin + 1) 'testing only If lotto(imin + 1) < lotto(imin) Then lcol = lotto(imin + 1) lotto(imin + 1) = lotto(imin) lotto(imin) = lcol End If Next imin ' Next isl '6 different numbers at this stage in asc order lotteryArray = lotto End Function Sub lrun() Const NUMTrials As Long = 1000000 Dim lo As Variant, loFix As Variant, L_collect(0 To 6) As Long, _ numBall As Integer loFix = lotteryArray For itrial = 1 To NUMTrials lo = lotteryArray numBall = lcomp(lo, loFix) L_collect(numBall) = L_collect(numBall) + 1 Next itrial SDeleter Sheets.Add For irow = 0 To 6 [c3].Cells(irow, 1).Value = irow [c3].Cells(irow, 2).Value = L_collect(irow) [c3].Cells(irow, 3).Value = L_collect(irow) / NUMTrials Next irow [e2:e8].NumberFormat = "0.00%" End Sub Function lcomp(lo As Variant, loFix As Variant) As Integer lcomp = 0 For isl = 1 To 6 'each ball selected For imin = 1 To 6 'isl ? swaps in a pass If lo(isl) = loFix(imin) Then lcomp = lcomp + 1 Exit For ' this ball won't match more than once End If Next imin ' Next isl End Function Function lottery2017() As String Dim lcol, lchk, lotto(6), slotto(6), mpos, isl As Integer Dim Lotstr As String, sLotstr As String, h As String lottr = "Your lottery numbers are :" sLotstr = "=>" h = "-" Randomize restart: lotto(1) = Int((59 * Rnd) + 1) Lotstr = Lotstr & " " & lotto(1) For lcol = 2 To 6 samenum: lotto(lcol) = Int((59 * Rnd) + 1) For lchk = 1 To lcol - 1 If lotto(lcol) = lotto(lchk) Then GoTo samenum Next lchk Lotstr = Lotstr & " " & lotto(lcol) 'why?? not sorted yet! Next lcol 'Debug.Print Lotstr 'testing only '6 different numbers at this stage For isl = 1 To 6 'pass loop For imin = 1 To 5 'isl ? swaps in a pass ''''Debug.Print isl; h; imin; h; lotto(imin); h; lotto(imin + 1) 'testing only If lotto(imin + 1) < lotto(imin) Then lcol = lotto(imin + 1) lotto(imin + 1) = lotto(imin) lotto(imin) = lcol End If Next imin ' Next isl '6 different numbers at this stage in asc order 'If lotto(1) < 20 Then GoTo restart 'finding nos startin at 20! For isl = 1 To 6 sLotstr = sLotstr & " " & lotto(isl) Next isl Lotstr = "" 'dresponse = MsgBox(Lotstr & sLotstr, , lottr) lottery2017 = sLotstr 'MsgBox ("end of lotto") 'Sheets(shname).Delete 'Application.DisplayAlerts = True End Function Sub PasTriangle100() SDeleter Sheets.Add 'set up triangle For ir = 1 To 100 Cells(ir, 1).Value = 1 Cells(ir, ir).Value = 1 Next 'Fill Values For ir = 3 To 100 For ic = 2 To 100 If Cells(ir, ic).Value = 1 Then Exit For 'reached end of PT row Cells(ir, ic).Value = Cells(ir - 1, ic).Value + Cells(ir - 1, ic - 1).Value Next ic Next ir End Sub Sub aa_aa_aSKatMAC() ''nullified screen saver '05/07/2016 15:25:31 'apSwitch = "%{TAB}" 'SendKeys apSwitch 'switch applications.Manually as usual.. '' MORE SUBTLE keys to move 'simple s l i d e s h o w should hold ss open tssep = "{RIGHT}" ' "{TAB}" 'Breather 2 'SendKeys "%{F11}", True Breather 5 ' Breather 20 For i = 1 To 90 'SendKeys "" & Now Breather 10 SendKeys tssep Next i End Sub Sub memorablepwd17() Randomize Const VOWELS As String = "aeiou" Const CONSONANTS As String = "bcdfghjklmnpqrst vwxyz" Dim vl As Variant, cl As Variant Dim pwlength As Integer, leC As Integer Dim switch As Integer pwlen = 80 switch = -1 If Rnd > 0.4 Then switch = -1 'vowels less often vl = splitChars(VOWELS) cl = splitChars(CONSONANTS) Debug.Print For leC = 1 To pwlen If switch = -1 Then Debug.Print vl(1 + Int(UBound(vl) * Rnd)); Else Debug.Print cl(1 + Int(UBound(cl) * Rnd)); End If switch = switch * -1 'alternates Next leC Debug.Print End Sub