VBA Copy missing files

Public Sub CopyMissingFiles(sProdFiles As String, sOfflineFiles As String, sFileExt As String)
Dim i As Integer
Dim sDestination As String
Dim iFiles As Integer
'FileExt = "*.pdf"

'Add \ at end if missing
If Not Right(sProdFiles, 1) = "\" Then sProdFiles = sProdFiles & "\"
If Not Right(sOfflineFiles, 1) = "\" Then sOfflineFiles = sOfflineFiles & "\"

iFiles = 0

With Application.FileSearch
.NewSearch
.LookIn = sProdFiles
.FileName = sFileExt
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count
sDestination = Replace(.FoundFiles(i), sProdFiles, sOfflineFiles)
If Not FileOrDirExists(sDestination) Then
'Debug.Print .FoundFiles(i)
FileCopy .FoundFiles(i), sDestination
iFiles = iFiles + 1
End If
Next i
End With

If iFiles > 0 Then
MsgBox "Copied " & iFiles & " pdf files to " & sOfflineFiles, vbInformation
End If

End Sub

Sub test_CopyMissingFiles()
Call CopyMissingFiles("D:\ProdTest\Files\", "D:\files\", "*.pdf")
End Sub

VBA Ado Example for Sql Server 2000/2005 etc...

Just use {SQL Server} for most...
Note: Backward compatibility isn't set automatically on sql server 2005/2008!
So one can use Sql Server Native Client - note - you need to download and install this small component from codeplex.....on each client pc that will need it.
Or you use normal oledb connections which work fine, once named pipes is enabled on sql 2005/2008!

Public Function AdoExample() As Long
Dim AdoRs As ADODB.Recordset
Dim AdoCn As ADODB.Connection
Dim sCon As String
Dim sSql As String
Dim sServerName As String
Dim sDatabaseName As String

On Error GoTo err_AdoExample

sServerName = "Database Server"
sDatabaseName = "Database"

'sCon = "Driver=SQLNCLI.1;DataTypeCompatibility=80;Server=" & sServerName & ";database=" & sDatabaseName & ";trusted_connection=yes;"
sCon = "driver={SQL Server};Server=" & sServerName & ";Database=" & sDatabaseName & ";trusted_connection=Yes;"
'sCon = "driver={SQL Native Client};Server=" & sServerName & ";Database=" & sDatabaseName & ";trusted_connection=Yes;"
Set AdoCn = New ADODB.Connection
AdoCn.ConnectionString = sCon
AdoCn.Open
Set AdoRs = New ADODB.Recordset
sSql = "Select @Version as Version"
AdoRs.Open sSql, AdoCn ', adOpenDynamic, adLockOptimistic

debug.print AdoRs!Version

AdoRs.Close
AdoCn.Close

Exit Function

err_AdoExample:

MsgBox err.Description & vbNewLine & err.Number, vbInformation
Resume Next

End Function

Regards,
Tom Bizannes
Sydney, Australia
Database Development and Auditing

Re: Index / Match versus Vlookup macro

Wow..

Vlookup is way too dodgy!

Index match returns the values looking up itmes in a column, but vlookup which should have had exact matches had issues with - in the names and came with with incorrect values....

=INDEX('Sheet2!C:E,MATCH(AG974,'Sheet2!C:C,0),3)

This checks for a match to cell AG974 in the other worksheet - 'Sheet2' and gets the value in column E...(The 3rd column in the columns selected)

Regards,
Tom Bizannes
Excel Macros and Development Sydney

Office Developer Conference 2008

This was really good.

Full kudos to Graham Seach for Organising this.

Couldn't go to all the sessions...but learnt a few new tricks.....

I presented Real World Access and Real World Excel...Two rapid fire ones about migrations to Office 2007 where we helped clients to upgrade microsoft office and discovered a few gotchas..

Wow these really stretch you...It's easier to talk for an hour than for 10 minutes!

Got back and now designing an Access 2007 picture gallery/viewer for your pc...
Access can read and show pictures in a directory without having to load them into the database.

Regards,
Tom

Re: Michael Noel and SharePoint Virtualisation

This was really good…

Michael Noel did a really good talk about virtualising SharePoint using either HyperV or Vmware. ( and he also mentioned no 3 in virtualisation - Cirtix Xen Server)

Still cannot believe Michael flew in from the states today and is flying out again today….just to talk to me and Peter regarding virtualising SharePoint…
Well actually there were a few others there….

He’s written a few SharePoint and isa server books and whitepapers for Microsoft etc…He knew his stuff.
One of his clients has 250,000 SharePoint Users!

Don’t virtualise Sql Server was one very important point…This is due to the high I/O of Sql Server
Next to go physical would be Index Server….
Other Virtuals only lose 2% to 3% of CPU performance so web servers do make a good case for Virtualisation.

