+ Reply to Thread
Results 1 to 5 of 5

File that opens all .xls files in a folder (make some actions in each) and then close it

  1. #1
    Registered User
    Join Date
    05-20-2013
    Location
    Cyprus
    MS-Off Ver
    Excel 2007
    Posts
    6

    File that opens all .xls files in a folder (make some actions in each) and then close it

    The code i wrote was working perfectly until i openned an irrelevant .xls file in another folder and saved it
    From that moment, when i run the code it tries to open the file that i saved (the irrelevant one) from the folder i specify in the code and of course it cannot find it
    I suspect that the code tries to open the most recent saved file

    here is the code


    Dim oWbk As Workbook
    Dim sFil As Variant
    Dim sPath As Variant

    Dim i As Integer
    i = 2



    ' The following path is the location of the folder where the spread files are saved
    'sPath = Sheets("Spreads List").Cells(j, 16366).Value
    sPath = "\\ciserver1\spreads\New Spreads\MENA\2012\"
    ChDir sPath
    sFil = Dir("*.xls") 'this is the type of files that we want to open that are saved in the above path
    Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through


    ' every time a file (i.e. workbook) is openned, it gives it the name oWbk
    Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

    ' The following is the full path of the file openned
    myname = oWbk.path & "\" & oWbk.Name


    ' Copies the name of the issuer from oprnnrd workbook in sheets "bank" cells(1,1)
    oWbk.Worksheets("RATIOS").Cells(1, 1).Copy
    'Pastes the name in the "Spreads List" sheet
    Windows(Credit_Score_Workbook).Activate
    Sheets("Spreads List").Activate
    Cells(i, 16360).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    'Copies the file path in "Spreads List" sheet
    Cells(i, 16361) = myname
    i = i + 1

    Application.DisplayAlerts = False
    oWbk.Close
    Application.DisplayAlerts = True
    ' oWbk.Close True 'close the workbook, saving changes


    Loop ' End of LOOP

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: File that opens all .xls files in a folder (make some actions in each) and then close

    Please Login or Register  to view this content.
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    05-20-2013
    Location
    Cyprus
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: File that opens all .xls files in a folder (make some actions in each) and then close

    thanks patel but now tho code can run BUT it keeps opening the first file (in the folder) again and again instead of opening all files one by one

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: File that opens all .xls files in a folder (make some actions in each) and then close

    Please Login or Register  to view this content.
    Last edited by patel45; 05-20-2013 at 08:15 AM.

  5. #5
    Registered User
    Join Date
    05-20-2013
    Location
    Cyprus
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: File that opens all .xls files in a folder (make some actions in each) and then close

    Thanks patel,
    what you suggested works perfect
    Last edited by kgiorgosss; 05-20-2013 at 08:57 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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