excel vba - VBA Copy data from an unopened CSV file to worksheet without opening closed CSV -

i believe have unique problem have not seen anywhere on internet.

i business analyst/application developer , want automatically gather data other user's excel csv file on personal computer without opening file , disrupting them. there way?

here code have far:

option explicit  dim mydocuments string, strfilename, mytoday, origworkbook, origworksheet, strconnection dim row integer  private sub btnstart_click()     mydocuments = environ$("userprofile") & "\my documents"     mytoday = format(date, "mmddyy")     strfilename = "datafile" & mytoday & ".csv"     strconnection = "text;" & mydocuments & "\datafolder\" & strfilename     origworksheet = "datafile" & mytoday      row = 1     on error resume next     row = range("a1").end(xldown).row + 1      activesheet.querytables.add(connection:=strconnection, destination:=range("$a$" & row))         .name = "temp"         .fieldnames = true         .rownumbers = false         .filladjacentformulas = false         .preserveformatting = true         .refreshonfileopen = false         .refreshstyle = xlinsertdeletecells         .savepassword = false         .savedata = true         .adjustcolumnwidth = true         .refreshperiod = 0         .textfilepromptonrefresh = false         .textfileplatform = 437         .textfilestartrow = 1         .textfileparsetype = xldelimited         .textfiletextqualifier = xltextqualifierdoublequote         .textfileconsecutivedelimiter = false         .textfiletabdelimiter = false         .textfilesemicolondelimiter = false         .textfilecommadelimiter = true         .textfilespacedelimiter = false         .textfilecolumndatatypes = array(1)         .textfiletrailingminusnumbers = true         .refresh backgroundquery:=false     end end sub 

like said, don't want csv file open while working. behind scenes can keep working while gather data.

i'm guessing biggest hang it's csv file, or file not open. if there's way can done, please let me know. currently, getting out of range error.

assuming want grab data , put in current workbook. recorded macro using data -> import data method , in vba , seems work csv file closed:

print consecutive column:

sub macro1()      dim mydocuments, strfilename, mytoday, file, strconnection string      mydocuments = environ$("userprofile") & "\my documents"     mytoday = format(date, "mmddyy")     strfilename = "datafile" & mytoday & ".csv"      strconnection = "text;" & mydocuments & "\datafolder\" & strfilename     activesheet.querytables.add(connection:= _          strconnection, destination:=range("$a$1"))         .name = "temp"         .fieldnames = true         .rownumbers = false         .filladjacentformulas = false         .preserveformatting = true         .refreshonfileopen = false         .refreshstyle = xlinsertdeletecells         .savepassword = false         .savedata = true         .adjustcolumnwidth = true         .refreshperiod = 0         .textfilepromptonrefresh = false         .textfileplatform = 437         .textfilestartrow = 1         .textfileparsetype = xldelimited         .textfiletextqualifier = xltextqualifierdoublequote         .textfileconsecutivedelimiter = false         .textfiletabdelimiter = false         .textfilesemicolondelimiter = false         .textfilecommadelimiter = true         .textfilespacedelimiter = false         .textfilecolumndatatypes = array(1)         .textfiletrailingminusnumbers = true         .refresh backgroundquery:=false     end end sub 

print consecutive row:

here have add

dim row integer     row = 1     on error resume next      row = range("a1").end(xltoright).end(xldown).row + 1 

and instead of: destination:=range("$a$1") use row variable: destination:=range($a$" & row)

sub macro1()      dim mydocuments, strfilename, mytoday, file, strconnection string      mydocuments = environ$("userprofile") & "\my documents"     mytoday = format(date, "mmddyy")     strfilename = "datafile" & mytoday & ".csv"      dim row integer     row = 1     on error resume next     row = range("a1").end(xldown).row + 1      strconnection = "text;" & mydocuments & "\datafolder\" & strfilename     activesheet.querytables.add(connection:= _          strconnection, destination:=range("$a$" & row))         .name = "temp"         .fieldnames = true         .rownumbers = false         .filladjacentformulas = false         .preserveformatting = true         .refreshonfileopen = false         .refreshstyle = xlinsertdeletecells         .savepassword = false         .savedata = true         .adjustcolumnwidth = true         .refreshperiod = 0         .textfilepromptonrefresh = false         .textfileplatform = 437         .textfilestartrow = 1         .textfileparsetype = xldelimited         .textfiletextqualifier = xltextqualifierdoublequote         .textfileconsecutivedelimiter = false         .textfiletabdelimiter = false         .textfilesemicolondelimiter = false         .textfilecommadelimiter = true         .textfilespacedelimiter = false         .textfilecolumndatatypes = array(1)         .textfiletrailingminusnumbers = true         .refresh backgroundquery:=false     end end sub 

this grab of csv data , put in a1 can change $a$1 whatever location want. of course can change of other variables also, recorded macro , edited strconnection variable match location described in question.

hopefully looking for, if not let me know.