Watch the NUMA boundaries…giving to much Ram for a virtual doesn’t equate to better performance if the NUMA boundary is hit..--> Too much paging etc...
Good rule of thumb … Ram per vm = Ram/CPUs…..
e.g two quad cores --> 8 cpus --> 64G of Ram --> 64/6 = 8G per virtual.

Hyper V guys need to get Ms Virtual Machine Manger 2008 – came out in Oct 2008…

Check out:
http://tinyurl.com/virtualsp
http://tinyurl.com/edgenoel2
http://tinyurl.com/edgenoel3

Virtual Machine Manager 2008 is out now….
System Centre Data Protection Manager 2007 great for shadow copies to restore sharepoint servers faster..

VMware has a cool tool P2V (free now!!) for copying physica to Virtual Machines…
Will need to email some people who asked me about this this year….

Big sites get vmware ESX or Citrix xen server 5 for vmotion or xen motion for great fail over if one server goes down.

Synergy and AvePoint have some SharePoint Replicators? … Will need to check these out…

Peter came along because apart from hyper v he is now playing with vmware---
It was good that Michael confirmed what I had hinted to the guys about -- don't virtualise SQL Server..That being said, up to 100 users should push sq server too far? Maybe not unless they are really using SharePoint by using an addon like Wisdom DMF where they can easily open and save their documents....Then take no chances...

Re: Sydney Sql Server User Group Tuesday 11th November 2008

BI was the topic.

This was slightly different than a canned demo as open conversation and comments were the go..

The main things one got out of this would be that the more you know MDX, the better you can design reports, build cubes and anlyse the data....

There was a hint that one can do MDX in Excel 2007....Will try this out.....That would enable you to do more than just slice and dice.....

e.g. For power analytics...

Office Dev in two weeks time!

The Office Development Conference is only two weeks away!

If you are thinking of going, you need to let them know via email so there will be enough Pizza!
Oh my gosh!...Will need to get my Cholesterol checked before this weekend!

Same as last year, cannot go to all the sessions I want to go to!

FYI – I am doing two quick rapid fire sessions, as I will be the only Excel Expert speaking apart from Grant at Angry Koala talking about data mining....
plus I couldn’t help myself and just had to do something on Access rather than just sit and correct people all the time!

Will have to hand around a few WISDOM cubes……

Regards,
Tom Bizannes
Sydney, Australia
http://www.macroview.com.au

PC versus Mac - you tube sending up the mac

They are cleverly done to send a certain message!

PC vs MAC - Money
The PC vs MAC war - from the PC's point of view.Written and directed by Laurie McGuinness. Watch the full series at www.lauriemcguinness.com


PC vs MAC - At work
The PC vs MAC war - from the PC's point of view.Written and directed by Laurie McGuinness. Watch the full series at www.lauriemcguinness.com


PC vs MAC - Music
The PC vs MAC war - from the PC's point of view. Written and directed by Laurie McGuinness. Watch the full series at www.lauriemcguinness.com


PC vs MAC - The girl
The PC vs MAC war - from the PC's point of view. Written and directed by Laurie McGuinness. Watch the full series at www.lauriemcguinness.com

Regards,

Tom Bizannes
Sydney Australia

Migrating to Office 2007

Microsoft has made this so much fun!

Well I'm being sarcastic...This is not a simple process if you have vba enhanced applications.

Many clients are now going through the process and asking us to assist.

Excel linked workbooks and vba saveas are just two that come to mind immediately.
Was asked to knock up a bit of code to report on which workbooks had links and/or macros that could break.
The saveas issue will cause weird issues complaining of compatibility errors when there aren't any...but there are!

Word 2007 has other issues as some of the vba is just a little bit different so search and replace act a little different.

Outlook 2007 does weird things to your Microsoft Sql Reporting Services Scheduled html reports...Now that's a mouthful to say all at once!

Had to upgrade a 2007 access application application that created a menu on the fly depending on the user's status in a staff list and activated or de-activated certain items. All that because Microsoft blew away the object permission model.
But the new queries are nice with built in totals etc...

Regards,
Tom Bizannes
Microsoft Office Soutions
Sydney Australia
http://www.macroview.com.au

City to Surf Times

Finally got my Certificate showing 86:53 as the 2008 time.

The City to surf guys emailed me the last few times as follows:
2000 - 124.26
2002 - 108.30
2006 - 104.39
2007 - 98.12
2008 - 86 minutes 53 seconds
2010 - 84 minutes 42 seconds
2011 - 85 minutes 48 seconds
2012 - 95 minutes 53 seconds
2013 - 82 minutes 8 seconds

Now to dig up the other times when I actually ran it properly.

Just ran a normal 14km on 1st June 2014 in 75 minutes and 12 seconds.
Got to get under 75 minutes for the first time in decades!