Import Sheet and Name it
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Import Sheet and Name it
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
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import Sheet and Name it
Try this macro:
It opens the Remote.xls workbook, copies the Daily Data sheet into the calling workbook, closes Remote.xls, then renames the copied worksheet.
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Import Sheet and Name it
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?
One thing though, could it be tweaked at all to not show the process, and make the newly named sheet very hidden?
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import Sheet and Name it
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Import Sheet and Name it
Perfect!! The flicker is barely noticeable. Thanks again Hans!!
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Import Sheet and Name it
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:
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.
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 need some ideas or examples of the best way to go about this, please.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import Sheet and Name it
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".
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Import Sheet and Name it
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...
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...
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import Sheet and Name it
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Import Sheet and Name it
I'm getting "Invalid use of Null" for this line:
strSiteID = Me.List_sites.Value
strSiteID = Me.List_sites.Value
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Import Sheet and Name it
It is a multi, will need to import several sites at a time.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import Sheet and Name it
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:
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
Best wishes,
Hans
Hans