Loop through multiple files, run macro, then save as Excel workbook in a different folder

Naveed. From your post a couple of days ago, I get the impression that you might be able to help. I look forward to hearing further from you.

Peter

Thanks Naveed and Holger; your help was much appreciated. Neither code offered worked. In both cases I received "Run-time error 1004 - The file could not be accessed". The problem was in the "SaveAs line of code after running my Walktime macro. As I indicated in my original post, my code worked ok but saved the .xlsx files in the same folder as the .asc files. I wanted to separate the two groups of files into different folders. I have resolved the issue by inserting a line of code to also save the files to another folder (Excel Files) thus saving the files twice, once in each of two folders. I then introduced another line of code to delete all .xlsx file from the Temp folder. What I have done might seem a bit clumsy, but it works. My revised code is as follows:

Sub LoopAllFilesTemp()

Dim sPath As String, sDir As String

sPath = "e:\My Documents\Bushwalking\Temp\"
dPath = "e:\My Documents\Bushwalking\Excel Files\"

If Right(sPath, 1) <> "\" Then sPath = sPath & "\"

sDir = Dir$(sPath & "*.asc", vbNormal)
Do Until Len(sDir) = 0

Workbooks.Open (sPath & sDir)

Call WalktimeTemp


With ActiveWorkbook

.SaveAs Filename:=Left(.FullName, InStrRev(.FullName, ".")) & "xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveWorkbook.SaveAs dPath & ActiveWorkbook.Name
Kill "e:\my Documents\Bushwalking\Temp\*.xls"
.Close
End With

Thanks again

Peter

sDir = Dir$
Loop

End Sub

Here is the code. It was compiled using the Record Macro feature in Excel.

Sub WalktimeTemp()
'
' WalktimeTemp Macro
'
'
'This Macro opens an ".asc" file in Excel, then summarises data and calculates walking speed.
'Open ".asc" file in Excel.

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("A1").Select


'Remove extraneous fields and data from beginning and end of file.
'Select column "K" which contains no irrelevant data at beginning or end.
Columns("K:K").Select
'Find blank cells in column "K".
Selection.SpecialCells(xlCellTypeBlanks).Select
'Delete rows containing blank column "K" cells.
Selection.EntireRow.Delete
'Delete first 10 rows.
Rows("1:12").Select
Selection.Insert Shift:=xlDown
'Insert column headings.
Range("B1").Select
ActiveCell.FormulaR1C1 = "Zone"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Easting"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Northing"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Elevation"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Clock"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Km"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Km/Hr"
Range("l1").Select
ActiveCell.FormulaR1C1 = "Walk Time"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Ascents (m)"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Descents (m)"
'Insert Summary Table headings.
Range("c3").Select
ActiveCell.FormulaR1C1 = "Date"

Range("c4").Select
ActiveCell.FormulaR1C1 = "Start Time"
Range("c5").Select
ActiveCell.FormulaR1C1 = "End Time"
Range("c6").Select
ActiveCell.FormulaR1C1 = "Elapsed Time"
Range("c7").Select
ActiveCell.FormulaR1C1 = "Walk Time"
Range("c8").Select
ActiveCell.FormulaR1C1 = "Distance"
Range("c9").Select
ActiveCell.FormulaR1C1 = "Km per Hr"
Range("G4").Select
Selection.Font.Underline = xlUnderlineStyleSingle
ActiveCell.FormulaR1C1 = "Elevation (m)"
Range("F5").Select
ActiveCell.FormulaR1C1 = "Start"
Range("F6").Select
ActiveCell.FormulaR1C1 = "Finish"
Range("F7").Select
ActiveCell.FormulaR1C1 = "Max"
Range("F8").Select
ActiveCell.FormulaR1C1 = "Min"
Range("F9").Select
ActiveCell.FormulaR1C1 = "Ascents"
Range("F10").Select
ActiveCell.FormulaR1C1 = "Descents"
'Set column widths.
Columns("G:G").ColumnWidth = 12.43
Columns("C:C").ColumnWidth = 13.43
Columns("D:D").ColumnWidth = 11
Columns("F:F").ColumnWidth = 11.57
Columns("M:M").ColumnWidth = 10.86
Columns("N:N").ColumnWidth = 12.57

