Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA - Importing Sheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 38
Default VBA - Importing Sheets
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 With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 523
Default VBA - Importing Sheets
There are more elegant ways, but this is a quick & dirty method:

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")
Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")
Worksheets(3).UsedRange.Copy .Range("Sheet3!B10")


ActiveWorkbook.Close False
End With
End If
End Sub


"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 With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 38
Default VBA - Importing Sheets
This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

"Sam Wilson" wrote:

There are more elegant ways, but this is a quick & dirty method:

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")
Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")
Worksheets(3).UsedRange.Copy .Range("Sheet3!B10")


ActiveWorkbook.Close False
End With
End If
End Sub


"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 With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 38
Default VBA - Importing Sheets
BTW: The error has the commet:
Run-time error '1004': Application-defined or object-defined error

"maywood" wrote:

This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

"Sam Wilson" wrote:

There are more elegant ways, but this is a quick & dirty method:

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")
Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")
Worksheets(3).UsedRange.Copy .Range("Sheet3!B10")


ActiveWorkbook.Close False
End With
End If
End Sub


"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 With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 523
Default VBA - Importing Sheets
Is there a sheet called "Sheet2" in your target workbook?


"maywood" wrote:

This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

"Sam Wilson" wrote:

There are more elegant ways, but this is a quick & dirty method:

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")
Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")
Worksheets(3).UsedRange.Copy .Range("Sheet3!B10")


ActiveWorkbook.Close False
End With
End If
End Sub


"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 With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 38
Default VBA - Importing Sheets
Yes.

"Sam Wilson" wrote:

Is there a sheet called "Sheet2" in your target workbook?


"maywood" wrote:

This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

"Sam Wilson" wrote:

There are more elegant ways, but this is a quick & dirty method:

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")
Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")
Worksheets(3).UsedRange.Copy .Range("Sheet3!B10")


ActiveWorkbook.Close False
End With
End If
End Sub


"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 With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 523
Default VBA - Importing Sheets

What is the used range in Worksheets(2)? If it's every row or column and you
try to paste into any cell other than A1 it won't work.

"maywood" wrote:

Yes.

"Sam Wilson" wrote:

Is there a sheet called "Sheet2" in your target workbook?


"maywood" wrote:

This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

"Sam Wilson" wrote:

There are more elegant ways, but this is a quick & dirty method:

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")
Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")
Worksheets(3).UsedRange.Copy .Range("Sheet3!B10")


ActiveWorkbook.Close False
End With
End If
End Sub


"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 With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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"?
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 38
Default 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"?
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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"?
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 38
Default 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 With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing from CSV over multiple sheets Verity Excel Programming 5 November 16th 05 03:44 AM
IMPORTING SHEETS Rebecca New Users to Excel 3 June 29th 05 01:04 PM
Importing data from several sheets, to one chart Martin Thorgaard Charts and Charting in Excel 2 January 6th 05 03:07 PM
importing sheets el_peacock Excel Programming 0 November 21st 04 04:09 PM
Excel VBA - Importing columns from different sheets Gaston[_2_] Excel Programming 0 January 29th 04 12:41 AM


All times are GMT +1. The time now is 01:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement