Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to open file and copy data

    I have a CSV file which I download each month into a folder called extract.

    I need a macro to do the following in the destination workbook

    1) Open up the CSV file
    2) Copy Col B from the CSV file to Col A & E after the last row in the destination workbook
    3) Copy Col C & D from the source workbook to Col F & F on the destination workbook

    I have attached a sample of the source SVC file



    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I would recommend starting with the macro recorder, get the code and modify it. I think this will help you learn better than us just creating a macro for you. Post back with questions on the code generated.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    You are 100% correct-One way of learning is trying yourself

    I will attemp myself post back with any questions where I need assistance

    Howard

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Atached please find my workbook (HOINS.xls) containing my attempted macro as well as the source csv workbook. It would be appreciated if you would neaten this up to conform to the original spec

    Howard
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Howard, you won't learn until you work through it.
    Does the code do what you want? If not, why not? Do you get errors or odd results? Does it need to do more.

    You may need to step through the code to see what is happening at each step to understand what it does and see if it is doing what you want it to do.

    You know the proverb: "Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime". I am trying to teach you how to fish...

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Will spend some more time on this and let you know where I need assistance

    Howard

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    See my code below to open workbook and then copy th data from the various columns to one row below last row containing data

    The macro runs with no errors, but I cannot get the data to copy from the source workbook to the destination workbook, Your assistance in this regard is most appreciated



    Sub Copy_Data()
    Dim files, nb As Workbook, lr As Long
    files = Application.GetOpenFilename
    If files = Null Or files = Empty Then Exit Sub
    With Application

    .CutCopyMode = False
    End With

    Set nb = Workbooks.Open(files)
    With nb.Sheets(1)
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("B2:b" & lr).Copy ThisWorkbook.Sheets("Sheet1").[e2 & LR]
    .Range("c2:d" & lr).Copy ThisWorkbook.Sheets("Fassets").[f2 & LR]
    End With

    nb.Close False
    Set nb = Nothing


    End Sub

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The following lines are not from the recorder (they are invailid):
    .Range("B2:b" & lr).Copy ThisWorkbook.Sheets("Sheet1").[e2 & LR]
    .Range("c2:d" & lr).Copy ThisWorkbook.Sheets("Fassets").[f2 & LR]

    What are they supposed to be doing? Are e2 and f2 supposed to be variables (as they are used) or refer to the cells in the active sheet (I presume that the active sheet is the new file you open, though the active sheet can vary with which sheet was active when the file was saved...

    Steve

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Will look at revising my code this evening

    Howard

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    I flew back from JFK yesterday. I love NYC! But it's sunny in Newcastle!

    So, looking at your code it seems your copy-to destination isn't quite right.
    Try this..

    Code:
    Sub Copy_Data()
    
    Dim files, nb As Workbook, lr As Long
    
    files = Application.GetOpenFilename
    If files = Null Or files = Empty Then Exit Sub
    
    Application.CutCopyMode = False
    
    Set nb = Workbooks.Open(files)
    With nb.Sheets(1)
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    
    Set zDest = ThisWorkbook.Sheets("Sheet1").[e65536].End(xlUp).Offset(1)
    .Range("B2:b" & lr).Copy zDest
    
    Set zDest = ThisWorkbook.Sheets("Fassets").[f65536].End(xlUp).Offset(1)
    .Range("c2:d" & lr).Copy zDest
    
    End With
    
    nb.Close False
    
    Set nb = Nothing
    Set zDest = Nothing
    
    End Sub
    You can use square brackets for a specific cell or range, but you can't do calcs in them.
    So [e57] is OK, but you can't use [e2 & xxx]
    However, you can use calcs like Range("c2:d" & xxx)

    zeddy

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    With so many people transitioning from XL97-2003 to XL2007+ with the different workbook sizes, I recommend avoiding anything that explicitly uses the old workbook size (eg e65536 and f65536) and instead go with calculating the max rows at runtime. Something more like:

    with ThisWorkbook.Sheets("Sheet1")
    Set zDest = .cells(.rows.count,5).End(xlUp).Offset(1)
    end with
    .Range("B2:b" & lr).Copy zDest

    with ThisWorkbook.Sheets("fassets")
    Set zDest = .cells(.rows.count,6).End(xlUp).Offset(1)
    end with
    .Range("c2:d" & lr).Copy zDest

    This will be good whether the files are older or newer and avoid any updates if they transition to the newer program and will even be good if XL2015 allows even larger size workbooks.

    Steve

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Steve

    You are correct of course. But I still think there is room for a little simplicity over technical precision.
    In Howards case, I'm sure he won't be dealing with a million invoices.
    If you use
    [e65536].End(xlUp).Offset(1)
    ..you can see clearly you are working with column [E]
    If you use
    cells(.rows.count,5).End(xlUp).Offset(1)
    ..you have to remember that you need the 5 for column [E], 6 for column [F] etc.

    I use the correct format when I know that there will likely be more than say, 50 thousand records.
    ..But then again, sometimes I'll just use [e100000].End(xlUp) just because it seems more intuitive to me, or, if I have to share it with someone else, then maybe [e1048576].End(xlUp) etc etc
    But I might just have to get used to using the correct version-independent more-typing method.

    zeddy

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I understand the simplicity, and only bring it up since others use the code as well and I try to be general with it.
    On a related note: I have also gotten into the habit of defining row variables as long, since I have gotten burned with defining as integer. I haven't broken myself of the habit of integer columns, even though they may not all be...

    Take care,
    Steve

  14. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve & Zeddy

    Thanks for the help, much appreciated

    Howard

  15. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by sdckapr View Post
    I understand the simplicity, and only bring it up since others use the code as well and I try to be general with it.
    Right ON!

    Quote Originally Posted by sdckapr View Post
    On a related note: I have also gotten into the habit of defining row variables as long, since I have gotten burned with defining as integer. I haven't broken myself of the habit of integer columns, even though they may not all be...
    "Simplicity is for the Simple!"

    [RANT ON]
    Seriously, I've long been an advocate of good coding standards which of course include declaring (DIM) all variables and typing them properly (based on the language spec). Some say this is too much information for new comers I say if you are going to teach someone how to do something teach them the right way 1st. If they want to take shortcuts later that's their decision, and they can pay the consequences, but at least they know the right way.
    [RANT OFF]

    Ah...I feel "MUCH BETTER NOW"!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

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