Import data into Excel sheet from CSV File
 

I am using the following code to import data from a csv file into a
sheet in my workbook. I keep getting the 1004 error for the line
wkbk.Worksheets("Employee List").Cells.Select:

Private Sub Download_Employee_Data_Click()
Dim sh As Worksheet
Dim rng As Range
Dim wb As Workbook, wkbk As Workbook

Set wkbk = ActiveWorkbook
Set wb = Workbooks.Open(FileName:= "Hourly employees.csv")
wb.Worksheets(1).Cells.Select
Selection.Copy
wkbk.Worksheets("Employee List").Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Since the source data and destination sheet have the same number of
columns, I am using the cells.select functionality. Once the new data
is copied onto the Employees List sheet, I would also like to update a
user defined name (Employee_List) with the new data. I can use the
following command to detect the range, but I'm not sure what syntax to
use to change the defined name:

Set sh = Worksheets("Employee List")
set rng = sh.Range(sh.Cells(1, "A"), _
sh.Cells(sh.Rows.Count, "D").End(xlUp))

Source Data:

Employee Number Last Name First Name HR Organization Name
11111 Last1 First1 BR - ALBUQUERQUE
22222 Last2 First2 BR - ALBUQUERQUE
33333 Last3 First3 BR - ALBUQUERQUE
44444 Last4 First4 BR - ALBUQUERQUE

Any help would be appreciated. Thanks!
Connie

Import data into Excel sheet from CSV File
 

You can't select a sheet unless the parent book is active. You can't select
a cell unless the parent sheet is active.

You can replace

wkbk.Worksheets("Employee List").Cells.Select

with
application.Goto wkbk.Worksheets("Employee List").Cells


or

wkbk.Select
wkbk.Worksheets("Employee List").Select
wkbk.Worksheets("Employee List").Cells.Select

--
Regards,
Tom Ogilvy


"Connie" wrote in message
oups.com...
I am using the following code to import data from a csv file into a
sheet in my workbook. I keep getting the 1004 error for the line
wkbk.Worksheets("Employee List").Cells.Select:

Private Sub Download_Employee_Data_Click()
Dim sh As Worksheet
Dim rng As Range
Dim wb As Workbook, wkbk As Workbook

Set wkbk = ActiveWorkbook
Set wb = Workbooks.Open(FileName:= "Hourly employees.csv")
wb.Worksheets(1).Cells.Select
Selection.Copy
wkbk.Worksheets("Employee List").Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Since the source data and destination sheet have the same number of
columns, I am using the cells.select functionality. Once the new data
is copied onto the Employees List sheet, I would also like to update a
user defined name (Employee_List) with the new data. I can use the
following command to detect the range, but I'm not sure what syntax to
use to change the defined name:

Set sh = Worksheets("Employee List")
set rng = sh.Range(sh.Cells(1, "A"), _
sh.Cells(sh.Rows.Count, "D").End(xlUp))

Source Data:

Employee Number Last Name First Name HR Organization Name
11111 Last1 First1 BR - ALBUQUERQUE
22222 Last2 First2 BR - ALBUQUERQUE
33333 Last3 First3 BR - ALBUQUERQUE
44444 Last4 First4 BR - ALBUQUERQUE

Any help would be appreciated. Thanks!
Connie

Import data into Excel sheet from CSV File
 

Connie,
You can only .Select from the active sheet.
But do need the selects at all ?
wb.Worksheets(1).Cells.Copy
wkbk.Worksheets("Employee List").Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

As for the named range update:
Names("Employee_List").RefersTo=

NickHK

"Connie" wrote in message
oups.com...
I am using the following code to import data from a csv file into a
sheet in my workbook. I keep getting the 1004 error for the line
wkbk.Worksheets("Employee List").Cells.Select:

Private Sub Download_Employee_Data_Click()
Dim sh As Worksheet
Dim rng As Range
Dim wb As Workbook, wkbk As Workbook

Set wkbk = ActiveWorkbook
Set wb = Workbooks.Open(FileName:= "Hourly employees.csv")
wb.Worksheets(1).Cells.Select
Selection.Copy
wkbk.Worksheets("Employee List").Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Since the source data and destination sheet have the same number of
columns, I am using the cells.select functionality. Once the new data
is copied onto the Employees List sheet, I would also like to update a
user defined name (Employee_List) with the new data. I can use the
following command to detect the range, but I'm not sure what syntax to
use to change the defined name:

Set sh = Worksheets("Employee List")
set rng = sh.Range(sh.Cells(1, "A"), _
sh.Cells(sh.Rows.Count, "D").End(xlUp))

Source Data:

Employee Number Last Name First Name HR Organization Name
11111 Last1 First1 BR - ALBUQUERQUE
22222 Last2 First2 BR - ALBUQUERQUE
33333 Last3 First3 BR - ALBUQUERQUE
44444 Last4 First4 BR - ALBUQUERQUE

Any help would be appreciated. Thanks!
Connie

Import data into Excel sheet from CSV File
 

NickHK:
I never responded to you, but your suggestion below was a big help!
Thanks for your time; it's much appreciated.

Connie

NickHK wrote:
Connie,
You can only .Select from the active sheet.
But do need the selects at all ?
wb.Worksheets(1).Cells.Copy
wkbk.Worksheets("Employee List").Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

As for the named range update:
Names("Employee_List").RefersTo=

NickHK

"Connie" wrote in message
oups.com...
I am using the following code to import data from a csv file into a
sheet in my workbook. I keep getting the 1004 error for the line
wkbk.Worksheets("Employee List").Cells.Select:

Private Sub Download_Employee_Data_Click()
Dim sh As Worksheet
Dim rng As Range
Dim wb As Workbook, wkbk As Workbook

Set wkbk = ActiveWorkbook
Set wb = Workbooks.Open(FileName:= "Hourly employees.csv")
wb.Worksheets(1).Cells.Select
Selection.Copy
wkbk.Worksheets("Employee List").Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Since the source data and destination sheet have the same number of
columns, I am using the cells.select functionality. Once the new data
is copied onto the Employees List sheet, I would also like to update a
user defined name (Employee_List) with the new data. I can use the
following command to detect the range, but I'm not sure what syntax to
use to change the defined name:

Set sh = Worksheets("Employee List")
set rng = sh.Range(sh.Cells(1, "A"), _
sh.Cells(sh.Rows.Count, "D").End(xlUp))

Source Data:

Employee Number Last Name First Name HR Organization Name
11111 Last1 First1 BR - ALBUQUERQUE
22222 Last2 First2 BR - ALBUQUERQUE
33333 Last3 First3 BR - ALBUQUERQUE
44444 Last4 First4 BR - ALBUQUERQUE

Any help would be appreciated. Thanks!
Connie