Results 1 to 17 of 17

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

Threaded View

Peter Kallio Loop through multiple files,... 08-16-2013, 03:12 AM
Naveed Raza Re: Loop through multiple... 08-16-2013, 09:15 AM
Peter Kallio Re: Loop through multiple... 08-17-2013, 11:57 PM
HaHoBe Re: Loop through multiple... 08-18-2013, 03:28 AM
Naveed Raza Re: Loop through multiple... 08-18-2013, 04:03 AM
Peter Kallio Re: Loop through multiple... 08-20-2013, 02:22 AM
Naveed Raza Re: Loop through multiple... 08-20-2013, 02:46 AM
Peter Kallio Re: Loop through multiple... 08-20-2013, 03:20 AM
HaHoBe Re: Loop through multiple... 08-20-2013, 11:36 AM
Peter Kallio Re: Loop through multiple... 08-22-2013, 06:24 AM
Naveed Raza Re: Loop through multiple... 08-22-2013, 07:30 AM
HaHoBe Re: Loop through multiple... 08-22-2013, 12:22 PM
duncandhu Re: Loop through multiple... 08-22-2013, 12:52 PM
Peter Kallio Re: Loop through multiple... 08-23-2013, 02:19 AM
HaHoBe Re: Loop through multiple... 08-23-2013, 12:57 PM
Peter Kallio Re: Loop through multiple... 08-26-2013, 09:48 PM
HaHoBe Re: Loop through multiple... 08-26-2013, 11:42 PM
  1. #1
    Registered User
    Join Date
    08-15-2013
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

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

    This is my first post to the forum. I have 115 files all of which have been downloaded from my GPS using GPS Utility software. They are all in GPSU(.asc) format and reside in a single folder. The files can quite easily be converted to Excel and I have recorded a macro (Walktime) which will convert each .asc file singly to Excel, then do several calculations and prepare a data summary within that workbook. My macro does not save the workbook. I have devised some VBA code which loops through all .asc files, runs my macro, and then saves the resultant workbook in xlsx format and with the same filename as the .asc file. My problem is that the Excel workbook is saved in the same folder that contains the .asc files leaving me with a mix of both. What I want to do is keep all .asc files together in one folder, run my code on files within that folder, then save all Excel workbooks in another folder. The VBA code I am using is as follows:

    Sub LoopAllFilesTemp()
    
        Dim sPath As String, sDir As String
        
        sPath = "e:\My Documents\Bushwalking\Temp\"
        
        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
               
                .Close
            End With
            
            sDir = Dir$
        Loop
        
    End Sub
    The above works fine but I do not know how to save the Excel files to a folder other than "Temp". Can someone help me please. I use Excel 2010.

    Thanks

    Peter Kallio
    Last edited by jeffreybrown; 08-17-2013 at 11:59 PM. Reason: As per Forum Rule #3, please use code tags…Thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to create a folder and then save all files in that folder
    By gokzee in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-27-2013, 01:49 PM
  2. Macro Needed to Loop, Save, and Name Excel Files as PDFs
    By ahkarchem in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2012, 10:46 AM
  3. Macro to loop through all files in a folder troubleshooting (excel for mac)
    By TSMIII in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-31-2011, 09:31 AM
  4. Run macro for multiple excel files in a folder
    By ciprian in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-30-2011, 05:12 PM
  5. exce; macro to open, save embedded pdf, word, excel, ppt files to a folder
    By mcledavid in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2009, 05:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1