Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Data (2000)

    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 ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Data (2000)

    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

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (2000)

    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>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Data (2000)

    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)

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (2000)

    Set wshTarget = wbkTarget.Worksheets("Page1")

    comes up subscript out of range... ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Data (2000)

    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

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •