Import Sheet and Name it

[Joseph]

I need to do the following procedure, and I'm failing miserably (again)!!

In my active workbook ("Master")
Need to import sheet name ("Daily Data")
From Filename ("Remote.xls")
From Target:"C:\Reports\Remote.xls"

Once the sheet is imported
Change tab name to the value of cell in Daily Data (2, 54)

If possible, then the rest I can handle.

I'm not sure if I'd have to import the whole workbook or not. If so, then I'd need to know how to delete the tabs I don't need smoothly...

Sorry so cryptic, on my phone. Thanks

[HansV]

Try this macro:

Code: Select all

Sub ImportSheet()
  Dim wbkSrc As Workbook
  Dim wbkTrg As Workbook
  Set wbkTrg = ActiveWorkbook
  Set wbkSrc = Workbooks.Open("C:\Reports\Remote.xls")
  wbkSrc.Worksheets("Daily Data").Copy After:=wbkTrg.Worksheets(wbkTrg.Worksheets.Count)
  wbkSrc.Close SaveChanges:=False
  wbkTrg.Worksheets("Daily Data").Name = wbkTrg.Worksheets("Daily Data").Cells(2, 54)
End Sub

It opens the Remote.xls workbook, copies the Daily Data sheet into the calling workbook, closes Remote.xls, then renames the copied worksheet.

[Joseph]

Just tried this works perfect!

One thing though, could it be tweaked at all to not show the process, and make the newly named sheet very hidden?

[HansV]

Code: Select all

Sub ImportSheet()
  Dim wbkSrc As Workbook
  Dim wbkTrg As Workbook
  Application.ScreenUpdating = False
  Set wbkTrg = ActiveWorkbook
  Set wbkSrc = Workbooks.Open("C:\Reports\Remote.xls")
  wbkSrc.Worksheets("Daily Data").Copy After:=wbkTrg.Worksheets(wbkTrg.Worksheets.Count)
  wbkSrc.Close SaveChanges:=False
  With wbkTrg.Worksheets("Daily Data")
    .Name = .Cells(2, 54)
    .Visible = xlSheetVeryHidden
  End With
  Application.ScreenUpdating = True
End Sub

Setting ScreenUpdating to False doesn't completely prevent the window from flickering when the other workbook is opened and closed.

[Joseph]

Perfect!! The flicker is barely noticeable. Thanks again Hans!!

[Joseph]

I'm trying now to run this code in an argument, and I can't seem to get it working. The code works, but my argument fails.

I'm going to need to import many sheets, depending on what is selected from a list box.

For instance:

Code: Select all

Private Sub CommandButton2_Click()
Dim ws As Worksheet
Set ws = Worksheets("main")
Dim irow As Long

'Search Range for UN_CHF
[b]if Me.List_sites.value = "UN_CHF" [/b]
THEN

'Import daily data worksheet
Dim wbkSrc As Workbook
  Dim wbkTrg As Workbook
  Application.ScreenUpdating = False
  Set wbkTrg = ActiveWorkbook
  Set wbkSrc = Workbooks.Open("I:\MapTool\UN_CHF\UN_CHF SPP.xls")
  Application.ScreenUpdating = False
  wbkSrc.Worksheets("Daily Data").Visible = xlSheetVisible
  wbkSrc.Worksheets("Daily Data").Unprotect Password:="*****"
  wbkSrc.Worksheets("Daily Data").Copy After:=wbkTrg.Worksheets(wbkTrg.Worksheets.Count)
  wbkSrc.Close SaveChanges:=False
  wbkTrg.Worksheets("Daily Data").Name = wbkTrg.Worksheets("Daily Data").Cells(2, 54)
  With wbkTrg.Worksheets("UN_CHF")
    .Name = .Cells(2, 54)
    .Visible = xlSheetVisible
    End With
 
  End If

I will have 30 different options on the listbox. Each option is a specific "Site Id", and each site has their own identical report on the drive. I need to have only selected sites report imported, so I planned to duplicate the above code 30 times just tweak the filepath etc..

I need some ideas or examples of the best way to go about this, please.

[HansV]

Can't the file path be derived from the site ID? In your example, the site ID is "UN_CHF", and the folder is named "UN_CHF" too, and the workbook "UN_CHF SPP.xls".

[Joseph]

I do suppose, all of the file path's are the same with the exception of the site id and folder.

How would I set the code to change the filepath depending on the selections? Set an array of some sort I'd imagine.

Selection: UN_CHF
("I:\MapTool\UN_CHF\UN_CHF SPP.xls")

Selection: UN_DAY
("I:\MapTool\UN_DAY\UN_DAY SPP.xls")

Selection: UN_IOC
("I:\MapTool\UN_IOC\UN_IOC SPP.xls")

etc...

[HansV]

Does this do what you want?

Code: Select all

Private Sub CommandButton2_Click()
  'Import daily data worksheet
  Dim wbkSrc As Workbook
  Dim wbkTrg As Workbook
  Dim strSiteID As String
  Application.ScreenUpdating = False
  Set wbkTrg = ActiveWorkbook
  strSiteID = Me.List_Sites.Value
  Set wbkSrc = Workbooks.Open("I:\MapTool\" & strSiteID & "\" & strSiteID & " SPP.xls")
  wbkSrc.Worksheets("Daily Data").Visible = xlSheetVisible
  wbkSrc.Worksheets("Daily Data").Unprotect Password:="*****"
  wbkSrc.Worksheets("Daily Data").Copy After:=wbkTrg.Worksheets(wbkTrg.Worksheets.Count)
  wbkSrc.Close SaveChanges:=False
  wbkTrg.Worksheets("Daily Data").Name = .Cells(2, 54)
  Application.ScreenUpdating = True
End Sub

[Joseph]

I'm getting "Invalid use of Null" for this line:

strSiteID = Me.List_sites.Value

[HansV]

Is this a single-select or multi-select list box?

[Joseph]

It is a multi, will need to import several sites at a time.

[HansV]

As I have explained in an earlier thread, a multi-select list box does not have a Value. You have to loop through the items to see which ones are selected.

A new try:

Code: Select all

Private Sub CommandButton2_Click()
  'Import daily data worksheet
  Dim wbkSrc As Workbook
  Dim wbkTrg As Workbook
  Dim i As Long
  Dim strSiteID As String
  Application.ScreenUpdating = False
  Set wbkTrg = ActiveWorkbook
  For i = 0 To Me.List_Sites.ListCount - 1
    If Me.List_Sites.Selected(i) Then
      strSiteID = Me.List_Sites.List(i)
      Set wbkSrc = Workbooks.Open("I:\MapTool\" & strSiteID & "\" & strSiteID & " SPP.xls")
      wbkSrc.Worksheets("Daily Data").Visible = xlSheetVisible
      wbkSrc.Worksheets("Daily Data").Unprotect Password:="*****"
      wbkSrc.Worksheets("Daily Data").Copy After:=wbkTrg.Worksheets(wbkTrg.Worksheets.Count)
      wbkSrc.Close SaveChanges:=False
      wbkTrg.Worksheets("Daily Data").Name = .Cells(2, 54)
    End If
  Next i
  Application.ScreenUpdating = True
End Sub