Page 1 of 2 12 LastLast
Results 1 to 10 of 15

opening external files with VBA

This is a discussion on opening external files with VBA within the Excel Questions forums, part of the Question Forums category; Hi I was wondering if somebody could help me out here: On the first worksheet of my workbook I have ...

  1. #1
    Board Regular
    Join Date
    Apr 2006
    Posts
    206

    Default opening external files with VBA

    Hi

    I was wondering if somebody could help me out here:

    On the first worksheet of my workbook I have a list of file names in cells I11:I27. The filenames have been 'compiled' using the concatenate function, so for example, although cell I11 displays:

    Richard 2007-09.xls,

    the cell contents are really:

    =CONCATENATE(G20," ",L9,"-",J9,".xls")

    Using VBA, I want to be able to open each respective file in cells I11:I27, copy the contents into this workbook (sheet=raw), and close it. My problem is that I don't know how to tell excel the filename in VBA, because the cells contents are not really the filename - they are a formula.

    I hope I have explained this ok, and if anyone can help me out I'd appreciate it.

    thanks

    Rich

  2. #2
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656

    Default

    Rich,

    "I want to be able to open each respective file in cells I11:I27".

    What is the full path to the folder where the above files reside?

    Are there multiple worksheets in each workbook?

    Or, do you want to copy only one worksheet?

    If you want to copy only one worksheet, what is the sheetname? And, what is the range to copy?

    Have a great day,
    Stan

  3. #3
    Board Regular
    Join Date
    Apr 2006
    Posts
    206

    Default

    Hi Stan

    sorry - i could have been clearer!

    I have loads of raw data files (all filenames in the format: employee yyyy-mm.xls). I don't know the path to where the files will be stored eventually - do I need to put this in if all of the workbooks I'm using are in the same folder? If so, lets say it's "c:\my documents\data\filename.xls".

    The workbook containing the list of filenames is called "formatter.xls", and the idea it to open each respective file, select the entire sheet of "sheet1", paste it into "formatter.xls" into cell A1 of sheet "raw", then close the raw data file.

    In the workbook "formatter.xls" there are multiple sheets, which I will be using to format the raw data, but in the raw data files - there are just the standard 3 sheets Sheet1 Sheet2 & Sheet3, but only sheet1 contains data.

    Hope this is a bit clearer!

    Rich

  4. #4
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656

    Default

    Rich,

    If all the files that you want to copy from are in the same folder, then all we need is the full path to that location.

    For example the full path could be:
    "c:\my documents\data\"

    Do you want to copy a specific range from Sheet1 in all the files?

    Will the copied data go into one summary worksheet, or into their own worksheets?

    Have a great day,
    Stan

  5. #5
    Board Regular
    Join Date
    Apr 2006
    Posts
    206

    Default

    Hi again Stan

    the data range in the raw data files I am importing will change every time I receive them, and so I just want to select the entire worksheet (sheet1) and import it into formatter.xls onto the sheet called "raw" which will be blank.

    I have cobbled together a macro which will do all the formatting I need - it formats the data onto 3 sheets (cash, share, annual). After this I will export a copy of all 4 sheets (cash, share, annual, raw) to a new workbook with the filename "employee.xls" (all employees names are in cells G11:G27 on the sheet "front", along side the raw data filenames)

    This second part, I reckon I will be able to botch together, if I can get some help on the first part. i.e. if I can get the raw data files opened and the raw data pasted onto Sheet="raw", I reckon I could give the rest a shot.

    many thanks for you time Stan,

    Rich

  6. #6
    New Member
    Join Date
    Oct 2007
    Location
    Australia
    Posts
    16

    Default

    If you dont need to keep the formular in those cells just the file names then a paste special can remove the formular but leave the values as this script does, or maybe a copy paste specail into the cells next to the formular. might get you started.

    script/
    'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    'This section selects the cells in the required sheet and removes the formulars used for sorting and
    'deletes column A,B and D which are no longer required.

    Worksheets("Required").Activate
    Range("A1:G405").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("A:B,D:D").Select
    Selection.Delete Shift:=xlToLeft
    /Script

  7. #7
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656

    Default

    Rich,

    Here you go.

    I have tested the code in my environment, and with test files to read from (the information on your "front" worksheet) and write to the new files.

    There is a section in the code that indicates where you can place your code to format the four sheets "cash, share, annual, raw", and create new workbooks for the employees with copies of the four worksheets.


    Please TEST this FIRST in a COPY of your workbook.

    Press and hold down the 'ALT' key, and press the 'F11' key.

    Insert a Module in your VBAProject, Microsoft Excel Objects

    Copy the below code, and paste it into the Module1.

    Code:
    Option Explicit
    Sub OpenCopyCloseFormatSaveNewFiles()
        
    'Using VBA, I want to be able to open each respective file in cells I11:I27,
    '  copy the contents into this workbook (sheet=raw), and close it.
    'After this I will export a copy of all 4 sheets (cash, share, annual, raw)
    '  to a new workbook with the filename "employee.xls" (all employees names are in cells G11:G27
    '  on the sheet "front", along side the raw data filenames).
    
        Dim wbToOpen As Workbook
        Dim wbCodeBook As Workbook
        Dim lngLoopCtr As Long
    
        Dim strFileNameToOpen As String
        Dim strEmployeeFileName As String
    
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.EnableEvents = False
         
        On Error Resume Next
         
        Set wbCodeBook = ThisWorkbook
        With wbCodeBook
            'Loop from row 11 to 27
            'strFileNameToOpen = wbCodeBook.Worksheets("front").Cells(lngLoopCtr, "I").Value
            'strEmployeeFileName = wbCodeBook.Worksheets("front").Cells(lngLoopCtr, "G").Value & ".xls"
            
            For lngLoopCtr = 11 To 27 Step 1
                strFileNameToOpen = wbCodeBook.Worksheets("front").Cells(lngLoopCtr, "I").Value
                With Application.FileSearch
                    .NewSearch
                    
                    '------------------------------------------------------------
                    'Change path to suit
                    .LookIn = "c:\my documents\data"
    
    
                    '------------------------------------------------------------
                    
                    
                    '.FileType = msoFileTypeExcelWorkbooks
                    .Filename = strFileNameToOpen
            
                    Set wbToOpen = Workbooks.Open(Filename:=.Filename)
        
                    wbToOpen.Worksheets("Sheet1").Cells.Copy wbCodeBook.Worksheets("raw").Cells
                    Application.CutCopyMode = False
        
                    wbToOpen.Close savechanges:=False
        
                End With
    
                '------------------------------------------------------------
                strEmployeeFileName = wbCodeBook.Worksheets("front").Cells(lngLoopCtr, "G").Value & ".xls"
    
                'Your code to format data, create additional sheets,
                '  copy all 4 sheets (cash, share, annual, raw)
                '  to a new workbook with the filename "employee.xls"
                '  (all employees names are in cells G11:G27 on the sheet "front", along side the raw data filenames)
    
    
    
    
    
    
                'Clear sheets cash, share, annual, raw, for next file to open/employee file to save.
                wbCodeBook.Worksheets("raw").Cells.Clear
                wbCodeBook.Worksheets("cash").Cells.Clear
                wbCodeBook.Worksheets("share").Cells.Clear
                wbCodeBook.Worksheets("annual").Cells.Clear
    
            Next lngLoopCtr
            
        End With
    
        On Error GoTo 0
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        Application.EnableEvents = True
    
    End Sub

    Please TEST this FIRST in a COPY of your workbook.

    Then run the 'OpenCopyCloseFormatSaveNewFiles' macro.

    Have a great day,
    Stan

  8. #8
    Board Regular
    Join Date
    Apr 2006
    Posts
    206

    Default

    Hi Stan,

    Firstly - wow, and thanks a lot! I wasn't expecting help like that. I have insterted the code into the VBA module, and tried to run it, but the bit I thought I'd be OK with, I'm actually struggling with!

    I have inserted your code, and in the section which you left for me to add my 'formatting code' I have just put this statement:

    Application.Run "'Sales data viewer.xls'!FormatMyData"

    because I already had that peice of code saved. After this code has run, I was hoping to be able to get excel to copy 4 worksheets (cash, share, annual & raw) to another workbook, and save it as "employee.xls", where the empolyees are listed in cells G11:G27. I thought this would be fairly easy once I saw what you had done with the first part - unfortunately, although very impressive - it is way over my head!

    I feel very cheeky asking for more help after what you have already sent me, but if you have time could you show me how to incorporate this?

    Thanks again -

    Rich

  9. #9
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656

    Default

    Rich,

    I will be posting, tonight or tomorrow morning, the new code for:
    "I was hoping to be able to get excel to copy 4 worksheets (cash, share, annual & raw) to another workbook, and save it as "employee.xls", where the empolyees are listed in cells G11:G27."

    I have been testing my workbook with those additional sheetnames.

    What file contains the "cash, share, annual & raw" sheetnames that you are referring to?

    Have a great day,
    Stan

  10. #10
    Board Regular
    Join Date
    Apr 2006
    Posts
    206

    Default

    Hi Stan

    Apologies for not replying sooner - my wife and son weren't too well for the last few days, and I haven't had chance to get on the computer.

    Thanks once again for your reply - the cash, share, annual & raw sheets are in a file called "formatter.xls". Initially all four of these sheets are blank - the individual raw data files are copied and pasted onto sheet "raw", and then my formatting code is run (the name of the macro is FormatMyData). My code creates various tables & charts from the raw data and places them on the sheets "cash, share & annual". Once my code has run I would like to export a copy of sheets "cash", "share", "annual", and "raw" into another workbook, save it, and then clear these 4 sheets in the original file (formatter.xls) ready to import the next raw data file.

    Hope I explained this OK, and thanks again,

    Rich

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com