?
Solved

Import worksheet error

Posted on 2011-10-03
4
Medium Priority
?
309 Views
Last Modified: 2012-05-12
I am using the following code to add a new worksheet to the workbook. When I expect the new sheet to be added to the end of the workbook and named "Import" , it is actually renaming the last sheet to "import"  and is importing sheet1 without renaming it. Please help!

Public Sub ImportWksheet()
'On Error GoTo ErrHandler

  Dim DstWkb        As Workbook
  Dim FileFilter    As String
  Dim Filename      As String
  Dim SrcWkb        As Workbook
  Dim srcWks        As String
  Dim dstWks        As String
  
    srcWks = "Sheet1"         'Name of the Worksheet to be copied
    dstWks = "Import"    'Name the copied Worksheet will be given
    
    FileFilter = "Excel Files, *.xls;*.xla;*.csv, All Files, *.*"
    Filename = Application.GetOpenFilename(FileFilter, 1)
    
    If Filename = "False" Then Exit Sub
    
      Set DstWkb = ThisWorkbook
      Set SrcWkb = Workbooks.Open(Filename:=Filename, ReadOnly:=True)
      
      Application.ScreenUpdating = False
        'On Error Resume Next
          With SrcWkb.Worksheets(srcWks)
            If Err.Number = 9 Then
              MsgBox srcWks & " was not found in " & SrcWkb.Name
              GoTo OrderlyExit
            End If
            'On Error GoTo 0
            .Copy After:=DstWkb.Worksheets(DstWkb.Worksheets.Count)
            'On Error Resume Next
              ActiveSheet.Name = dstWks
              If Err.Number = 1004 Then
                MsgBox dstWks & " already exists in " & DstWkb.Name & vbCrLf _
                     & "Two worksheets can not have the same name."
                GoTo OrderlyExit
              End If
            'On Error GoTo 0
          End With
       
OrderlyExit:
      Application.ScreenUpdating = True
      SrcWkb.Close SaveChanges:=False
      ysnValid = True
ErrHandler:
    
       ' MsgBox "Failed to Import to database.  Check database is not read-only."
        'Exit Sub
     
End Sub

Open in new window

0
Comment
Question by:smm6809
  • 3
4 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 36903184
Instead of using ActiveSheet use an explicit reference to the workbook you want to rename.

DstWkb.Worksheets(DstWkb.Worksheets.Count).Name = "Import"
0
 
LVL 35

Expert Comment

by:Norie
ID: 36903187
Oops, meant worksheet you want to rename.
0
 

Author Comment

by:smm6809
ID: 36903206
Error message:
Copy method of worksheet class failed.+
0
 
LVL 35

Expert Comment

by:Norie
ID: 36903229
The code posted was to replace the ActiveSheet.Name, nothing to do with the Copy.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question