Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button to import worksheets
On a worksheet I have the following data
A 1 Name 1 2 Name 2 3 Name 3 etc up to 15 Within the same parent folder I have 15 single page workbooks named the same as col a ie Name 1, Name 2 etc I need to import copies of the single sheet workbooks as worksheets in my main file in the order they appear in COL A ie sheet1 then Name1, Name2, Name3 etc So far i have managed to do this with 15 command buttons with the following code; Private Sub CommandButton2_Click() Sheets("Front Sheet").Select PathName = Range("JA26").Value Filename = Range("G30").Value If Filename = "" Then Exit Sub TabName = Range("I30").Value If I30 = ("Module 1") Then CommandButton2.Visible = True ControlFile = ActiveWorkbook.Name Workbooks.Open Filename:=PathName & Filename ActiveSheet.Name = TabName Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1) Windows(Filename).Activate ActiveWorkbook.Close SaveChanges:=False Windows(ControlFile).Activate Sheets("Front Sheet").Select End Sub I would like this to operate from 1 command button. could some one point me in the right direction please. Many thanks in advance. Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button to import worksheets
Maybe something like:
Option Explicit Private Sub CommandButton2_Click() Dim FSWks As Worksheet Dim TempWks As Worksheet Dim TempWkbk As Workbook Dim PathName As String Dim FileName As String Dim myRng As Range Dim myCell As Range Set FSWks = Worksheets("Front Sheet") With FSWks 'the stuff in column A Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) PathName = .Range("JA26").Value If Right(PathName, 1) < "\" Then PathName = PathName & "\" End If For Each myCell In myRng.Cells 'use column B as a report column myCell.Offset(0, 1).Value = "" 'try to open the file (in readonly mode) On Error Resume Next Set TempWkbk = Workbooks.Open _ (FileName:=PathName & myCell.Value, ReadOnly:=True) On Error GoTo 0 If TempWkbk Is Nothing Then 'couldn't be opened (bad name, wrong folder, password protected) myCell.Offset(0, 1).Value = "Couldn't be opened!" Else Set TempWks = TempWkbk.Sheets(1) TempWks.Copy _ after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt) TempWkbk.Close savechanges:=False End If Next myCell End With End Sub Mark Dullingham wrote: On a worksheet I have the following data A 1 Name 1 2 Name 2 3 Name 3 etc up to 15 Within the same parent folder I have 15 single page workbooks named the same as col a ie Name 1, Name 2 etc I need to import copies of the single sheet workbooks as worksheets in my main file in the order they appear in COL A ie sheet1 then Name1, Name2, Name3 etc So far i have managed to do this with 15 command buttons with the following code; Private Sub CommandButton2_Click() Sheets("Front Sheet").Select PathName = Range("JA26").Value Filename = Range("G30").Value If Filename = "" Then Exit Sub TabName = Range("I30").Value If I30 = ("Module 1") Then CommandButton2.Visible = True ControlFile = ActiveWorkbook.Name Workbooks.Open Filename:=PathName & Filename ActiveSheet.Name = TabName Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1) Windows(Filename).Activate ActiveWorkbook.Close SaveChanges:=False Windows(ControlFile).Activate Sheets("Front Sheet").Select End Sub I would like this to operate from 1 command button. could some one point me in the right direction please. Many thanks in advance. Mark -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button to import worksheets
Private Sub CommandButton2_Click() Folder = ThisWorkbook.Path Folder = Folder & "\" Set bk1 = ThisWorkbook Set sht = ActiveSheet TabName = sht.Range("I30").Value If I30 = ("Module 1") Then CommandButton2.Visible = True End If With bk1 RowCount = 1 Do While .Range("A" & RowCount) FName = .Range("A" & RowCount) Set bk2 = Workbooks.Open(Filename:=Folder & FName) bk2.Sheets(1).Copy _ After:=bk1.Sheets(bk1.Sheets.Count) bk2.Close SaveChanges:=False RowCount = RowCount + 1 Loop End With End Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=204270 http://www.thecodecage.com/forumz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging Worksheets via command button | Excel Discussion (Misc queries) | |||
import data from excel command button | Excel Programming | |||
Command Button to make multiple duplicate worksheets | Excel Worksheet Functions | |||
Command Button to email worksheets | Excel Programming | |||
Import a data using a command button | Excel Programming |