I have data coming in from a database into excel format, I need a macro which will format the data the way i want it to and then a macro for linking the excel table with Access and export to XML from there. Below is the process which I have to follow. Please look at the attached excel files to get a beter understanding:
1) I get the data from a database in the form of a crosstab, the layout has some blanks which needs to be fixed.
2) This data needs to be linked to another excel workbook which will be used to format the data. If you look at the zip file and open 2008_Actual&[login to view URL] the CampusSummary (3) worksheet should be linked to the Campus excel workbook which is also attached.
3) The XML worksheet in 2008_Actual&[login to view URL] is the formatted data, I basically check for blanks and if there is a blank i just copy the data from the cell above. This is not a efficient way as the data will change every week and there is no way of knowing how many rows of data there are.
4) I need a macro which will automate this, so on the press of a button i will get the data in the another excel file in the format which you see in the XML worksheet.
5) The XML worksheet needs to be exported to XML, which I do using Access, so basically I import this worksheet into Access and then export to xml. I need a way of automating this as well, so on the press of a button I can get the XML file.