[RESOLVED] Importing data from Excel to Excel

[JohnGorst]

Hi

I need to write a macro that will import data contained in another spreadsheet, but am unsure how to do this.

Basically, I have several (about 15) spreadsheets that contain data. I need to import key bits of this data into one central spreadsheet that will be used for reporting purposes. I only need 2 cells worth (values) from each source spreadsheet, to be pasted into the destination spreadsheet, into designated cells.

The source spreadsheets are usually closed down and kept on a file server, which my PC has access to. Ideally I want to activate this macro with a control button - i.e. I press the button once and the macro goes off and collects/updates each field with the latest data stored in each of the source spreadsheets.

All I really need is something that does this for example:

Get data from "C:\FolderName\WorkbookName.xls", "worksheet1" "B21"
Paste it to "ActiveWorkbook", "worksheet3", "D34"

Then move to the next source data file...
etc
etc


I'm pretty sure it can be done, but I'm not very familiar with VB, hence my posting here. Does anyone have any ideas?

[Hack]

Excel VBA question moved to Office Development

[DKenny]

John, welcome to the Forums

In order to retrieve data from a workbook you will need to have that book open. If all the source books are contained in a single folder and can be identified as a group, you can use the DIR function to loop through the files.
Within the loop, you can open each file in turn, and retrieve the values from the specific cells (if its the same 2 cells in each book). I would suggest storing them in an array while going through the loop.
Once you have all the data in the array, you can then copy the entire array into your central reporting spreadsheet.

Let us know if you think this is a practical solution for you files and data structure and we can get the code together pretty quickly.

[JohnGorst]

Hi Declan, thanks for the welcome.

Yes, I thought it might require the file to be opened. I was wondering if an ADO command could do it without opening the file? Either way it doesn't really matter. Would the code also be able to close the file once it had been read? That would be tidier.

I could possibly edit the source files so that the data was in the same cells in each spreadsheet, but this would be an effort and it would mean that if I needed to create new source files they would have to follow the same format. It would be better if I could specify the cells and worksheets specific to each spreadsheet, for example:

"C:\FolderName\Book1.xls",
"worksheet1"
"D10"

"C:\FolderName\Book2.xls",
"worksheet5"
"G34"

"C:\FolderName\Book3.xls",
"worksheetABC"
"X5"

etc etc

And yes, when the array is full it could be dumped into the reporting excel file and worksheet, just as long as the values were pasted into the same cells consistently. Ideally I'd like to be able to specify which cells each value was pasted into, but it wouldn't matter if it was output as one big unformatted table as I'd just set up links to that table.

Many many thanks for your help with this, I really appreciate it.
John G

[DKenny]

John
It looks like an array may not be the best way to do this. Can I suggest we add a control table as a new sheet in the report workbook.
This table would contain the following data

1. Fully Path, including workbook name. (As in your example)
2. Worksheet Name. (Again as in your example)
3. Source Cell Address. (Yes, you had that one too)
4. Target Worksheet Name in the reporting workbook.
5. Target Cell Address.

The last two data in the table will alows us to load directly to the target cell.

If you are willing/able to maintain this reference table, it will be a relatively simple exercise to loop through this table and perform the following steps for each line.

1. Open the source workbook.
2. Retrieve the value from the cell on the worksheet.
3. Close the workbook.
4. Record the value in the correct cell on the reporting worksheet.

Let me know if you think this is a viable solution? If you can maintain the table the code will be very straightforward.

[JohnGorst]

Hi Declan

That's absolutely ideal. A reference table would be very easy for me to maintain and keep up to date, assuming it uses common syntax, punctuation etc.

It would be very useful if I was able to copy, paste & edit new lines into that table so that I could introduce more source spreadsheets if and when required.

Again, thanks for your help.

John G

[DKenny]

It would be very useful if I was able to copy, paste & edit new lines into that table so that I could introduce more source spreadsheets if and when required.

Absolutely, this is always a requirement for table driven code. I have included this in the following example.

This example assumes that the reference data is contained in a seperate worksheet in the reporting workbook, and that this worksheet only this reference data. The data should be in columns A to E with column titles in Row 1 and the data proper starts in row 2.
"RefTableSheetName" in the example will have to be changed to the sheet name of the sheet described above.

