What VB would I use to automate the import of text files into Excel based on user names found on a worksheet?

Question

We are migrating users from Novell server to Microsoft and we must preserve their drive mappings.

I have a spreadsheet which lists between say 1 and 75 user names. I have drive mapping files associated with most user names that indicates which drive mappings the user has set up on their machine. I would like from Excel to (the cell addresses and directory names are little changed from actual specific needs):

1. Create a batch file which takes the user names starting from cell D47 say up to cell D122 appends ".txt" to the end of the user name.

2. The batch file would then look for a text file by by each user name in the say H:\DriveMappings Directory and concatenates all the text files in one file say "all.txt." This process should not stop if Excel can't find a file with the name of say username1.txt, but should just continue to the next one.*

3. Lastly the macro should paste this into say cell K251 by way of the text import wizard and to use spaces and colons (":") to parse the data into 3 columns while leaving the the 4th and 5th column un-imported, since these columns contain non-essential inofrmation such as "Netware" or "Microsoft Server." *

*I just found out that a copy command could work for the concatenation, for instance:

H:\>copy pinchpa.txt+sortgr.txt+h235gz1.txt all.txt

However this command seems to fail if "DOS" can't find one of the files.

**Note, each drive line is preceded in the drive mapping file by the user names so it would be in the in the form:

pinchpa H: \\change05.net.company.org\gz_inc Netware
pinchpa I: \\change05.net.company.org\st_inc Microsoft Server
pinchpa S: \\change05.net.company.org\gz_inc_shared

etc.

Thanks much for any help!

It is not necessary for the colon to remain in the final data.

Answer 1

You could make an if then else with the check
If FileExists(filename) then
copy
Else
**Do Something
End if

Helmut

Answer 2

in accordance to the question on uploading the Excel 2007 documents to sq. Server 2005, i'd decide to grant you the innovations as follows: a million. Connection string subject: Please examine in case you may connect the two Excel 2007 and sq. Server 2005 as a results of fact the counsel source effectively. The excelConnection seems to be like discover as you provided. then you rather may additionally upload "HDR=specific" to show that the 1st row incorporates columnnames, no longer documents. "HDR=No;" exhibits the choice. this would stay away from the 1st row puzzled whilst uploading to the sq. tables. 2. Please try your ODBC sq. Server connection string purely so the relationship to sq. Server is right. different than the ODBC, you additionally can use SqlConnection and execute the SqlCommand merchandise with the assertion that consists of the excelConnection on your VB.internet code, yet no longer purely combine the whole in one sq. assertion. in this article exhibits you the way: Import Excel sheet into table 3. different than the above procedures, you are able to planned to apply SqlBulkCopy type to import. in this article exhibits you the occasion: Import Excel Spreadsheet documents into sq. Server Database table utilising SqlBulkCopy 4. there's a KB article in MSDN on the subject remember. notwithstanding the article isn't utilized for sq. Server 2005, you additionally can learn on sq. Server documents Transformation centers (DTS), Microsoft sq. Server 2005 Integration centers (SSIS), sq. Server proper servers and sq. Server disbursed queries. they are effective on your artwork. the thank you to import documents from Excel to sq. Server desire that would help you.