How to pull XML data into another excel sheet using VBA?

[paulked]

I've had a sleep now!

You could limit the file open to compatible files, eg xml, csv or xl as in this example:

Sub ImportFile()
    Dim fName As Variant, fType As String, fPath As String
    fPath = Sheets("input").Range("e2").Text
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
TryAgain:
    ChDir fPath
    fName = Application.GetOpenFilename("Compatible Files *.xml *.csv *.xl*,*.xml;*.xl*;*.csv", 1, "Select a File to Import...")
    If fName = False Then GoTo Xit
    If LCase(Left(fName, Len(fPath))) <> LCase(fPath) Then
        If MsgBox("You cannot load a file from this directory.  Try again?", vbYesNo, "Wrong folder...") <> vbYes Then Exit Sub
        GoTo TryAgain
    End If
    fType = Right(fName, 3)
    If fType = LCase("xml") Or fType = LCase("csv") Then
        MsgBox "XML or CSV files can be imported direct to the workbook"
    Else
        MsgBox "Excel Files can be opened to manually copy data from or certain sheets/data copied from them via VBA"
    End If
    Exit Sub
Xit:
    MsgBox "No file loaded.", , "Cancelled?"
End Sub

This restricts files to XML, CSV or XL* and also to the directory or its sub directories in E2.

[StarBite207]

Tried using this VBA. It opens the directory specified but can't import any file.
When opening xml file, the message displays (xml or csv file can be imported direct to workbook)
and when trying Excel file it says (Excel files can be opened to manually copy data from or certain sheets/data copied from them via VBA)

[paulked]

Yes, because it's an example!

I have no idea what to do if it's an Excel file you are opening and don't know what other formats you use, or may use (csv for instance).

[StarBite207]

ok but how can i make this vba example work if say I want to open .xlsx file? Sorry, it's complicated for me to understand.

also a question for vba 2, does it work if i changed the file extension from .xml to .xlsx if i the file extension is already xlsx? or does it only work for .xml?

example:

Sub ImportBobH()
    Sheets("bob h").Cells.ClearContents
    Workbooks.Open Sheets("input").Range("e2") & "\bob h.xml" --> Instead of bob h.xml to bob h.xlsx?
    Sheets("bob h").Cells.Copy ThisWorkbook.Sheets("bob h").Range("a1")
    ActiveWorkbook.Close 0
End Sub

[paulked]

When you open an Excel file it can have more than one sheet.

What sheet would you like it to copy and to where? What if the sheet doesn't exist in the a) the newly opened workbook and b) the 'import' workbook? What if the sheet data is older than what you've already got? How do you tell if it is older? Do you want to load from any directory?

These are questions that have to be answered before starting, and maybe a few more! It can all be done in VBA, but not 'on the fly'.

I posted the example because you were interested. If you want to go ahead I'm willing to help, but only once you have a clear idea what goal you want to acheive

[StarBite207]

Ok .. so I have another macro-enabled excel workbook.

Sheet “Master” is where I will allocate 6 files locations as shown in the screenshot.

Screenshot 1.jpg

Each of the 6 directory locations has a common xlsm file name (Example; Ahmed – Daily File – Date.xlsm). But the date is not the same, so you have Ahmed – Daily File – 01 Apr 2020 and Ahmed – Daily File – 08 Apr 2020, etc .. (Side notes if they matter: the size of each file is 7 MB and all sheets are protected. All of the files are macro-enabled. These files have to be in different locations based on dated extractions for business purposes).

In each of these files, there is a common sheet named “2020”.

I want two vba codes:

1. First VBA assigned to each directory: The vba code to go the designated location, automatically opens and select that specific file name, copy sheet “2020” and paste it all in the data sheet specified for it (Example, Week 1 directory to load in Data Week 1) and then closes the file automatically. Off course, I want the destination sheet to be cleared first.
2. Second VBA: A Master vba code that runs the same action as the first one but for all directories one by one.

Also when pasting, I want it to paste values and format of the origin source but no formulas at all.

Due to above nature of the actions I want to do, I’m ok with the third vba you proposed that populates a window for me to select the files. But, if there’s a different one that does all the above without me selecting files – if possible, that would be great.

[paulked]

It would be a great help if you could post your workbook and a workbook you are pulling data from

[StarBite207]

The thing is the reports are work related and confidential .. is there a way to work this around?

[paulked]

You could strip out the confidential data and just leave the structure there. It really would save some guessing my end!

[paulked]

To save me going down the wrong route, I could do with knowing a few things.