'Formatting.
Range("D4:D7").Select
Selection.NumberFormat = "[h]:mm:ss"
Range("D8:d9").Select
Selection.NumberFormat = "0.00"
Columns("L:L").Select
Selection.NumberFormat = "[h]:mm:ss"
Range("G5:G10").Select
Selection.NumberFormat = "0.0"
'Calculate "Walk Times" in Column "L".
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]<1,"""",IF(R[-1]C[-1]="""","""",RC[-5]-R[-1]C[-5]))"
Range("L2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Range("L2:L3000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Calculate Ascents in Column "M".
Range("M14").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]>R[-1]C[-8],RC[-8]-R[-1]C[-8],"""")"
Range("M14").Select
Selection.Copy
Range("M14:M3000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Calculate Descents in Column "N".
Range("N13").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",IF(RC[-9]<R[-1]C[-9],R[-1]C[-9]-RC[-9],""""))"
Range("N13").Select
Selection.Copy
Range("N13:N3000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Insert Date in Summary Table.
Range("D3").Select
ActiveCell.FormulaR1C1 = "=R[10]C[2]"
'Insert "Start Time" in Summary Table.
Range("D4").Select
ActiveCell.FormulaR1C1 = "=R[9]C[3]"
'Insert "End Time in Summary Table".
Range("D5").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(2,1/(C[3]<>""""),C[3])"
'Calculate and insert "Elapsed Time in Summary Table".
Range("D6").Select
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C"
'Calculate and insert "Walk Time" in Summary Table.
Range("D7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C[8]:R[2993]C[8])"
'Calculate and insert "Distance" in Summary Table.
Range("D8").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(2,1/(C[6]<>""""),C[6])"
'Calculate and insert walk speed as "Km/Hr" in Summary Table.
Range("D9").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/R[-2]C/24"
'Insert elevation at start in Summary Table.
Range("G5").Select
ActiveCell.FormulaR1C1 = "=R[8]C[-2]"
'Insert elevation at end in Summary Table.
Range("G6").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(2,1/(C[-2]<>""""),C[-2])"
'Calculate and insert maimum elevation in Summary Table.
Range("G7").Select
ActiveCell.FormulaR1C1 = "=MAX(C[-2])"
'Calculate and insert minimum elevation in Summary Table.
Range("G8").Select
ActiveCell.FormulaR1C1 = "=MIN(C[-2])"
'Calculate and insert Total Ascents in Summary Table.
Range("G9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[4]C[6]:R[2991]C[6])"
'Calculate and insert Total Descents in Summary Table.
Range("G10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[7]:R[2990]C[7])"
Rows("3:11").Select
Selection.Copy
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select

End Sub

Hello Holger

I have included Code Tags as you requested. As I said in my last post, my code works ok but might be a little clumsy and could be streamlined to make it faster. I intend to work on that in slower time in the future. What I have done might be of assistance to others. Feel free to comment on my code if you wish and have time, but I will close the thread in a week's time.

Regards

Peter

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi Peter,

Am I right in thinking that all you want the macro to do different is save the files to a different folder?

If so, then the line

Please Login or Register  to view this content.

is the culprit.

You should replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

.FullName has the whole file path in it, while .Name has just the filename.

Let us know if that works (if that is indeed the problem).

Thanks,
Duncan

Thanks, Duncan. You have stated my problem spot-on. I have already tried what you suggested and it doesn't work. I get a syntax error. My latest posted method of saving it separately to my desired destination EXCEL folder, then deleting files already saved to the ASC folder works ok, but there must be a better way. I am not too concerned at the moment because I have something that works.

Peter

Thanks Holger; that works fine, and solves my problem. Sorry for the delay in getting back to you, but my computer has taken a dislike to the forum and I cannot access it.
Regards

Peter