Importing Data (2000)

[Steve_in_Kent]

I need to place some code in a button to import some .xls data into another .xls workbook. I searched the forum and found this code, posted by LegarColeman...

Sub ImportCSV()
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:Wo rkTest.csv", _ Destination:=Range("A1"))
.Name = "Test"
.FieldNames = True

etc..etc....

I changed the file extension etc, but it doesn't seem to work for regular files.

The files that i need to import and count are a mixture of text and numbers (each column is the same data type), with an unknown amount of rows.
The filename of the file to import needs to come from a cell in the workbook.

Anyone have any other code to do this ?

[HansV]

I would open the source workbook in Excel like any other workbook, copy the relevant data and paste it into the target workbook. Something like this (adapt it to your needs):

Dim wbkSource As Workbook
Dim wbkTarget As Workbook
Dim wshSource As Worksheet
Dim wshTarget As Worksheet
Dim rngSource As Range

Set wbkTarget = ActiveWorkbook
Set wshTarget = ActiveWorksheet
Set rngTarget = wshTarget.Range("D37")

Set wbkSource = Workbooks.Open("C:WorkTest.xls")
Set wshSource = wbkSource.Worksheets("Sheet1")
Set rngSource = wshSource.Range("A1").CurrentRange

rngSource.Copy rngTarget

wbkSource.Close SaveChanges:=False

[Steve_in_Kent]

Hey Hans, i never knew you were an Excel addict as well as an Access Guru ! <img src=/S/clever.gif border=0 alt=clever width=15 height=15>



hmm gives me an error 'object required' and error line : Set wshTarget = ActiveWorksheet

I changed the target worksheet but it gives a 'subscript out of range error'



<font color=blue>Private Sub CommandButton1_Click()

Dim wbkSource As Workbook, wbkTarget As Workbook, wshSource As Worksheet, wshTarget As Worksheet
Dim rngSource As Range

Set wbkTarget = ActiveWorkbook
<font color=red>Set wshTarget = wbkTarget.Worksheets("Page1")</font color=red>
Set rngTarget = wshTarget.Range("A21")

Set wbkSource = Workbooks.Open("R:FACTORYTQMSNew P-ViewL2-150803-1.xls")
Set wshSource = wbkSource.Worksheets("Sheet1")
Set rngSource = wshSource.Range("A1").CurrentRange

rngSource.Copy rngTarget

wbkSource.Close SaveChanges:=False

End Sub</font color=blue>

[HansV]

I'm not much of an Excel guru - I was too hasty and posted air code. Sorry about that. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
You will need the following modifications:
<UL><LI>I forgot a declaration; add
Dim rngTarget As Range
<LI>ActiveWorksheet should have been ActiveSheet
<LI>CurrentRange should have been CurrentRegion[/list]With these modifications it worked on my PC (with existing file names of course)

[Steve_in_Kent]

Set wshTarget = wbkTarget.Worksheets("Page1")

comes up subscript out of range... ?

[HansV]

Umm, I hate to ask this: are you sure that the active workbook (wbkTarget) has a worksheet named Page1? The error message you mention occurs if you try to refer to a non-existing worksheet.

What happens if you activate the target worksheet manually before starting the import and using

Set wshTarget = ActiveSheet

or

Set wshTarget = wbkTarget.ActiveSheet

[Steve_in_Kent]

Oops..! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> yea, Hans, it was my stupid typo. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Thanks Again