VBA Export Excel to XML format and save to specific directory -

i'm finished building large macro have on efinal stumbling block. have produced sheet expected values need export xml format. need export particular sheet called "output". there issues when exported text had create sub exporttoxml remove quotation marks produced on export. worked fine. when export have manually type in directory , filename want on output file isnt good. directory may change time time , filename.

sub exporttoxml(filename string, ws worksheet) on error goto endmacro: dim fnum integer fnum = freefile dim startrow long, endrow long dim startcol integer, endcol integer ws.usedrange startrow = .cells(1).row startcol = .cells(1).column endrow = .cells(.cells.count).row endcol = .cells(.cells.count).column end with

open filename output access write #fnum dim rowndx long, colndx integer dim wholeline string, cellvalue string rowndx = startrow endrow     wholeline = ""     colndx = startcol endcol         if ws.cells(rowndx, colndx).value = ""             cellvalue = ""         else             cellvalue = ws.cells(rowndx, colndx).value             if removecommanums                 if isnumeric(cellvalue)                     cellvalue = replace(cellvalue, ",", "")                 end if             end if         end if         wholeline = wholeline & cellvalue & sep     next colndx     wholeline = left(wholeline, len(wholeline) - len(sep))     print #fnum, wholeline; " " next rowndx endmacro: on error goto 0 close #fnum end sub  sub saveas() fmt string, directory string, tradeid string fmt = ".txt" tradeid = worksheets("xml instruction").range("b16").value directory = worksheets("euc").range("c7").value dim op worksheet set op = sheets("output")  exporttoxml "i:\test.txt", op  end sub 

the last part of savas sub part have issue on :- exporttoxml "i:\test.txt", op . had manually enter location (i:) , filename (test) output file.

can not reference directory & tradeid example determine file export , filename be? why have referenced directory, tradeid, fmt.

you can. write:

exporttoxml directory & "\" & tradeid & "\" & fmt, op

make sure directory valid directory , tradeid not have special chars not permitted.