This code will need to be copied into the reporting workbook in order to function.

Have a read through the code and let me know if there is anything you need clarification on. Then try it out and let me know if you have any other questions.

Sub JohnGCopyData()
 
'Variables Declaration
Dim rngAddressTable As Range
Dim lTableLastRow As Long
Dim lRowNum As Long
Dim sPath As String
Dim sSourceSheetName As String
Dim sSourceCellAddress As String
Dim sDestSheetName As String
Dim sDestCellAddress As String
Dim wkbSourceBook As Workbook
Dim vCopyValue As Variant
    
    Application.Cursor = xlWait
    Application.ScreenUpdating = False
    
    'Create a reference to the reference table
    With ThisWorkbook.Worksheets("RefTableSheetName")
        
        'determine the last used row on the sheet
        lTableLastRow = .Range("A65536").End(xlUp).Row
        
        'We need to include cells A2 [ represeneted by .Cells(2, 1)]
        ' to Elastrowused[ represeneted by .Cells(lTableLastRow, 5)]
        Set rngAddressTable = .Range(.Cells(2, 1), .Cells(lTableLastRow, 5))
    End With
    
    'Loop through each row in the table
    For lRowNum = 1 To rngAddressTable.Rows.Count
        
        'Get the values from the table
        With rngAddressTable
            sPath = .Cells(lRowNum, 1) 'first column on curent row
            sSourceSheetName = .Cells(lRowNum, 2) 'second column on current row
            sSourceCellAddress = .Cells(lRowNum, 3) 'etc...
            sDestSheetName = .Cells(lRowNum, 4)
            sDestCellAddress = .Cells(lRowNum, 5)
        End With
        
        'Only continue if the Source file can be found
        If Len(Dir(sPath)) > 0 Then
            
            'Open the Source workbook
            Set wkbSourceBook = Application.Workbooks.Open(sPath)
            
            'Only continue if the Source Worksheet exists
            If SheetExists(wkbSourceBook.Name, sSourceSheetName) Then
                
                'Get the value
                vCopyValue = wkbSourceBook.Worksheets(sSourceSheetName).Range(sSourceCellAddress).Value
                
                'Now we can close the Source Workbook
                'without saving any changes
                wkbSourceBook.Close False
                
                'Only continue if the Destination Worksheet exists
                If SheetExists(ThisWorkbook.Name, sDestSheetName) Then
                    
                    'Add the value to the correct cell in this workbook
                    ThisWorkbook.Worksheets(sDestSheetName).Range(sDestCellAddress).Value = vCopyValue
                End If
            End If
            
        End If
    Next lRowNum
    
    Application.ScreenUpdating = False
    Application.Cursor = xlWait
    
    'Clear Object Variables
    Set wkbSourceBook = Nothing
End Sub
 
 
Function SheetExists(ByVal BookName As String, ByVal SheetName As String) As Boolean
Dim wkbBook As Workbook
Dim wksSheet As Worksheet
Dim bTemp As Boolean
    
    'Loop through all open workbooks
    For Each wkbBook In Application.Workbooks
        
        'Find a matching book name
        If wkbBook.Name = BookName Then
            
            'Loop through all sheets
            For Each wksSheet In wkbBook.Worksheets
                
                'If we find a match record a true value
                ' and exit the loop
                If wksSheet.Name = SheetName Then
                      
                      bTemp = True
                      Exit For
                End If
                
            Next wksSheet
            
        End If
        
        'No need to contine if we found a match
        If bTemp Then Exit For
    Next wkbBook
    
    'Set the function return value
    SheetExists = bTemp
    
End Function

[JohnGorst]

Hi Declan

Many thanks for this, I'll have a look this morning and let you know how it goes.

John
London, UK

[JohnGorst]

Hi Declan

OK, I've tried out the code and I'm running into a problem. I get:

Run-time error '1004':
" could not be found. Check the spelling of the file name and verify that the location is correct.

When I F8 to step through the code, it runs to Line 39:

If Len(Dir(sPath)) > 0 Then

Then jumps to line 62:

End If

which suggests that the criteria has not been met, i.e Len(Dir(sPath)) is not greater than zero.

