vba questions

[VegasNath]

Hello,

I have a few vba questions if I may pick loungers brains.......

1. When writing vba code, I don't like to specify (full) workbook paths. This is for two reasons, using the same code in live and test regions, and also if folder structures slightly change. Therefore I use code like:

strPath1 = ThisWorkbook.Path & "\"
strPath2 = ThisWorkbook.Path & "\" & "Day" & "\"
....etc

Is it possible, from "ThisWorkbook.Path" to obtain the folder 'back a level'? IOW, if "ThisWorkbook.Path" = P:\1\2\3, how do I get to P\1\2?

2. How can I get the file count in a specific folder?

3. How to copy all files from one folder to another, and prefix each file name with a date?

Thanks for any suggestions.

[HansV]

1. Use code like this:

Code: Select all

Dim fso As Object
Dim fld As Object
Dim strParent As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(ThisWorkbook.Path)
strParent = fld.ParentFolder.Path

strParent will contain the path of the parent folder of the folder containing the workbook with the code.

2. Use code like this:

Code: Select all

Dim fso As Object
Dim fld As Object
Dim strPath As String
Dim lngCount As Long
strPath = "..."
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(strPath)
lngCount = fld.Files.Count

lngCount will contain the number of files in the folder specified in strPath.

3. Use code like this:

Code: Select all

Dim fso As Object
Dim fld As Object
Dim fil As Object
Dim strSourcePath As String
Dim strTargetPath As String
strSourcePath = "..."
strTargetPath = "..."
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(strSourcePath)
For Each fil In fld.Files
  fil.Copy Destination:=strTargetPath & "\" & Format(Date, "yyyymmdd") & fil.Name
Next fil

You can modify this to suit your needs, of course.

[VegasNath]

Brilliant Hans, Thanks very much!

[Rudi]

Agreed...Brilliant Hans.

Now my questions:

1. What does the ellipse mean in this line of code? >>> strSourcePath = "..."
2. >>> CreateObject("Scripting.FileSystemObject") - Does this allow one to collect info from the system outside of the Excel Application Object?

And here I thought the only way to get a list of files from a directory (or count the amount of files in a folder) was using the DIR function in VBA.

[HansV]

1. The ... should be replaced with the appropriate path. I used the ellipsis because I don't know which path Nathan wants to use.

2. You can use CreateObject to use other object libraries than the standard ones. I'm using late binding here, so that it's not necessary to set a reference to the Microsoft Scripting Runtime library. See this post in the Windows Secrets Lounge for an explanation of the difference between early binding and late binding.

[Rudi]

Tx Slinky

Like Don and Alan said...great explanation...(but also far above what I have been exposed to and probably will need to know). Great to learn though!

[VegasNath]

Another one, slightly modified....

Code: Select all

Dim arrFiles As Variant
Dim varFile As Variant
Dim strPathT As String, strPath4 As String
Dim folY As String, folM As String, folD As String

folY = "2010"
folM = "3. Mar 2010"
folD = "26 Mar 2010"

strPathT = strParnt & "\" & "Files" & "\"
strPath4 = strParnt & "\" & "Daily Files" & "\" & folY & "\" & folM & "\" & folD & "\" & "Reports" & "\"

    arrFiles = Array( _
        strPathT & "XYZ4257 TEMPLATE.xls", _
        strPathT & "XYZ4258 TEMPLATE.xls", _
        strPathT & "XYZ4259 TEMPLATE.xls")

    For Each varFile In arrFiles
        FileCopy varFile, strPath4 & (Left(varFile.Name, 8) & Format(folD, "dd-mm-yy") & ".xls")
    Next varFile

Can anyone see why I am getting a 'RTE 424 - Object required' error on the last but one line?

[HansV]

varFile is the filename, so you shouldn't use varFile.Name. Simply use varFile instead.

[VegasNath]

Thanks Hans, but now I am getting 'RTE 52 - Bad file name or number'.

[HansV]

varFile is the full name, including the path. So you're repeating the path by using strPath4 and Left(varFile, 8).

[VegasNath]

Ok, I understand what you mean, but cannot see how to correct it?

[HansV]

Don't include the path in the array values.

[VegasNath]

3. Use code like this:

Code: Select all

Dim fso As Object
Dim fld As Object
Dim fil As Object
Dim strSourcePath As String
Dim strTargetPath As String
strSourcePath = "..."
strTargetPath = "..."
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(strSourcePath)
For Each fil In fld.Files
  fil.Copy Destination:=strTargetPath & "\" & Format(Date, "yyyymmdd") & fil.Name
Next fil

You can modify this to suit your needs, of course.

Hans, Thankyou for your help.

How can I adjust this so that the date becomes a suffix? I'm having difficulty working out how to lose the file extention?

[HansV]

You could add the following declarations at the beginning:

Dim strFilename As String
Dim strExtension As String
Dim intPos As Integer
Dim strNewName As String

Change the code within the For ... Next loop to

' Get the filename
strFilename = fil.Name
' Get position of last . in name
intPos = InStrRev(strFilename, ".")
' Filename without extension
strFilename = Left(strFilename, intPos - 1)
' Extension, including the .
strExtension = Mid(strFilename, intPos)
' Assemble new name
strNewName = strTargetPath & "\" & strFilename & Format(Date, "yyyymmdd") & strExtension
' Copy the file
fil.Copy Destination:=strNewName

[VegasNath]

strExtension is returning "" ?

[HansV]

Sorry, that's what you get if you write air code. Two instructions have to be switched, change

' Filename without extension
strFilename = Left(strFilename, intPos - 1)
' Extension, including the .
strExtension = Mid(strFilename, intPos)

to

' Extension, including the .
strExtension = Mid(strFilename, intPos)
' Filename without extension
strFilename = Left(strFilename, intPos - 1)