Page 3 of 3 FirstFirst 123
Results 21 to 29 of 29

Thread: Can't figure out this script Excel thing

  1. #21
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,176
    Bank
    0
    Total Points
    680,176
    Donate
    So I have this code here which is pretty basic...

    Code:
    Sub CopyToJoe()
    '
    ' CopyToJoe Macro
    '
    
    '
        Range("A6:B6").Select
        Selection.Copy
        Sheets("Sheet2").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Sheets("Sheet1").Select
    End Sub
    So I'm trying to figure out what the syntax would be if I wanted to define a place in another workbook rather than just another sheet as it says by default (in red above).

    I was thinking this all might work if it was something more like this...

    Code:
    Sub CopyToJoe()
    '
    ' CopyToJoe Macro
    '
    
    '
        Range("A6:B6").Select
        Selection.Copy
        Sheets("Sheet2").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Sheets("=[main_file.xlsx]Sheet1!$B$6").Select
    End Sub
    I just can't figure out what the code is for that red part above.

  2. #22
    Senior Member
    Join Date
    Jan 2014
    Posts
    725
    vCash
    0
    Points
    131,294
    Bank
    0
    Total Points
    131,294
    Donate
    look at this. this is kinda where you need to go
    http://www.techrepublic.com/forums/q...ve-then-close/

    in this example instead of just copying / pasting rows, you'll actually need to read those values as variables into your routine and then you can write them to the excel file.

    it'll get more complicated of course because you'll also need to check to see if there's data already there and where to insert this new data etc.....
    Last edited by 74AD; 06-12-2014 at 04:09 PM.

  3. #23
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,176
    Bank
    0
    Total Points
    680,176
    Donate
    Interesting. Thanks man! I've been working on this all week and picking up a little bit everyday. By the end of this thread I will be an expert VBS developer. LOL.

  4. #24
    Senior Member
    Join Date
    Jan 2014
    Posts
    725
    vCash
    0
    Points
    131,294
    Bank
    0
    Total Points
    131,294
    Donate
    Quote Originally Posted by Webhead View Post
    Interesting. Thanks man! I've been working on this all week and picking up a little bit everyday. By the end of this thread I will be an expert VBS developer. LOL.
    no crap, eh!
    your best bet in my not super programmer mind would be to read the existing excel sheet first, and store it then go ahead write what was previously in there back over top of itself, add your new piece and save it.

    here's how to read an excel file in vbs
    http://www.robvanderwoude.com/vbstec...ases_excel.php

  5. #25
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,176
    Bank
    0
    Total Points
    680,176
    Donate
    Quote Originally Posted by 74AD View Post
    no crap, eh!
    your best bet in my not super programmer mind would be to read the existing excel sheet first, and store it then go ahead write what was previously in there back over top of itself, add your new piece and save it.

    here's how to read an excel file in vbs
    http://www.robvanderwoude.com/vbstec...ases_excel.php
    Well for what it's worth, from my point of view you are a master excel programmer. Thanks again for the help. I guess I got some reading to do now.

  6. #26
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,176
    Bank
    0
    Total Points
    680,176
    Donate
    So I found this guy on Youtube: https://www.youtube.com/user/familycomputerclub/videos

    This guy has a video for just about everything. Awesome! between a few of his videos I can piece together the thing I'm trying to do.

  7. #27
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,176
    Bank
    0
    Total Points
    680,176
    Donate
    Update: So I guess this case is officially closed. I created the thing I need using my ancient Sharepoint Services 3.0 server. It works great. Haven't nailed down the permissions yet but otherwise it works just as we need. Going to also setup Sharepoint Foundation 2013 and see if I can import and maybe the newer version will have better stuff in it. But this is waaaaaaaaaay better than trying to use this clunky Excel way of doing it.

  8. #28
    Senior Member
    Join Date
    Jan 2014
    Posts
    725
    vCash
    0
    Points
    131,294
    Bank
    0
    Total Points
    131,294
    Donate
    Quote Originally Posted by Webhead View Post
    Update: So I guess this case is officially closed. I created the thing I need using my ancient Sharepoint Services 3.0 server. It works great. Haven't nailed down the permissions yet but otherwise it works just as we need. Going to also setup Sharepoint Foundation 2013 and see if I can import and maybe the newer version will have better stuff in it. But this is waaaaaaaaaay better than trying to use this clunky Excel way of doing it.
    that's good. I'm glad you didn't keep plugging ahead on the excel thing. A database solution with a front end (which is exactly what sharepoint is) is a more more appropriate and elegant solution. I think you made the right call. As far as upgrading, who cares if it's on old 2003 Sharepoint services. If it works as intended, then who cares why go through the hassle just to have the "latest"

  9. #29
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,176
    Bank
    0
    Total Points
    680,176
    Donate
    Quote Originally Posted by 74AD View Post
    that's good. I'm glad you didn't keep plugging ahead on the excel thing. A database solution with a front end (which is exactly what sharepoint is) is a more more appropriate and elegant solution. I think you made the right call. As far as upgrading, who cares if it's on old 2003 Sharepoint services. If it works as intended, then who cares why go through the hassle just to have the "latest"
    Thanks man. Yeah, this is sooo much better. I've been stressin out all week about this and then once I got it working on Sharepoint it was like instant relief.

    As for upgrading, I have a student account with Dreamspark. So I got access to all this free MS software. As a result, I ended up setting up a Windows Server 2008 R2 server and a Windows Server 2012 server. On the 2012 one (Xeon with 8GB - pretty good harware I guess), I'm going to install SQL Server 2008 R2 and Sharepoint Foundation 2013. I agree that I don't really care about it being the latest and greatest either but my hope is that the newer software is setup a little better. The old Sharepoint is nice and has served me well for various things but isn't very intuitive. I drive myself crazy every time I have to configure something on it. So gonna setup this newer one and check it out. Hoping it will have some better features and maybe be a little more intuitive. My hopes aren't high though. Either way, it's fun to play with. It's like new toys.

Posting Permissions

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