''
''#######
''# ## # # ##### ## #### ##### # ####
''# # # ## # # # # # # # # #
''##### # # # # # # # # #### # # #
''# ###### # # # # ###### # # # #
''# # # # ## # # # # # # # # #
''# # # # # # # # #### # # ####
''
''###### ### ###
''# # ##### #### #### ##### ## # # ### ###
''# # # # # # # # # # # # ## ## ### ###
''###### # # # # # # # # # # ## # # #
''# ##### # # # ### ##### ###### # #
''# # # # # # # # # # # # # ### ###
''# # # #### #### # # # # # # ### ###
''
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