Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Import xlsx sheet from server to existing workbook, etc.

    Maybe this should be in the VBA group, but it's Excel, so I'm posting here.

    I have a 1 sheet xlsx file on a server: htttp://www.domain.com/data.xlsx for example.

    I want a "button" on my Workbook on my PC that will get this external file, place it in the PC workbook, rename the tab (e.g., to DATA), and then make it very hidden.

    Totally out of my depth on this one.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    KW,

    Here is some generic code that will allow you to pull a worksheet from a server file, place it in the current workbook as the last sheet then make it very hidden with a sheet name of "Data". Enter in cell E1 the complete path for the file.

    This assumes that the sheet you want to pull is the first sheet of the external file else an adjustment is simple. The names and number of the worksheets are irrelevant. If you want to re-run the code, remember to open the VB editor (Alt-F11) and make the Data sheet visible then delete it from within Excel or you will generate an error trying to create a duplicate sheet.

    HTH,
    Maud

    Code:
    Public Sub GetDataSheet()
    Application.ScreenUpdating = False
    '------------------------------
    'DECALRE AND SET VARIABLES
    Dim wb1 As Workbook, wb2 As Workbook
    FilePath = Range("E1")
    Set wb1 = Workbooks.Open(FilePath)
    Set wb2 = ThisWorkbook
    '------------------------------
    'GET FILE, COPY SHEET, MAKE VERY HIDDEN
    wb1.Worksheets(1).Copy After:=wb2.Worksheets(wb2.Worksheets.Count)
    wb2.Worksheets(Worksheets.Count).Name = "Data"
    wb2.Worksheets("Data").Visible = xlVeryHidden
    '------------------------------
    'CLEANUP
    wb1.Close
    Set wb1 = Nothing
    Set wb2 = Nothing
    Application.ScreenUpdating = True
    End Sub
    KW4.png
    Attached Files Attached Files
    Last edited by Maudibe; 2015-11-15 at 19:33.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    That's GREAT! One minor issue that I suppose you could offer a fix inside this macro.
    I'd like to be able to run this, say, weekly, with a new external file (same path). Can the macro make the DATA file visible then delete it and then rerun and do what it's doing now?

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maud: ever since I put this macro in, Excel keeps crashing (even before I run the macro) and then restarts.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    KW,

    Strange behavior. If you ran the code once and it created a very hidden sheet called Data as it is supposed to do, could the presence of this hidden sheet be interfering with other code you might have? Did you by chance copy the button from my workbook and paste in yours? If so, delete the form button, add your own, assign the GetDataSheet macro in your workbook to it. Have you changed the path in cell E1 to a valid path? Do you have any issues with running the code in the workbook that I supplied? What version of Excel are you running?

    If you can get past this issue, the answer to your question is yes, an added line of code can delete the data sheet before uploading a new one.

    Maud

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I did create my own button that is liked to the macro. I also changed the E1 ref in the code as well as where the path is on the sheet.
    When I removed the macro, I haven't had Excel stop working. Odd is right.

    I didn't try your workbook, but will Monday.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maud: Continues to be very strange. Your Destination Excel file worked as you indicated (the code worked in my file as well -- once). However, when I ran yours once, then went to my original, Excel stopped working again and I had to close everything.
    I'm running 2010 on a Win 10 PC. Very odd!

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    I tested the spreadsheet on another computer that I have 2010 on and I had no issues; it is pretty basic coding. I'd like to open this to to anyone else who might have some ideas of what is causing the problem.

    Any chance you could post a knockdown version of your workbook?

    Maud

  9. #9
    jwoods
    Guest
    Quote Originally Posted by kweaver View Post
    Maud: Continues to be very strange. Your Destination Excel file worked as you indicated (the code worked in my file as well -- once). However, when I ran yours once, then went to my original, Excel stopped working again and I had to close everything.
    I'm running 2010 on a Win 10 PC. Very odd!
    Check the Windows Event logs in Event Viewer for errors related to Excel.

    In Control Panel, enter event in the top-right search box and click View event logs in the result.

    Expand the Custom Views folder and click on Administrative Events.

    The Administrative Events contain Warnings, Errors, and Critical events from all event logs.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    jwoods: thanks for that suggestion, but I have NO IDEA what I'm looking at or for here.

  11. #11
    jwoods
    Guest
    Quote Originally Posted by kweaver View Post
    jwoods: thanks for that suggestion, but I have NO IDEA what I'm looking at or for here.
    Errors related to Excel with a date and time logged that is close to or matches when the issue occurred.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    If this were possible, it might be a work-around my issue:

    Can I copy one sheet from an Excel file and insert that as a new sheet into SEVERAL other excel files? All the sheets can have the same sheet name. It would be nice if, as the sheets are inserted, they could then be very hidden in each.

    I can create a list of the files into which this single file needs to be inserted.

    (is there a macro option that might do this?)
    Last edited by kweaver; 2015-11-16 at 20:26.

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    KW,

    Yes, that is doable. Can you be more specific as to the names of the source and destination files?

    Maud

    P.S. Jwoods shows a good starting point to understanding the nature of the Excel crashes. Still would like to see a sample of your worksheet if possible
    Last edited by Maudibe; 2015-11-16 at 21:20.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I have to figure out how to easily carve up the worksheet because there's a HIPAA issue/restrictions with confidentiality of data.
    Here's my best (hopefully) description.

    The end user is generating commission statements for 40+ sales reps based which are based on formulas that come from this ONE Excel file of data.

    If I could capture that sheet and place it in 40+ workbooks (very hidden), the program would work perfectly (he says, optimistically).

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    KW,

    What is the name of the sheet in the main workbook? Is it possible for you to create a hidden sheet in the main workbook that has the full paths of the destination workbooks in column A? Or perhaps, can you place all and only the destination workbooks in a designated folder? If so, this can be done

    Maud

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
  •