+ Reply to Thread
Results 1 to 9 of 9

Check Directory and Convert .xls to .xlsx File

  1. #1
    Forum Contributor
    Join Date
    02-23-2015
    Location
    SG
    MS-Off Ver
    MS Excel 2016
    Posts
    135

    Check Directory and Convert .xls to .xlsx File

    I have the code to check the.xls file in the directory and open and convert them to.xlsx.

    I encountered an error when the.xlsx file had already been converted and was present in the folder, but the code would prompt me if I wanted to save, etc., and it would loop indefinitely.

    If a duplicated file exists, I want the code to stop and prompt the user.

    any help would appreciate.


    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    10 ... 21 - 32 & 64
    Posts
    2,840

    Re: Check Directory and Convert .xls to .xlsx File

    Easiest is to simply check first if the new filename already exists but you cannot use DIR in the middle of a loop where you already use DIR without parameters to get the next file in the loop.

    You should be able to use the FSO to check if a file exists. Create a new function and pass the (new) file name. If it returns True then you need to decide what to do.
    Please Login or Register  to view this content.
    You'll probably need to pass a fully qualified ('C:\dir\dir...XLSX) filename, so test that.

    (typed freehand so no testing but the general idea holds)
    Last edited by cytop; 05-05-2022 at 03:22 AM.

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,565

    Cool Try the classic Dir way


    A VBA basics demonstration for starters :

    PHP Code: 
    Sub Demo1()
        
    Dim F(), DL&, NAs Boolean
      ReDim F
    (1 To 1)
            
    Dir(ThisWorkbook.Path "\*.xls"):  If "" Then Beep: Exit Sub
        
    Do
            
    1
            ReDim Preserve F
    (1 To L)
            
    F(L) = "\" & D
            D = Dir
        Loop Until D = ""
        With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
        For Each D In F
            N = ThisWorkbook.Path & Replace(D, "
    .xls", ".xlsx")
            If Dir(N) > "" Then B = MsgBox("
    Overwrite " & N & " ?", vbYesNo) = vbYes Else B = True
            If B Then
                With Workbooks.Open(ThisWorkbook.Path & D, 0)
                    .SaveAs N, 51
                    .Close
                End With
            End If
        Next
            .DisplayAlerts = True
            .ScreenUpdating = True
            .Speech.Speak "
    Done !"
        End With
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 05-05-2022 at 06:05 AM. Reason: simplification

  4. #4
    Forum Contributor
    Join Date
    02-23-2015
    Location
    SG
    MS-Off Ver
    MS Excel 2016
    Posts
    135

    Re: Try the classic Dir way

    Thanks, Marc L.
    Your code works.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,565

    Lightbulb A variation


    Quote Originally Posted by cytop View Post
    You'll probably need to pass a fully qualified ('C:\dir\dir...XLSX) filename
    Rather than slowing down the execution by creating a new useless variable object each time the function is called
    the better is to use an unique object directly in the VBA procedure :

    PHP Code: 
    Sub Demo2()
        
    Dim P$, F$, N$, As Boolean
            P 
    ThisWorkbook.Path "\"
            F = Dir$(P & "
    *.xls"):  If F = "" Then Beep: Exit Sub
        With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
        With CreateObject("
    Scripting.FileSystemObject")
            Do
                N = P & Replace$(F, "
    .xls", ".xlsx")
                If .FileExists(N) Then B = MsgBox("
    Overwrite " & N & " ?", 4) = 6 Else B = True
                If B Then With Workbooks.Open(P & F, 0): .SaveAs N, 51: .Close: End With
                F = Dir$
            Loop Until F = ""
        End With
            .DisplayAlerts = True
            .ScreenUpdating = True
            .Speech.Speak "
    Done !"
        End With
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !

  6. #6
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    10 ... 21 - 32 & 64
    Posts
    2,840

    Re: Check Directory and Convert .xls to .xlsx File

    I'll take that as an invitation to criticise your posts on a 'commercial level' rather than a quick, and free, suggestion to get code for a beginner working but, just for your info, it was culled from a project where it was used as a general file exists function.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,565

    Arrow Re: Check Directory and Convert .xls to .xlsx File


    So you misread or did not well understand
    As for example if your function is called for 1 000 files so it will create 1 000 FSO objects (with useless variable not released)
    versus my sample just needing an unique object whatever the files #

    And as it is your post #2 raises an error !
    Last edited by Marc L; 05-05-2022 at 07:52 PM.

  8. #8
    Forum Contributor
    Join Date
    02-23-2015
    Location
    SG
    MS-Off Ver
    MS Excel 2016
    Posts
    135

    Re: A variation

    I'd want to ask for assistance in revisiting the code (#3 & #5).
    When I copy the entire directory to a different drive or location and run the code, the folder displays some additional file .xlsxx file. The excel screen will be blank.
    I search but can't seem to find anything related to .xlsxx file.

    The code as follow:-
    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by cyliyu; 05-09-2022 at 06:33 AM.

  9. #9
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,867

    Re: A variation

    Quote Originally Posted by Marc L View Post
    Rather than slowing down the execution by creating a new useless variable object each time the function is called
    the better is to use an unique object directly in the VBA procedure :
    Alternatives that offer improvements are always welcome but the word "useless" in italics was entirely unnecessary here and is pejorative. Avoid subjective judgements and stick to the facts.
    Jeff
    | | || || || || | |:| | || ||
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Convert CSV file to XLSX without saving it
    By fabian_76 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-05-2021, 10:25 AM
  2. [SOLVED] Convert File from xlsm to xlsx
    By Bill1234 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-28-2018, 02:49 AM
  3. .XLS file won't convert properly to .XLSX
    By oldmaven in forum Excel General
    Replies: 19
    Last Post: 01-24-2017, 03:58 PM
  4. Help with code to convert xls to xlsx and continue with xlsx file
    By 3345james in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-02-2016, 11:47 AM
  5. convert all xlsx in directory to text using vb
    By cmccabe in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-28-2015, 09:52 PM
  6. [SOLVED] convert xlsx to csv and save to file directory
    By shaz0503 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-27-2014, 07:19 PM
  7. How to convert a file to xlsx
    By Raner in forum Excel General
    Replies: 2
    Last Post: 12-15-2011, 03:27 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