Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Copying from a Workbook without opening it ?

    At the moment, using VBA, I am copying cells from source to target Workbooks by opening the source, selecting and copying the relevant cells then pasting the values (I don't want to copy any formatting) into the target.

    This works fine, but I'd like to do it without opening the source. Is this possible ?

    Thanks,

    Martin

    PS I cannot link the cells as the source is not always present and I need to run checks before actually transferring the values.

  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 think you will need to have VB create some direct references and then copy and paste the values. the direct references need to be of the form:
    ='C:\File Path\[File name.xlsx]Sheet name'!B25

    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Martin
    Quote Originally Posted by MartinM View Post
    I'd like to do it without opening the source. Is this possible ?
    Yes, it is possible to retrieve data from an Excel workbook without opening it.
    You can use a function which calls an ExcecuteExcel4Macro, which is still supported in current Excel versions.

    Do you want to retrieve single values or a block of values (e.g. 50 rows x 10 columns)?

    zeddy

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Hi Zeddy (not ignoring Steve !),

    That's exactly what I was going to ask Steve - can I use his syntax to retrieve a block of values ?

    I can't give the block a range name as it is a downloaded .csv file, but I do know the range which contains the values I need.

    Martin

  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
    The direct link I believe must be one cell at a time (though you can enter one link and copy it into a range to get a range of cells relative to that one)

    But if it is a CSV file, then it is NOT an excel spreadsheet and does not have a structure that excel can link to. In this case I think you would have to open it (converting it) into an excel sheet in which you can copy and paste-values from it, or bypass Excel and "OPEN" the film (though not opened in excel) and read it line by line, but that seems a lot more work to write the code then just opening it in excel and let it do the work...

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thank you Steve - it is helpful to know when I'm in a dead end.

    Martin

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Would something like this help? It will open the source workbook, transfer a block of data with no formatting using a loop, then close the source.
    Code:
    Public Sub test()
    Application.ScreenUpdating = False
    Workbooks.Open Filename:="C:\Users\Maudibe\Desktop\source.xlsm"
    For I = 1 To 50
        Workbooks("Target.xlsm").ActiveSheet.Cells(I, 1).Value = Workbooks("Source.xlsm").Sheets("Sheet2").Cells(I, 1).Value
    Next I
    Windows("Source.xlsm").Close
    Application.ScreenUpdating = True
    End Sub
    OR using a named range with no looping

    Code:
    Public Sub test()
    Application.ScreenUpdating = False
    Workbooks.Open Filename:="C:\Users\Maudibe\Desktop\source.xlsm"
    Workbooks("Target.xlsm").ActiveSheet.Cells(1, 1).Value = Workbooks("Source.xlsm").Sheets("Sheet2").Range("sampleblock").Value
    Windows("Source.xlsm").Close
    Application.ScreenUpdating = True
    End Sub
    OR transferring data from a CSV file (Note: The sheet name and workbook name are the same)

    Code:
    Public Sub test()
    Application.ScreenUpdating = False
    Workbooks.Open Filename:="C:\Users\Maudibe\Desktop\source1.csv"
    For I = 1 To 50
        Workbooks("Target.xlsm").ActiveSheet.Cells(I, 1).Value = Workbooks("Source1.csv").Sheets("Source1").Cells(I, 1).Value
    Next I
    Windows("Source1.csv").Close
    Application.ScreenUpdating = True
    End Sub

    HTH,
    Maud

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks Maud, I was already successfully working a procedure similar to your last example.

    I was trying to avoid opening the .csv file as that act causes strange upsets in an already open Workbook (something that Zeddy alerted me to some months ago), necessitating a recalculation.

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Martin

    it is possible to copy a range of data from a closed CSV file into Excel.
    I have attached an Excel file and a CSV test file to demonstrate this.

    Copy these two attached files to a folder of your choice.
    I am unable to upload a .csv file, so I simple renamed the file with .txt
    So when you copy this to your folder, rename it back to .csv before proceeding.
    You now have my sample csv file.

    Now open the Excel file by itself.
    Click the button to import data from the closed CSV file.
    Voila!

    Now, this demo will import data from [A1:Q50] from the CSV file without opening the CSV file.
    The data is imported to the active sheet, i.e will import 50 rows x 17 columns.
    The vba code can of course be adjusted to change the source and destination locations.

    But there are certain rules to this process:
    1. The csv file will be temporarily renamed (via vba) from .csv to .xls for the import process to work
    (so make sure there isn't a similar named .xls file already, and the .csv file isn't currently open)
    2. After importing, the source csv file is then automatically renamed back from .xls to original .csv.
    3. Any dates in the csv file will be imported as numeric values only i.e. will not be formatted


    Please let me know if this works for you.
    I am sure you will be able to modify the vba code to suit your purposes.
    Any questions, please ask.

    zeddy
    Attached Files Attached Files

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks Z - I should be able to try this out on Sunday evening, and will post back then

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Sorry Martin. I made the assumption you were manually opening and closing. Read too much into it.

  12. #12
    New Lounger
    Join Date
    Dec 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up I changed a bit from excel help have a look

    Code:
    Sub testCSVimport()
    Workbooks.OpenText FileName:="W:\Abhishek Catalogue\01. Live Catalogue\Master Sheet Combination Uploaded 16122013.csv"
    
    Range("E1:E100000").Copy
    Workbooks("Upload sheet gen.xlsm").Activate
    Sheets("Temp").Select
    Range("A:A").Select
    ActiveSheet.Paste
    
    Workbooks("Master Sheet Combination Uploaded 16122013.csv").Close SaveChanges:=False
    End Sub

Posting Permissions

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