| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Importing Sheets
see if this dose what you want:
Sub Import_Sheets() Dim fname As Variant Dim NewWkb As Workbook Dim ImportWkB As Workbook Set NewWkb = ThisWorkbook fname = Application.GetOpenFilename("XLS-Dateien,*.xls") If fname < False Then Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True) For Each sh In ImportWkB.Sheets sh.UsedRange.Copy NewWkb.Sheets(sh.Index).Range("B10") Next sh ImportWkB.Close False End If Set NewWkb = Nothing Set ImportWkB = Nothing End Sub -- jb "maywood" wrote: Hi, I got a question for VBA in Excel 2003: I have different .xls-files in a folder. I need to import some data from the most up-to-date .xls-file in this folder into a new .xls-file Example: In the folder "xy" there is the file "sep_09.xls". I need to copy all the data from the worksheets 1, 2 & 3 to my file "new.xls" (also to the worksheets 1, 2 & 3) By pushing a button in my file "new.xls" I want to get those worksheets from "sep_09.xls" imported into the different worksheets of "new.xls". At the moment I am using a code, which only can import one separate worksheet: Code:
Option Explicit
Sub Import_Sheet1()
Dim fname As Variant
fname = Application.GetOpenFilename("XLS-Dateien,*.xls")
If fname < False Then
With ActiveSheet
Workbooks.Open fname
Worksheets(1).UsedRange.Copy .Range("Sheet1!B10")
ActiveWorkbook.Close False
End With
End If
End Sub
For the second worksheet I use a second button with the same code (but: "worksheets(2)" and ("Sheet2!B10"). How can I manage it to import different worksheets at the same time with one "central button"? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
It says "variable sh not defined"!
"john" wrote: see if this dose what you want: Sub Import_Sheets() Dim fname As Variant Dim NewWkb As Workbook Dim ImportWkB As Workbook Set NewWkb = ThisWorkbook fname = Application.GetOpenFilename("XLS-Dateien,*.xls") If fname < False Then Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True) For Each sh In ImportWkB.Sheets sh.UsedRange.Copy NewWkb.Sheets(sh.Index).Range("B10") Next sh ImportWkB.Close False End If Set NewWkb = Nothing Set ImportWkB = Nothing End Sub -- jb "maywood" wrote: Hi, I got a question for VBA in Excel 2003: I have different .xls-files in a folder. I need to import some data from the most up-to-date .xls-file in this folder into a new .xls-file Example: In the folder "xy" there is the file "sep_09.xls". I need to copy all the data from the worksheets 1, 2 & 3 to my file "new.xls" (also to the worksheets 1, 2 & 3) By pushing a button in my file "new.xls" I want to get those worksheets from "sep_09.xls" imported into the different worksheets of "new.xls". At the moment I am using a code, which only can import one separate worksheet: Code:
Option Explicit
Sub Import_Sheet1()
Dim fname As Variant
fname = Application.GetOpenFilename("XLS-Dateien,*.xls")
If fname < False Then
With ActiveSheet
Workbooks.Open fname
Worksheets(1).UsedRange.Copy .Range("Sheet1!B10")
ActiveWorkbook.Close False
End With
End If
End Sub
For the second worksheet I use a second button with the same code (but: "worksheets(2)" and ("Sheet2!B10"). How can I manage it to import different worksheets at the same time with one "central button"? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
I omitted
Dim sh As Variant -- jb "maywood" wrote: It says "variable sh not defined"! "john" wrote: see if this dose what you want: Sub Import_Sheets() Dim fname As Variant Dim NewWkb As Workbook Dim ImportWkB As Workbook Set NewWkb = ThisWorkbook fname = Application.GetOpenFilename("XLS-Dateien,*.xls") If fname < False Then Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True) For Each sh In ImportWkB.Sheets sh.UsedRange.Copy NewWkb.Sheets(sh.Index).Range("B10") Next sh ImportWkB.Close False End If Set NewWkb = Nothing Set ImportWkB = Nothing End Sub -- jb "maywood" wrote: Hi, I got a question for VBA in Excel 2003: I have different .xls-files in a folder. I need to import some data from the most up-to-date .xls-file in this folder into a new .xls-file Example: In the folder "xy" there is the file "sep_09.xls". I need to copy all the data from the worksheets 1, 2 & 3 to my file "new.xls" (also to the worksheets 1, 2 & 3) By pushing a button in my file "new.xls" I want to get those worksheets from "sep_09.xls" imported into the different worksheets of "new.xls". At the moment I am using a code, which only can import one separate worksheet: Code:
Option Explicit
Sub Import_Sheet1()
Dim fname As Variant
fname = Application.GetOpenFilename("XLS-Dateien,*.xls")
If fname < False Then
With ActiveSheet
Workbooks.Open fname
Worksheets(1).UsedRange.Copy .Range("Sheet1!B10")
ActiveWorkbook.Close False
End With
End If
End Sub
For the second worksheet I use a second button with the same code (but: "worksheets(2)" and ("Sheet2!B10"). How can I manage it to import different worksheets at the same time with one "central button"? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
Wow, I think this works now! Thanks for your help!
But now I have 2 other questions: 1. Now I have some trouble with the references in the ImportWkB. How is it possible to copy only the content and not the formulas? 2. How is it possible to copy only the worksheets 1 to 6 and not 7, 8, etc.? "john" wrote: I omitted Dim sh As Variant -- jb "maywood" wrote: It says "variable sh not defined"! "john" wrote: see if this dose what you want: Sub Import_Sheets() Dim fname As Variant Dim NewWkb As Workbook Dim ImportWkB As Workbook Set NewWkb = ThisWorkbook fname = Application.GetOpenFilename("XLS-Dateien,*.xls") If fname < False Then Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True) For Each sh In ImportWkB.Sheets sh.UsedRange.Copy NewWkb.Sheets(sh.Index).Range("B10") Next sh ImportWkB.Close False End If Set NewWkb = Nothing Set ImportWkB = Nothing End Sub -- jb "maywood" wrote: Hi, I got a question for VBA in Excel 2003: I have different .xls-files in a folder. I need to import some data from the most up-to-date .xls-file in this folder into a new .xls-file Example: In the folder "xy" there is the file "sep_09.xls". I need to copy all the data from the worksheets 1, 2 & 3 to my file "new.xls" (also to the worksheets 1, 2 & 3) By pushing a button in my file "new.xls" I want to get those worksheets from "sep_09.xls" imported into the different worksheets of "new.xls". At the moment I am using a code, which only can import one separate worksheet: Code:
Option Explicit
Sub Import_Sheet1()
Dim fname As Variant
fname = Application.GetOpenFilename("XLS-Dateien,*.xls")
If fname < False Then
With ActiveSheet
Workbooks.Open fname
Worksheets(1).UsedRange.Copy .Range("Sheet1!B10")
ActiveWorkbook.Close False
End With
End If
End Sub
For the second worksheet I use a second button with the same code (but: "worksheets(2)" and ("Sheet2!B10"). How can I manage it to import different worksheets at the same time with one "central button"? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Importing from CSV over multiple sheets | Excel Programming | |||
| IMPORTING SHEETS | New Users to Excel | |||
| Importing data from several sheets, to one chart | Charts and Charting in Excel | |||
| importing sheets | Excel Programming | |||
| Excel VBA - Importing columns from different sheets | Excel Programming | |||
Hybrid Mode