1. Are you happy with initiating each macro by selecting a cell? My thoughts are selecting Week 1 (C41) would copy that weeks data to Data Week 1 Sheet and so on. To copy all weeks, select cell C46 (We can put "All Weeks" in there)
2. You say the sheets are protected, if they are password protected I'll need the password, or you enter it in run-time.
3. Is there only one XLSM file in each directory that ends with the date? (eg Ahmed – Daily File – 01 Apr 2020.xlsm)
4. What happens when Start Of Month equals the 1st day of week 1?
5. The name (eg Ahmed) has to be picked up from somewhere, can we have that in a cell?

Again, a sample of the structure of one of the 2020 sheets would be really helpful. It can contain nonsense... as long as the nonsense is in the right cells!

[StarBite207]

To answer your questions first:

1. Are you happy with initiating each macro by selecting a cell? My thoughts are selecting Week 1 (C41) would copy that weeks data to Data Week 1 Sheet and so on. To copy all weeks, select cell C46 (We can put "All Weeks" in there)

- I'm fine with this suggestion. Separate VBAs per each will work and I will assign the buttons per each.

2. You say the sheets are protected, if they are password protected I'll need the password, or you enter it in run-time.

- The password is only there to restrict the user from amending the sheet. It opens up normally with no password required to access.

3. Is there only one XLSM file in each directory that ends with the date? (eg Ahmed – Daily File – 01 Apr 2020.xlsm)

- The way I file, yes. One xlsm sheet with a particular name in that location. For the sake of giving here the example, had to put the file name out at such. In reality, it is (Hotel Name - xxx file name - Date.xlsm).

4. What happens when Start Of Month equals the 1st day of week 1?

- Didn't really understand that. But the start of the month is surely the extraction of the of the 1st might be the 2nd depends on a business calendar, then post that date up to 7 days would be considered the first week of the month. Anyhow, I segregate these files into different folders.

5. The name (eg Ahmed) has to be picked up from somewhere, can we have that in a cell?

Sure, I have added it up.

So here's the file. I replicated the original version of "2020".

Test 9.xlsx

And here's my file where I need all my imports to land:

WPU Monitor - VBA.xlsm

Taken some time to reformat and adjust before attaching

[paulked]

Thanks. Will hget stuck into it in the morning

[paulked]

Well, I think we're there! Try it out and let me know (I have put some notes on the master sheet).

[StarBite207]

See the file name I gave out was an example "Daily File" as I said before.
So, I changed the file name to the actual name without (dots, / or -) in cell D42.
Then I went to the VBA and adjusted where you mentioned " - Daily File - " to the original unified file name between the two dashes exactly (Nothing else was adjusted in any of the provided VBA.
Screenshot 2.jpg
It gave out the message "There isn't a valid file in the directory!"

3. The Test 9 file you sent me was an xlsx rather than a macro enabled file: I've allowed the code to open either, but it gives priority to xlsm files if both types are there --> Because I had to replicate the original file. Also, there is only one .xlsm file in the directory/else I can make sure that one .xlsm file is present in that directory when I'm running the report.

1. In modKed_Months there is a series of code that does the same as all your modules 1-10 --> Unfortunately, I couldn't try and see this feature because I had to delete the sheets in the original WPU Monitor sheet before up-loading. Can't up-load more than 1 MB here!

[paulked]

I don't completely understand what you are saying. Are you saying that:

Each of the 6 directory locations has a common xlsm file name (Example; Ahmed – Daily File – Date.xlsm).

is not a file named Ahmed - Daily File - Date.xlsm but something else?

[StarBite207]

The common name of the file is Hotel Name - xxx file - Date.xlsm (I just can't publicly put out the name here).
All these 6 directories have that file name. The only difference, the date is different.
I gave the name Ahmed - Daily File - Date.xlsm for the sake of an example here.

[paulked]

That would have been nice to know, even if secret you could have said *variable* or something. I'm enjoying a glass or two of wine now with the wife, will have a look asap.

[StarBite207]

Thanks a lot. Take your time, I'm not in a hurry

[paulked]

Try this one, notes in the file. I created directories to match yours and have run the routines successfully, I hope you have the same results

WPU Monitor - VBA(2).xlsm

[StarBite207]

Ok I tried it and we are almost there, but it didn't work.
First, the file name is *** - ** ******** file - 03 Mar 2020
So in your comments you asked me to write the first part of the file name as it appears and I wrote the first 3 letters without the dash. It gave out the message (There isn't a valid file in that directory!). I also tried it with a dash (*** -) also gave the same message.
Then I tried putting the entire file name without the date in the end like this (*** - ** ******** file -). And it worked in showing a message that is processing, the file in that directory was opened, then an error message appears (error 9 occurred. subscript out of range).
What seems to be the challenge?