Results 1 to 3 of 3

Import Macro to import separate files to separate worksheets - deletes existing worksheets

Threaded View

  1. #1
    Registered User
    Join Date
    07-07-2015
    Location
    Wash DC
    MS-Off Ver
    10
    Posts
    57

    Import Macro to import separate files to separate worksheets - deletes existing worksheets

    I have a script that imports mulitple .csv files into seperate worksheets - works great... However, when it imports all the files, it deletes any existing pages - ie a summary sheet..... Is there any eay way to set it so it doesn't removed any existing worksheets after the import


    THis is the script
    Sub ImportParasoftGenericFiles()
    '
    ' ImportParasoftGenericFiles Macro
    
        Dim FilesToOpen
        Dim x As Integer
        Dim wkbAll As Workbook
        Dim wkbTemp As Workbook
        Dim sDelimiter As String
    
        On Error GoTo ErrHandler
        Application.ScreenUpdating = False
    
        sDelimiter = "|"
    
        FilesToOpen = Application.GetOpenFilename _
          (FileFilter:="csv (*.csv), *.csv", _
          MultiSelect:=True, Title:="csv Files to Open")
    
        If TypeName(FilesToOpen) = "Boolean" Then
            MsgBox "No Files were selected"
            GoTo ExitHandler
        End If
    
        x = 1
        Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
        wkbTemp.Sheets(1).Copy
        Set wkbAll = ActiveWorkbook
        wkbTemp.Close (False)
        wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
          Destination:=Range("A1"), DataType:=xlDelimited, _
          TextQualifier:=xlDoubleQuote, _
          ConsecutiveDelimiter:=False, _
          Tab:=False, Semicolon:=False, _
          Comma:=False, Space:=False, _
          Other:=True, OtherChar:="|"
        x = x + 1
    
        While x <= UBound(FilesToOpen)
            Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
            With wkbAll
                wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
                .Worksheets(x).Columns("A:A").TextToColumns _
                  Destination:=Range("A1"), DataType:=xlDelimited, _
                  TextQualifier:=xlDoubleQuote, _
                  ConsecutiveDelimiter:=False, _
                  Tab:=False, Semicolon:=False, _
                  Comma:=False, Space:=False, _
                  Other:=True, OtherChar:=sDelimiter
            End With
            x = x + 1
        Wend
    
    ExitHandler:
        Application.ScreenUpdating = True
        Set wkbAll = Nothing
        Set wkbTemp = Nothing
        Exit Sub
    
    ErrHandler:
        MsgBox Err.Description
        Resume ExitHandler
    End Sub
    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
    Last edited by 6StringJazzer; 09-01-2015 at 10:32 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. import multiple XER files into separate Excel Spreadsheets
    By amchale in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2014, 11:41 AM
  2. [SOLVED] Split worksheets to separate files
    By ksteffin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-10-2013, 06:04 AM
  3. [SOLVED] Import multiple csv files into current workbook as separate sheets
    By santosh in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-31-2013, 04:19 AM
  4. Separate several worksheets into excel files
    By mnp101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2013, 11:13 PM
  5. Pause Macro, Import From Separate Book
    By pichon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2011, 11:38 AM
  6. Replies: 50
    Last Post: 02-12-2010, 01:35 PM
  7. Macro to auto-email separate worksheets of same workbook to separate recipients?
    By Sean Anderson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-09-2007, 09:51 PM
  8. Import 2 text files into 2 separate columns?
    By tcurrier in forum Excel General
    Replies: 3
    Last Post: 02-11-2006, 03:13 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