+ Reply to Thread
Results 1 to 3 of 3

Problem saving/closing workbook as an excel spreadsheet

Hybrid View

  1. #1
    Registered User
    Join Date
    07-27-2013
    Location
    Roanoke
    MS-Off Ver
    Excel 2019
    Posts
    64

    Problem saving/closing workbook as an excel spreadsheet

    I'm tweaking a couple of VBScripts that I found online to open several .dat files in a folder, separate the data into columns using the text to column function, then I'm trying to save the file as an excel file. The problem, which you would think would be the easiest part, is saving the file. Any thoughts will be appreciated. Several code lines are disabled below (for example the save and close lines) as they currently don't work.

    Sub ImportDATFileLoopAllFilesInFolder2B()
    'PURPOSE: To loop through all DAT files in a user specified folder and perform a set task on them
    
    
    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim myExtension2 As String
    Dim FldrPicker As FileDialog
    
    'Optimize Macro Speed
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual
    
    'Retrieve Target Folder Path From User
      Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    
        With FldrPicker
          .Title = "Select A Target Folder"
          .AllowMultiSelect = False
            If .Show <> -1 Then GoTo NextCode
            myPath = .SelectedItems(1) & "\"
        End With
    
    'In Case of Cancel
    NextCode:
      myPath = myPath
      If myPath = "" Then GoTo ResetSettings
    
    'Target File Extension (must include wildcard "*")
      myExtension = "*.dat*"
      myExtension2 = ".xlsx"
     
    'Target Path with Ending Extention
      myFile = Dir(myPath & myExtension)
      'myFile2 = Dir(myPath & myExtension2)
      
      
    
    'Just to show how we auto adjust the width of column A.
    Columns("A:A").EntireColumn.AutoFit
    
    
    'Loop through each Excel file in folder
      Do While myFile <> ""
          
       
          DoEvents
        
       'We now import the selected file, and data is
    'inserted in a new spreadsheet. If you want to use
    'another delimiter, you must change "Semicolon:=True"
    'to "Semicolon:=False" and set another delimiter
    '(e.g. "Tab") to True.
    
        Workbooks.OpenText Filename:=myPath & myFile, _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=True, OtherChar:=Chr(124), _
        TrailingMinusNumbers:=True, Local:=True
    
        
        'Save and Close Workbook
        
        'wb.SaveAs myPath & myFile & myExtension2
         
        'wb.Close
                
          
        'Ensure Workbook has closed before moving on to next line of code
          DoEvents
    
        'Get next file name
          myFile = Dir
      Loop
    
    'Message Box when tasks are completed
      MsgBox "Task Complete!"
    
    ResetSettings:
      'Reset Macro Optimization Settings
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    
    End Sub
    Last edited by ecorf; 02-18-2024 at 01:01 PM.

  2. #2
    Registered User
    Join Date
    10-16-2023
    Location
    Poland
    MS-Off Ver
    Excel 2019 32bit WIN10
    Posts
    98

    Re: Problem saving/closing workbook as an excel spreadsheet

    Try this:
        ...
    
        'Save and Close Workbook
    
        With ActiveWorkbook
        
              .SaveAs myPath & Left(myFile, InStrRev(myFile, ".") - 1) & myExtension2, _
                      FileFormat:=xlOpenXMLWorkbook
              .Close
    
        End With
        ...
    Last edited by Tajan; 02-18-2024 at 12:30 PM.

  3. #3
    Registered User
    Join Date
    07-27-2013
    Location
    Roanoke
    MS-Off Ver
    Excel 2019
    Posts
    64

    Re: Problem saving/closing workbook as an excel spreadsheet

    Awesome Tajan! That works. Thank you! Here's the final script.

    Sub ImportDATFileLoopAllFilesInFolder2B()
    'PURPOSE: To loop through all DAT files in a user specified folder and perform a set task on them
    
    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim myExtension2 As String
    Dim FldrPicker As FileDialog
    
    'Optimize Macro Speed
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual
    
    'Retrieve Target Folder Path From User
      Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    
        With FldrPicker
          .Title = "Select A Target Folder"
          .AllowMultiSelect = False
            If .Show <> -1 Then GoTo NextCode
            myPath = .SelectedItems(1) & "\"
        End With
    
    'In Case of Cancel
    NextCode:
      myPath = myPath
      If myPath = "" Then GoTo ResetSettings
    
    'Target File Extension (must include wildcard "*")
      myExtension = "*.dat*"
      myExtension2 = ".xlsx"
     
    'Target Path with Ending Extention
      myFile = Dir(myPath & myExtension)
      
    
    'Just to show how we auto adjust the width of column A.
    Columns("A:A").EntireColumn.AutoFit
    
    
    'Loop through each Excel file in folder
      Do While myFile <> ""
            
        DoEvents
        
       'We now import the selected text file, and data is
    'inserted in a new spreadsheet. If you want to use
    'another delimiter, you must change "Semicolon:=True"
    'to "Semicolon:=False" and set another delimiter
    '(e.g. "Tab") to True.
        Workbooks.OpenText Filename:=myPath & myFile, _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=True, OtherChar:=Chr(124), _
        TrailingMinusNumbers:=True, Local:=True
    
        
        
        'Save and Close Workbook
    
        With ActiveWorkbook
        
              .SaveAs myPath & Left(myFile, InStrRev(myFile, ".") - 1) & myExtension2, _
                      FileFormat:=xlOpenXMLWorkbook
              .Close
    
        End With
           
          
        'Ensure Workbook has closed before moving on to next line of code
          DoEvents
    
        'Get next file name
          myFile = Dir
      Loop
    
    'Message Box when tasks are completed
      MsgBox "Task Complete!"
    
    ResetSettings:
      'Reset Macro Optimization Settings
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    
    End Sub

+ 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] How do you reset an Excel Spreadsheet after Saving/Closing
    By bdhobbs1 in forum Excel General
    Replies: 2
    Last Post: 07-26-2022, 10:23 PM
  2. [SOLVED] Saving and Closing workbook leaves a blank excel shell open, why?
    By cubangt in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-01-2021, 09:33 AM
  3. Prevent Excel workbook closing saving if the mandatory cells not filled in
    By pearlite007 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 02-07-2017, 05:12 PM
  4. Saving personal workbook before closing excel
    By Thor Almighty in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2015, 06:27 AM
  5. Creating new workbook, saving as csv and then closing - but closing always fails.
    By ella626 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-28-2014, 10:05 PM
  6. closing a workbook without saving and closing a workbook with saving enabled
    By stanigator in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2007, 02:15 PM
  7. [SOLVED] Excel 2002 aborts randomly when closing/saving workbook
    By Barbara Ryan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2006, 09:50 AM

Tags for this Thread

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