VBA to import files with date extension

[mrexcelrc]

Hello Excel masters!
I am looking for some help to edit my macros below. This macro simply just imports the xml files at then end of each business day. Those files are saved in that location everyday. Additionaly, each file is also saved with a date extension for historical analysis purposes. For example, file "ALPTOTAL_RC_PORTBPV.xml" is also saved as "ALPTOTAL_RC_PORTBPV_20130404.xml". Each of these files are saved with that specific date format extension. How can I enhance this macro so that I can import any historical files for any specific date? For example, if I want to import all these files with date extension "20130226". Please help as I have been trying for 2 days now without any success at all. Thank you very much in advance for your help.




ActiveWorkbook.XmlMaps("report_output_Pv01").Import URL:= _
"Q:\Summit\Professional\ALPTOTAL_RC_PORTBPV.xml"
ActiveWorkbook.XmlMaps("report_output_Cflash").Import URL:= _
"Q:\Summit\Professional\CFLASH-ALPTOTAL.xml"
ActiveWorkbook.XmlMaps("report_output_MtM_Futures").Import URL:= _
"Q:\Summit\Professional\plupdMtM_futures.xml"
ActiveWorkbook.XmlMaps("report_output_shftAllcpty_BMA").Import URL:= _
"Q:\Summit\Professional\portrepShift_BMA_allcpty.xml"
ActiveWorkbook.XmlMaps("report_output_shftAllcpty_Libor").Import URL:= _
"Q:\Summit\Professional\portrepShift_allcpty.xml"
ActiveWorkbook.XmlMaps("report_output_shftFuts_Libor").Import URL:= _
"Q:\Summit\Professional\portrepShift_allcpty.xml"
ActiveWorkbook.XmlMaps("report_output_shftNonProfes_BMA").Import URL:= _
"Q:\Summit\Professional\portrepShift_BMA_nonprofes.xml"
ActiveWorkbook.XmlMaps("report_output_shftNonProfes_KNXVL").Import URL:= _
"Q:\Summit\Professional\portrepShift_KNXVL.xml"
ActiveWorkbook.XmlMaps("report_output_shftNonProfes_Libor").Import URL:= _
"Q:\Summit\Professional\portrepShift_nonprofes.xml"
ActiveWorkbook.XmlMaps("report_output_shftProfes_BMA").Import URL:= _
"Q:\Summit\Professional\portrepShift_BMA_profes.xml"
ActiveWorkbook.XmlMaps("report_output_shftProfes_Libor").Import URL:= _
"Q:\Summit\Professional\portrepShift_profes.xml"
ActiveWorkbook.XmlMaps("report_output_MtM").Import URL:= _
"Q:\Summit\Professional\portrep_MtM_allcpty.xml"

 

[JLGWhiz]

One way is to concatenate the date extension to the file name.

ActiveWorkbook.XmlMaps("report_output_Pv01").Import URL:= _
"Q:\Summit\Professional\ALPTOTAL_RC_PORTBPV_" & Format(2/26/2013, "yyyymmdd") & ".xml"

 

[mrexcelrc]

Hello JLGwhiz,
Wouldnt this require me to change the date for all files each time I pick a different date? Is there a way to declare a variable for the date so all i have to do is type a date in a cell where this variable will read? Can you please clarify? Thank you.

 

[JLGWhiz]

Yes, but it is the same code, just change the date in the code to a cell reference, for example cell A2 on Sheet1:

ActiveWorkbook.XmlMaps("report_output_Pv01").Import URL:= _
"Q:\Summit\Professional\ALPTOTAL_RC_PORTBPV_" & Format(Sheets("Sheet1").Range("A2").Value, "yyyymmdd") & ".xml"

Or using a variable

Dim myVal As Date
myVal = Sheets("Sheet1").Range("A2").Value
ActiveWorkbook.XmlMaps("report_output_Pv01").Import URL:= _
"Q:\Summit\Professional\ALPTOTAL_RC_PORTBPV_" & Format(myVal, "yyyymmdd") & ".xml"

 

[mrexcelrc]

Hello,
I tried your suggestions above but in both cases I get this run time error shown below. I'm not sure whether the error occurs on the first line where importing happens or on the 2nd line where the concatenation happens. The target file is highlighted below. Do I have to declare the path which I think I dont have since the patch is already specified in this line. I suspect this code results in a concatenated filename of "Q:\Summit\Professional\ALPTOTAL_RC_PORTBPV_20130327.xml" which I think may be the cause of the problem. The concatenated filename must be the same as the one shown in the screenshot below. Please help. Thank you.




-- removed inline image ---

-- removed inline image ---

 

[mrexcelrc]

Hello JLGWhiz,

I tried your suggestion and the code is below. I am getting error "Run-time error '-2147217376 (80041020)': XML Parse Error
I highlighted below where the code encounters the error. The value in Sheets("DashBoard").Range("S5") is 4/5/2013.
I have no success debugging this problem. Can you please help. Thank you very much.



Sub HistQuery()

Dim myVal As Date
myVal = Sheets("DashBoard").Range("S5").Value
ActiveWorkbook.XmlMaps("report_output_Pv01").Import URL:= _
"Q:\Summit\Professional\ALPTOTAL_RC_PORTBPV_" & Format(myVal, "yyyymmdd") & ".xml"





End Sub