Folder Selection Dialog Box (VBA, Excel 2010) causing files to be unreadable -

this may quite lengthy description, please bear me. problems experiencing excel related custom macros, vba, , file access.

background: trying write macro process multiple csv workbooks of data. have files named rawdata_1.csv until rawdata_x.csv x number of files have in particular folder.

my code macro looks this:

sub importdata()      application.screenupdating = false      dim strdir string     dim strfilename string     dim wbtocopy workbook     dim intcol integer      set master = activesheet      **please see below 2 versions of code can go here!**      strfilename = dir(strdir & "\*.csv")      intcol = 2      while len(strfilename) > 0         set wbtocopy = workbooks.open(strfilename, , true)          //do other things need here          wbtocopy.close (false)          strfilename = dir         intcol = intcol + 2     loop      application.screenupdating = true  end sub 

problem 1 (tiny problem): when execute macro, files somehow "change," , next time execute same macro in same folder, report files not found. exact error is:

run-time error '1004':

'rawdata_1.csv' not found. check spelling of file name, , verify file location correct.

if trying open file list of used files, make sure file has been renamed, moved, or deleted.

i have figured out solution problem. have go folder csv files, open first 1 in list, , "save as" ms-dos csv file. once have done this, can run macro , able open files (not first file "save as"-ed).

while annoying, it's not worst thing in world. if there reason why excel doing this, love know! if there solution problem, better!

problem 2 (big problem) main puzzle solve. in code above, section missing part of code tells excel (or macro) find files. can hardcoding in path follows:

method 1:

strdir = "c:\whateverpath" 

this method works (except when problem 1 encountered above).

however, not best way write macro, since not using once, need use multiple times, , data files wish import in various folders. therefore, tried write follows:

method 2:

dim folderdialog filedialog  set folderdialog = application.filedialog(msofiledialogefolderpicker) folderdialog.allowmultiselect = false folderdialog.show  strdir = folderdialog.selecteditems(1) 

i compared strdir method 1 , strdir method 2 , found no discernable differences in values. both contain correct path "c:\whateverpath".

however, using method 2, excel unable read of files in selected folder. return same run-time error 1004 above, , quick fix found problem 1 above nothing macro run.

if has idea going on here, appreciate figuring out!

edit: think found issue. set wbtocopy = workbooks.open(strfilename, , true) , strfilename not use qualified path. when call .open method, believe vba using curdirvalue , appending strfilename. when perform "save as", curdir value changed directory saving .csv files in. gives illusion "save as" action allows macro run. in actuality, act of changing curdir value directory files located. use qualified file name .open , should run everytime.

previous suggestion: don't think file name full qualified (your error message should read 'c:\whateverpath\rawdata_1.csv' not found. not 'rawdata_1.csv' not found.).

i having tough time tracking down error in code. kind of hacked if stuck, try using this:

option explicit sub importdata()  application.screenupdating = false  dim strdir string dim strfoldername string dim wbtocopy workbook dim intcol integer dim master excel.worksheet  dim fso object dim fso_folder object dim fso_file object dim file_ext string  file_ext = "csv" strfoldername = get_folder_path() & "\"  ''create filesystem objects set fso = createobject("scripting.filesystemobject")  set fso_folder = fso.getfolder(strfoldername)  set master = thisworkbook.activesheet  ''**please see below 2 versions of code can go here!**  intcol = 2  if fso_folder.files.count > 0  ''loop through each file in folder each fso_file in fso_folder.files     ''test extension    if fso.getextensionname(fso_file.name) = file_ext         set wbtocopy = workbooks.open(strfoldername & fso_file.name, , true)          ''//do other things need here          wbtocopy.close (false)         intcol = intcol + 2     else: end if  next  else  msgbox "no files found @ " & strfoldername  end if  set fso = nothing set fso_folder = nothing  application.screenupdating = true  end sub  function get_folder_path() string dim folderdialog filedialog set folderdialog = application.filedialog(4) folderdialog.allowmultiselect = false folderdialog.show  get_folder_path = folderdialog.selecteditems(1)  end function 

note uses filesystem library instead of native dir function. select folder name instead of file name dialog box.