It then re-runs through the code, starting from Line 30:

With rngAddressTable

And depending on how many rows of entries I have in the Reference Table, it either continues to cycle through, or eventually gets throws up the above error at line 42:

Set wkbSourceBook = Application.Workbooks.Open(sPath)

i.e. it continues to cycle through, but after 1, 2, 3 or more times it eventually moves onto Line 42, which it cannot execute.

The number of entries in the Ref Table does make a difference, so it's obviously reading it. I was wondering if I wasn't using the correct syntax for entries in the table? This is an example of the entries I have used (comma separated for purposes of this posting):
C:\Documents and Settings\jgorst\Desktop\Book3.xls,Sheet1,O12,SummaryPage,B2
I've tried putting "" marks around them, but this makes no difference.

I've also checked the obvious stuff like file names, locations, spellings of worksheets etc.

John

[zaza]

Hi,

Just to chip in a couple of cents worth, another way to do it is to put together a link formula and just leave it in the cell. Your workbook will ask you whether you want to update links every time you open it, and if you do then it will just go away and fish all the linked data out again.
Bear in mind, though, that if you move the source files with this method then the links will no longer be updatable.

Basically you need to put a formula in a cell that looks as follows:

='C:\path\[filename.xls]worksheet'!A1

Don't forget the single quotes...

zaza

[JohnGorst]

Hi Zaza

Thanks for the suggestion.

I'd considered this, and although it would work, I need to have a system that is updated by via a control button, linking to a macro. This is mainly due to the intended users of the reporting spreadsheet not being very competant :-)

For example, if they open the workbook, and they request that the values are updated, then fine. But if the need to refresh them again, then they need to either
1) close down and open the spreadsheet
or
2) go to Edit > Links > and refresh one by one. This is a big challenge for some of the completely non-technical users.

Neither of these are particularly pretty, and I'd like to go the extra mile to make it as simple as possible for them.

Many thanks for your suggestion though.
JG
London, UK

[DKenny]

John
My bad, I screwed up the line where we set the reference to the data range. I had the Column and Row numbers transposed.

Change

Set rngAddressTable = .Range(.Cells(1, 2), .Cells(5, lTableLastRow))

to

Set rngAddressTable = .Range(.Cells(2, 1), .Cells(lTableLastRow, 5))

and you should be good to go. You may also want to change the annotation to reflect this.

Also, when the code is finished I would suggest turning Screen Updating off while the code is running and turning it back on after the code is complete. This will improve performance.

[JohnGorst]

WOW!!! Declan, that works a treat. I'm thrilled.

My only remaining question is how to turn off screen updating? :-) Is there a way on Excel's menu or is it something that needs to be written in VB?

Thanks so much for helping me with this. Your generous use of comments throughout the code has also helped me understand what's going on with each step, and I can definitely say I've learnt something.

Thanks again,
John G
London

[DKenny]

After the variable declarations add the following lines.

The first turns the cursor to an hourglass (good visual hint to the user that the code is running)
The second turns screen updating off.

Application.Cursor = xlWait
    Application.ScreenUpdating = False

Then at the end of the procedure you will need to reset the cursor and turn screen updating back on. Intest the following after the Next lRowNum line.

Application.Cursor = xlDefault
    Application.ScreenUpdating = True

Glad to hear that this has been helpful, thats why we're here.

[JohnGorst]

Fantastic, it works much quicker with the screens not updating.

JohnG

[DKenny]

John.
One last thing, Can you mark the Thread as resolved (its under thread tools at the top of the page).

[zaza]

Hi Zaza

Thanks for the suggestion.

I'd considered this, and although it would work, I need to have a system that is updated by via a control button, linking to a macro. This is mainly due to the intended users of the reporting spreadsheet not being very competant :-)

For example, if they open the workbook, and they request that the values are updated, then fine. But if the need to refresh them again, then they need to either
1) close down and open the spreadsheet
or
2) go to Edit > Links > and refresh one by one. This is a big challenge for some of the completely non-technical users.

Neither of these are particularly pretty, and I'd like to go the extra mile to make it as simple as possible for them.

Many thanks for your suggestion though.
JG
London, UK

...or 3) Use the Update method for each link and put it in a button