Results 1 to 5 of 5

Importing a single sheet from another Excel file with or without table formatting

Threaded View

  1. #1
    Registered User
    Join Date
    07-04-2014
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    24

    Importing a single sheet from another Excel file with or without table formatting

    The following code works fine if the sheet being imported is formatted as a table, and the table is renamed. What I am trying to do is import the sheet no matter if it has table formatting or not, rename the table if it does, make a table and rename it if it doesn't.
    Dim fileDialog As fileDialog
        Dim dialogTitle As String
        Dim wbSource As Workbook
        Dim strpathfile As String
                
        dialogTitle = "Navigate to and select In Shop file"
        Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
        
        With fileDialog
            .InitialFileName = "C:\Users\User\In Shop"
            .AllowMultiSelect = False
            .Filters.Clear
            .Title = dialogTitle
            strpathfile = .SelectedItems(1)
        End With
    
        Set wbSource = Workbooks.Open(Filename:=strpathfile)
        
        Application.WindowState = xlNormal
        Worksheets(1).Select
        Worksheets(1).Copy After:=Workbooks("Evac Status Report.xlsm").Sheets("Program Commands")
            
        wbSource.Close savechanges:=False
        
        Worksheets(2).Select
        Worksheets(2).Name = "W1PL62 In Shop Work"
        
        'Rename In Shop Table
        With ActiveSheet
            .ListObjects(1).Name = "InShopWork"
        End With
        
        Set fileDialog = Nothing
        Set wbSource = Nothing
    Last edited by mantis1117; 05-20-2018 at 02:00 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Importing Data from Multiple Excel Documents Into a Single File (take 2)
    By YvonH in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2013, 08:02 PM
  2. Importing Data from Multiple Excel Documents Into a Single File MAC Issues
    By Kal_2013 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-04-2013, 04:16 PM
  3. Replies: 2
    Last Post: 12-01-2012, 04:22 PM
  4. Replies: 10
    Last Post: 11-27-2012, 11:08 AM
  5. Importing Data from Multiple Excel Documents Into a Single File
    By DinahMarcinko in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-07-2012, 04:41 PM
  6. Importing Data from Multiple Excel Documents Into a Single File
    By DinahMarcinko in forum Excel General
    Replies: 2
    Last Post: 09-06-2012, 04:11 PM
  7. Replies: 3
    Last Post: 03-29-2012, 05:16 AM
  8. Replies: 1
    Last Post: 01-27-2010, 03:06 AM

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