Solved

Read text file, add data and then write text file VBA

Posted on 2014-01-20
10
381 Views
Last Modified: 2014-01-27
Hi All,
I have a spreadsheet with about 110 lines of data and I need to dynamically generate a text file for each of the rows in the spreadsheet. The text file is a prf file to configure outlook and requires that only certain sections are changed to match the spreadsheet. What I would like to achieve is reading a text file, adding the required data then reading the next section of the file adding data etc. I need to do this with a VBA macro, I have created a macro which outputs concatenated data to a text file but the file looses all formatting which is not acceptable so I think the only way is to read a file, add data and repeat the process and once this is done output a text file. Unfortunately I have no clue how to achieve this can anyone spread any light on this or provide some example code?

Thanks in advance,
Alex.
0
Comment
Question by:alexcarter404
  • 6
  • 3
10 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39794463
What do you mean by 'looses all formatting'? Could you post your code, and explain the difference between what you get and what you need?
0
 
LVL 3

Author Comment

by:alexcarter404
ID: 39796390
Hi Graham,
Basically all line breaks are lost from the original file meaning that the new file is just word wrapped rather than seperated by line breaks. I have just lost the code I was using but I will recreate it and post the code shortly.

Thanks,
Alex
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39796528
Here is a way to save the text in a column as lines in a text file:
Sub SaveToTextFile()
    Dim f As Integer
    Dim sh As Worksheet
    Dim r As Integer
    
    f = FreeFile
    r = 2
    Set sh = ActiveWorkbook.Sheets(1)
    
    Open "C:\MyFolder\MyFile.txt" For Output As #f
        Do Until sh.Cells(r, 1).Value = ""
            Print #f, sh.Cells(r, 1).Value
            r = r + 1
        Loop
    Close #f
End Sub

Open in new window

0
 
LVL 3

Author Comment

by:alexcarter404
ID: 39796922
Hi Graham,
Thanks for the reply. I have created some new code which generates a file using the WriteLine function provided in VBA as below:

Sub CreateFile()

Dim fso As New FileSystemObject
Dim ts As TextStream
Dim filename As String
Dim email As String
Dim accname As String
'open this file to write to it
Set ts = fso.CreateTextFile("C:\test\" & filename, True)

ts.WriteLine (";Automatically generated PRF file from the Microsoft Office Customization and Installation Wizard")
ts.WriteLine ("")
ts.WriteLine ("; **************************************************************")
ts.WriteLine ("; Section 1 - Profile Defaults")
ts.WriteLine ("; **************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[General]")
ts.WriteLine ("Custom=1")
ts.WriteLine ("ProfileName=Outlook")
ts.WriteLine ("DefaultProfile=Yes")
ts.WriteLine ("OverwriteProfile=Yes")
ts.WriteLine ("ModifyDefaultProfileIfPresent=FALSE")
ts.WriteLine ("")
ts.WriteLine ("; **************************************************************")
ts.WriteLine ("; Section 2 - Services in Profile")
ts.WriteLine ("; **************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[Service List]")
ts.WriteLine (";ServiceX=Microsoft Outlook Client")
ts.WriteLine ("")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("; Section 3 - List of internet accounts")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[Internet Account List]")
ts.WriteLine ("Account1=IMAP_I_Mail")
ts.WriteLine ("")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("; Section 4 - Default values for each service.")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("")
ts.WriteLine (";[ServiceX]")
ts.WriteLine (";FormDirectoryPage=")
ts.WriteLine (";-- The URL of Exchange Web Services Form Directory page used to create Web forms.")
ts.WriteLine (";WebServicesLocation=")
ts.WriteLine (";-- The URL of Exchange Web Services page used to display unknown forms.")
ts.WriteLine (";ComposeWithWebServices=")
ts.WriteLine (";-- Set to TRUE to use Exchange Web Services to compose forms.")
ts.WriteLine (";PromptWhenUsingWebServices=")
ts.WriteLine (";-- Set to TRUE to use Exchange Web Services to display unknown forms.")
ts.WriteLine (";OpenWithWebServices=")
ts.WriteLine (";-- Set to TRUE to prompt user before opening unknown forms when using Exchange Web Services.")
ts.WriteLine ("")
ts.WriteLine ("")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("; Section 5 - Values for each internet account.")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[Account1]")
ts.WriteLine ("UniqueService=No")
ts.WriteLine "AccountName=" & accname
ts.WriteLine ("IMAPServer=outlook.office365.com")
ts.WriteLine ("SMTPServer=smtp.office365.com")
ts.WriteLine "IMAPUserName=" & email
ts.WriteLine "EmailAddress=" & email
ts.WriteLine ("IMAPUseSPA=0")
ts.WriteLine ("DisplayName=")
ts.WriteLine ("ReplyEMailAddress=")
ts.WriteLine ("SMTPUseAuth=1")
ts.WriteLine ("SMTPAuthMethod=0")
ts.WriteLine ("ConnectionType=0")
ts.WriteLine ("RootFolder=")
ts.WriteLine ("ConnectionOID=MyConnection")
ts.WriteLine ("IMAPPort=993")
ts.WriteLine ("IMAPUseSSL=1")
ts.WriteLine ("ServerTimeOut=60")
ts.WriteLine ("SMTPPort=587")
ts.WriteLine ("SMTPUseSSL=1")
ts.WriteLine ("")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("; Section 6 - Mapping for profile properties")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[Microsoft Exchange Server]")
ts.WriteLine ("ServiceName=MSEMS")
ts.WriteLine ("MDBGUID=5494A1C0297F101BA58708002B2A2517")
ts.WriteLine ("MailboxName=PT_STRING8,0x6607")
ts.WriteLine ("HomeServer=PT_STRING8,0x6608")
ts.WriteLine ("OfflineAddressBookPath=PT_STRING8,0x660E")
ts.WriteLine ("OfflineFolderPath=PT_STRING8,0x6610")
ts.WriteLine ("")
ts.WriteLine ("[Exchange Global Section]")
ts.WriteLine ("SectionGUID=13dbb0c8aa05101a9bb000aa002fc45a")
ts.WriteLine ("MailboxName=PT_STRING8,0x6607")
ts.WriteLine ("HomeServer=PT_STRING8,0x6608")
ts.WriteLine ("RPCoverHTTPflags=PT_LONG,0x6623")
ts.WriteLine ("RPCProxyServer=PT_UNICODE,0x6622")
ts.WriteLine ("RPCProxyPrincipalName=PT_UNICODE,0x6625")
ts.WriteLine ("RPCProxyAuthScheme=PT_LONG,0x6627")
ts.WriteLine ("CachedExchangeConfigFlags=PT_LONG,0x6629")
ts.WriteLine ("")
ts.WriteLine ("[Microsoft Mail]")
ts.WriteLine ("ServiceName=MSFS")
ts.WriteLine ("ServerPath=PT_STRING8,0x6600")
ts.WriteLine ("Mailbox=PT_STRING8,0x6601")
ts.WriteLine ("Password=PT_STRING8,0x67f0")
ts.WriteLine ("RememberPassword=PT_BOOLEAN,0x6606")
ts.WriteLine ("ConnectionType=PT_LONG,0x6603")
ts.WriteLine ("UseSessionLog=PT_BOOLEAN,0x6604")
ts.WriteLine ("SessionLogPath=PT_STRING8,0x6605")
ts.WriteLine ("EnableUpload=PT_BOOLEAN,0x6620")
ts.WriteLine ("EnableDownload=PT_BOOLEAN,0x6621")
ts.WriteLine ("UploadMask=PT_LONG,0x6622")
ts.WriteLine ("NetBiosNotification=PT_BOOLEAN,0x6623")
ts.WriteLine ("NewMailPollInterval=PT_STRING8,0x6624")
ts.WriteLine ("DisplayGalOnly=PT_BOOLEAN,0x6625")
ts.WriteLine ("UseHeadersOnLAN=PT_BOOLEAN,0x6630")
ts.WriteLine ("UseLocalAdressBookOnLAN=PT_BOOLEAN,0x6631")
ts.WriteLine ("UseExternalToHelpDeliverOnLAN=PT_BOOLEAN,0x6632")
ts.WriteLine ("UseHeadersOnRAS=PT_BOOLEAN,0x6640")
ts.WriteLine ("UseLocalAdressBookOnRAS=PT_BOOLEAN,0x6641")
ts.WriteLine ("UseExternalToHelpDeliverOnRAS=PT_BOOLEAN,0x6639")
ts.WriteLine ("ConnectOnStartup=PT_BOOLEAN,0x6642")
ts.WriteLine ("DisconnectAfterRetrieveHeaders=PT_BOOLEAN,0x6643")
ts.WriteLine ("DisconnectAfterRetrieveMail=PT_BOOLEAN,0x6644")
ts.WriteLine ("DisconnectOnExit=PT_BOOLEAN,0x6645")
ts.WriteLine ("DefaultDialupConnectionName=PT_STRING8,0x6646")
ts.WriteLine ("DialupRetryCount=PT_STRING8,0x6648")
ts.WriteLine ("DialupRetryDelay=PT_STRING8,0x6649")
ts.WriteLine ("")
ts.WriteLine ("[Personal Folders]")
ts.WriteLine ("ServiceName=MSPST MS")
ts.WriteLine ("Name=PT_STRING8,0x3001")
ts.WriteLine ("PathToPersonalFolders=PT_STRING8,0x6700 ")
ts.WriteLine ("RememberPassword=PT_BOOLEAN,0x6701")
ts.WriteLine ("EncryptionType=PT_LONG,0x6702")
ts.WriteLine ("Password=PT_STRING8,0x6703")
ts.WriteLine ("")
ts.WriteLine ("[Unicode Personal Folders]")
ts.WriteLine ("ServiceName=MSUPST MS")
ts.WriteLine ("Name=PT_UNICODE,0x3001")
ts.WriteLine ("PathToPersonalFolders=PT_STRING8,0x6700 ")
ts.WriteLine ("RememberPassword=PT_BOOLEAN,0x6701")
ts.WriteLine ("EncryptionType=PT_LONG,0x6702")
ts.WriteLine ("Password=PT_STRING8,0x6703")
ts.WriteLine ("")
ts.WriteLine ("[Outlook Address Book]")
ts.WriteLine ("ServiceName=CONTAB")
ts.WriteLine ("")
ts.WriteLine ("[LDAP Directory]")
ts.WriteLine ("ServiceName=EMABLT")
ts.WriteLine ("ServerName=PT_STRING8,0x6600")
ts.WriteLine ("UserName=PT_STRING8,0x6602")
ts.WriteLine ("UseSSL=PT_BOOLEAN,0x6613")
ts.WriteLine ("UseSPA=PT_BOOLEAN,0x6615")
ts.WriteLine ("DisableVLV=PT_LONG,0x6616")
ts.WriteLine ("DisplayName=PT_STRING8,0x3001")
ts.WriteLine ("ConnectionPort=PT_STRING8,0x6601")
ts.WriteLine ("SearchTimeout=PT_STRING8,0x6607")
ts.WriteLine ("MaxEntriesReturned=PT_STRING8,0x6608")
ts.WriteLine ("SearchBase=PT_STRING8,0x6603")
ts.WriteLine ("")
ts.WriteLine ("[Microsoft Outlook Client]")
ts.WriteLine ("SectionGUID=0a0d020000000000c000000000000046")
ts.WriteLine ("FormDirectoryPage=PT_STRING8,0x0270")
ts.WriteLine ("WebServicesLocation=PT_STRING8,0x0271")
ts.WriteLine ("ComposeWithWebServices=PT_BOOLEAN,0x0272")
ts.WriteLine ("PromptWhenUsingWebServices=PT_BOOLEAN,0x0273")
ts.WriteLine ("OpenWithWebServices=PT_BOOLEAN,0x0274")
ts.WriteLine ("CachedExchangeMode=PT_LONG,0x041f")
ts.WriteLine ("CachedExchangeSlowDetect=PT_BOOLEAN,0x0420")
ts.WriteLine ("")
ts.WriteLine ("[Personal Address Book]")
ts.WriteLine ("ServiceName=MSPST AB")
ts.WriteLine ("NameOfPAB=PT_STRING8,0x001e3001")
ts.WriteLine ("Path=PT_STRING8,0x001e6600")
ts.WriteLine ("ShowNamesBy=PT_LONG,0x00036601")
ts.WriteLine ("")
ts.WriteLine ("; ************************************************************************")
ts.WriteLine ("; Section 7 - Mapping for internet account properties.  DO NOT MODIFY.")
ts.WriteLine ("; ************************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[I_Mail]")
ts.WriteLine ("AccountType=POP3")
ts.WriteLine (";--- POP3 Account Settings ---")
ts.WriteLine ("AccountName=PT_UNICODE,0x0002")
ts.WriteLine ("DisplayName=PT_UNICODE,0x000B")
ts.WriteLine ("EmailAddress=PT_UNICODE,0x000C")
ts.WriteLine (";--- POP3 Account Settings ---")
ts.WriteLine ("POP3Server=PT_UNICODE,0x0100")
ts.WriteLine ("POP3UserName=PT_UNICODE,0x0101")
ts.WriteLine ("POP3UseSPA=PT_LONG,0x0108")
ts.WriteLine ("Organization=PT_UNICODE,0x0107")
ts.WriteLine ("ReplyEmailAddress=PT_UNICODE,0x0103")
ts.WriteLine ("POP3Port=PT_LONG,0x0104")
ts.WriteLine ("POP3UseSSL=PT_LONG,0x0105")
ts.WriteLine ("; --- SMTP Account Settings ---")
ts.WriteLine ("SMTPServer=PT_UNICODE,0x0200")
ts.WriteLine ("SMTPUseAuth=PT_LONG,0x0203")
ts.WriteLine ("SMTPAuthMethod=PT_LONG,0x0208")
ts.WriteLine ("SMTPUserName=PT_UNICODE,0x0204")
ts.WriteLine ("SMTPUseSPA=PT_LONG,0x0207")
ts.WriteLine ("ConnectionType=PT_LONG,0x000F")
ts.WriteLine ("ConnectionOID=PT_UNICODE,0x0010")
ts.WriteLine ("SMTPPort=PT_LONG,0x0201")
ts.WriteLine ("SMTPUseSSL=PT_LONG,0x0202")
ts.WriteLine ("ServerTimeOut=PT_LONG,0x0209")
ts.WriteLine ("LeaveOnServer=PT_LONG,0x1000")
ts.WriteLine ("")
ts.WriteLine ("[IMAP_I_Mail]")
ts.WriteLine ("AccountType=IMAP")
ts.WriteLine (";--- IMAP Account Settings ---")
ts.WriteLine ("AccountName=PT_UNICODE,0x0002")
ts.WriteLine ("DisplayName=PT_UNICODE,0x000B")
ts.WriteLine ("EmailAddress=PT_UNICODE,0x000C")
ts.WriteLine (";--- IMAP Account Settings ---")
ts.WriteLine ("IMAPServer=PT_UNICODE,0x0100")
ts.WriteLine ("IMAPUserName=PT_UNICODE,0x0101")
ts.WriteLine ("IMAPUseSPA=PT_LONG,0x0108")
ts.WriteLine ("Organization=PT_UNICODE,0x0107")
ts.WriteLine ("ReplyEmailAddress=PT_UNICODE,0x0103")
ts.WriteLine ("IMAPPort=PT_LONG,0x0104")
ts.WriteLine ("IMAPUseSSL=PT_LONG,0x0105")
ts.WriteLine ("; --- SMTP Account Settings ---")
ts.WriteLine ("SMTPServer=PT_UNICODE,0x0200")
ts.WriteLine ("SMTPUseAuth=PT_LONG,0x0203")
ts.WriteLine ("SMTPAuthMethod=PT_LONG,0x0208")
ts.WriteLine ("SMTPUserName=PT_UNICODE,0x0204")
ts.WriteLine ("SMTPUseSPA=PT_LONG,0x0207")
ts.WriteLine ("ConnectionType=PT_LONG,0x000F")
ts.WriteLine ("ConnectionOID=PT_UNICODE,0x0010")
ts.WriteLine ("SMTPPort=PT_LONG,0x0201")
ts.WriteLine ("SMTPUseSSL=PT_LONG,0x0202")
ts.WriteLine ("ServerTimeOut=PT_LONG,0x0209")
ts.WriteLine ("CheckNewImap=PT_LONG,0x1100")
ts.WriteLine ("RootFolder=PT_UNICODE,0x1101")
ts.WriteLine ("")
ts.WriteLine ("[INET_HTTP]")
ts.WriteLine ("AccountType=HOTMAIL")
ts.WriteLine ("Account=PT_UNICODE,0x0002")
ts.WriteLine ("HttpServer=PT_UNICODE,0x0100")
ts.WriteLine ("UserName=PT_UNICODE,0x0101")
ts.WriteLine ("Organization=PT_UNICODE,0x0107")
ts.WriteLine ("UseSPA=PT_LONG,0x0108")
ts.WriteLine ("TimeOut=PT_LONG,0x0209")
ts.WriteLine ("Reply=PT_UNICODE,0x0103")
ts.WriteLine ("EmailAddress=PT_UNICODE,0x000C")
ts.WriteLine ("FullName=PT_UNICODE,0x000B")
ts.WriteLine ("Connection Type=PT_LONG,0x000F")
ts.WriteLine ("ConnectOID=PT_UNICODE,0x0010")
'close down the file
ts.Close

End Sub

Open in new window


In the code above I have created variables:
accname
filename
email

Basically I need a loop which will loop through each row on the spreadsheet and set these variables and then output a file for each row until the last record is done. It will need to pull column 1 for accname, column 2 for email , and column 3 for filename.

Does this make a bit more sense now??

Cheers,
Alex
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39796985
Sorry, I still don't how this fits together.

You now have three variables. What do you need to do with them?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Author Comment

by:alexcarter404
ID: 39797032
Basically I have a spreadsheet with 100 rows in, I need to loop through row by row and grab the first 3 columns, I then need to set each column to one of the 3 variables. These variables are then used in the WriteLine function.

A text file must be generated for each line of the spreadsheet. So at the end I will have 100 text files which all have unique data in. You can see in section 5 of the write line where the variables are used.
Does this help?

Cheers,
Alex
0
 
LVL 3

Accepted Solution

by:
alexcarter404 earned 0 total points
ID: 39797080
Hi Graham,
I have just sussed it out, please see the code below:

Sub CreateFile()

For I = 1 To 96

Dim fso As New FileSystemObject
Dim ts As TextStream
Dim filename As String
Dim email As String
Dim accname As String
accname = Cells(I, 1)
email = Cells(I, 2)
filename = Cells(I, 3)

'open this file to write to it
Set ts = fso.CreateTextFile("C:\test\" & filename, True)

ts.WriteLine (";Automatically generated PRF file from the Microsoft Office Customization and Installation Wizard")
ts.WriteLine ("")
ts.WriteLine ("; **************************************************************")
ts.WriteLine ("; Section 1 - Profile Defaults")
ts.WriteLine ("; **************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[General]")
ts.WriteLine ("Custom=1")
ts.WriteLine ("ProfileName=Outlook")
ts.WriteLine ("DefaultProfile=Yes")
ts.WriteLine ("OverwriteProfile=Yes")
ts.WriteLine ("ModifyDefaultProfileIfPresent=FALSE")
ts.WriteLine ("")
ts.WriteLine ("; **************************************************************")
ts.WriteLine ("; Section 2 - Services in Profile")
ts.WriteLine ("; **************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[Service List]")
ts.WriteLine (";ServiceX=Microsoft Outlook Client")
ts.WriteLine ("")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("; Section 3 - List of internet accounts")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[Internet Account List]")
ts.WriteLine ("Account1=IMAP_I_Mail")
ts.WriteLine ("")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("; Section 4 - Default values for each service.")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("")
ts.WriteLine (";[ServiceX]")
ts.WriteLine (";FormDirectoryPage=")
ts.WriteLine (";-- The URL of Exchange Web Services Form Directory page used to create Web forms.")
ts.WriteLine (";WebServicesLocation=")
ts.WriteLine (";-- The URL of Exchange Web Services page used to display unknown forms.")
ts.WriteLine (";ComposeWithWebServices=")
ts.WriteLine (";-- Set to TRUE to use Exchange Web Services to compose forms.")
ts.WriteLine (";PromptWhenUsingWebServices=")
ts.WriteLine (";-- Set to TRUE to use Exchange Web Services to display unknown forms.")
ts.WriteLine (";OpenWithWebServices=")
ts.WriteLine (";-- Set to TRUE to prompt user before opening unknown forms when using Exchange Web Services.")
ts.WriteLine ("")
ts.WriteLine ("")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("; Section 5 - Values for each internet account.")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[Account1]")
ts.WriteLine ("UniqueService=No")
ts.WriteLine "AccountName=" & accname
ts.WriteLine ("IMAPServer=outlook.office365.com")
ts.WriteLine ("SMTPServer=smtp.office365.com")
ts.WriteLine "IMAPUserName=" & email
ts.WriteLine "EmailAddress=" & email
ts.WriteLine ("IMAPUseSPA=0")
ts.WriteLine ("DisplayName=")
ts.WriteLine ("ReplyEMailAddress=")
ts.WriteLine ("SMTPUseAuth=1")
ts.WriteLine ("SMTPAuthMethod=0")
ts.WriteLine ("ConnectionType=0")
ts.WriteLine ("RootFolder=")
ts.WriteLine ("ConnectionOID=MyConnection")
ts.WriteLine ("IMAPPort=993")
ts.WriteLine ("IMAPUseSSL=1")
ts.WriteLine ("ServerTimeOut=60")
ts.WriteLine ("SMTPPort=587")
ts.WriteLine ("SMTPUseSSL=1")
ts.WriteLine ("")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("; Section 6 - Mapping for profile properties")
ts.WriteLine (";***************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[Microsoft Exchange Server]")
ts.WriteLine ("ServiceName=MSEMS")
ts.WriteLine ("MDBGUID=5494A1C0297F101BA58708002B2A2517")
ts.WriteLine ("MailboxName=PT_STRING8,0x6607")
ts.WriteLine ("HomeServer=PT_STRING8,0x6608")
ts.WriteLine ("OfflineAddressBookPath=PT_STRING8,0x660E")
ts.WriteLine ("OfflineFolderPath=PT_STRING8,0x6610")
ts.WriteLine ("")
ts.WriteLine ("[Exchange Global Section]")
ts.WriteLine ("SectionGUID=13dbb0c8aa05101a9bb000aa002fc45a")
ts.WriteLine ("MailboxName=PT_STRING8,0x6607")
ts.WriteLine ("HomeServer=PT_STRING8,0x6608")
ts.WriteLine ("RPCoverHTTPflags=PT_LONG,0x6623")
ts.WriteLine ("RPCProxyServer=PT_UNICODE,0x6622")
ts.WriteLine ("RPCProxyPrincipalName=PT_UNICODE,0x6625")
ts.WriteLine ("RPCProxyAuthScheme=PT_LONG,0x6627")
ts.WriteLine ("CachedExchangeConfigFlags=PT_LONG,0x6629")
ts.WriteLine ("")
ts.WriteLine ("[Microsoft Mail]")
ts.WriteLine ("ServiceName=MSFS")
ts.WriteLine ("ServerPath=PT_STRING8,0x6600")
ts.WriteLine ("Mailbox=PT_STRING8,0x6601")
ts.WriteLine ("Password=PT_STRING8,0x67f0")
ts.WriteLine ("RememberPassword=PT_BOOLEAN,0x6606")
ts.WriteLine ("ConnectionType=PT_LONG,0x6603")
ts.WriteLine ("UseSessionLog=PT_BOOLEAN,0x6604")
ts.WriteLine ("SessionLogPath=PT_STRING8,0x6605")
ts.WriteLine ("EnableUpload=PT_BOOLEAN,0x6620")
ts.WriteLine ("EnableDownload=PT_BOOLEAN,0x6621")
ts.WriteLine ("UploadMask=PT_LONG,0x6622")
ts.WriteLine ("NetBiosNotification=PT_BOOLEAN,0x6623")
ts.WriteLine ("NewMailPollInterval=PT_STRING8,0x6624")
ts.WriteLine ("DisplayGalOnly=PT_BOOLEAN,0x6625")
ts.WriteLine ("UseHeadersOnLAN=PT_BOOLEAN,0x6630")
ts.WriteLine ("UseLocalAdressBookOnLAN=PT_BOOLEAN,0x6631")
ts.WriteLine ("UseExternalToHelpDeliverOnLAN=PT_BOOLEAN,0x6632")
ts.WriteLine ("UseHeadersOnRAS=PT_BOOLEAN,0x6640")
ts.WriteLine ("UseLocalAdressBookOnRAS=PT_BOOLEAN,0x6641")
ts.WriteLine ("UseExternalToHelpDeliverOnRAS=PT_BOOLEAN,0x6639")
ts.WriteLine ("ConnectOnStartup=PT_BOOLEAN,0x6642")
ts.WriteLine ("DisconnectAfterRetrieveHeaders=PT_BOOLEAN,0x6643")
ts.WriteLine ("DisconnectAfterRetrieveMail=PT_BOOLEAN,0x6644")
ts.WriteLine ("DisconnectOnExit=PT_BOOLEAN,0x6645")
ts.WriteLine ("DefaultDialupConnectionName=PT_STRING8,0x6646")
ts.WriteLine ("DialupRetryCount=PT_STRING8,0x6648")
ts.WriteLine ("DialupRetryDelay=PT_STRING8,0x6649")
ts.WriteLine ("")
ts.WriteLine ("[Personal Folders]")
ts.WriteLine ("ServiceName=MSPST MS")
ts.WriteLine ("Name=PT_STRING8,0x3001")
ts.WriteLine ("PathToPersonalFolders=PT_STRING8,0x6700 ")
ts.WriteLine ("RememberPassword=PT_BOOLEAN,0x6701")
ts.WriteLine ("EncryptionType=PT_LONG,0x6702")
ts.WriteLine ("Password=PT_STRING8,0x6703")
ts.WriteLine ("")
ts.WriteLine ("[Unicode Personal Folders]")
ts.WriteLine ("ServiceName=MSUPST MS")
ts.WriteLine ("Name=PT_UNICODE,0x3001")
ts.WriteLine ("PathToPersonalFolders=PT_STRING8,0x6700 ")
ts.WriteLine ("RememberPassword=PT_BOOLEAN,0x6701")
ts.WriteLine ("EncryptionType=PT_LONG,0x6702")
ts.WriteLine ("Password=PT_STRING8,0x6703")
ts.WriteLine ("")
ts.WriteLine ("[Outlook Address Book]")
ts.WriteLine ("ServiceName=CONTAB")
ts.WriteLine ("")
ts.WriteLine ("[LDAP Directory]")
ts.WriteLine ("ServiceName=EMABLT")
ts.WriteLine ("ServerName=PT_STRING8,0x6600")
ts.WriteLine ("UserName=PT_STRING8,0x6602")
ts.WriteLine ("UseSSL=PT_BOOLEAN,0x6613")
ts.WriteLine ("UseSPA=PT_BOOLEAN,0x6615")
ts.WriteLine ("DisableVLV=PT_LONG,0x6616")
ts.WriteLine ("DisplayName=PT_STRING8,0x3001")
ts.WriteLine ("ConnectionPort=PT_STRING8,0x6601")
ts.WriteLine ("SearchTimeout=PT_STRING8,0x6607")
ts.WriteLine ("MaxEntriesReturned=PT_STRING8,0x6608")
ts.WriteLine ("SearchBase=PT_STRING8,0x6603")
ts.WriteLine ("")
ts.WriteLine ("[Microsoft Outlook Client]")
ts.WriteLine ("SectionGUID=0a0d020000000000c000000000000046")
ts.WriteLine ("FormDirectoryPage=PT_STRING8,0x0270")
ts.WriteLine ("WebServicesLocation=PT_STRING8,0x0271")
ts.WriteLine ("ComposeWithWebServices=PT_BOOLEAN,0x0272")
ts.WriteLine ("PromptWhenUsingWebServices=PT_BOOLEAN,0x0273")
ts.WriteLine ("OpenWithWebServices=PT_BOOLEAN,0x0274")
ts.WriteLine ("CachedExchangeMode=PT_LONG,0x041f")
ts.WriteLine ("CachedExchangeSlowDetect=PT_BOOLEAN,0x0420")
ts.WriteLine ("")
ts.WriteLine ("[Personal Address Book]")
ts.WriteLine ("ServiceName=MSPST AB")
ts.WriteLine ("NameOfPAB=PT_STRING8,0x001e3001")
ts.WriteLine ("Path=PT_STRING8,0x001e6600")
ts.WriteLine ("ShowNamesBy=PT_LONG,0x00036601")
ts.WriteLine ("")
ts.WriteLine ("; ************************************************************************")
ts.WriteLine ("; Section 7 - Mapping for internet account properties.  DO NOT MODIFY.")
ts.WriteLine ("; ************************************************************************")
ts.WriteLine ("")
ts.WriteLine ("[I_Mail]")
ts.WriteLine ("AccountType=POP3")
ts.WriteLine (";--- POP3 Account Settings ---")
ts.WriteLine ("AccountName=PT_UNICODE,0x0002")
ts.WriteLine ("DisplayName=PT_UNICODE,0x000B")
ts.WriteLine ("EmailAddress=PT_UNICODE,0x000C")
ts.WriteLine (";--- POP3 Account Settings ---")
ts.WriteLine ("POP3Server=PT_UNICODE,0x0100")
ts.WriteLine ("POP3UserName=PT_UNICODE,0x0101")
ts.WriteLine ("POP3UseSPA=PT_LONG,0x0108")
ts.WriteLine ("Organization=PT_UNICODE,0x0107")
ts.WriteLine ("ReplyEmailAddress=PT_UNICODE,0x0103")
ts.WriteLine ("POP3Port=PT_LONG,0x0104")
ts.WriteLine ("POP3UseSSL=PT_LONG,0x0105")
ts.WriteLine ("; --- SMTP Account Settings ---")
ts.WriteLine ("SMTPServer=PT_UNICODE,0x0200")
ts.WriteLine ("SMTPUseAuth=PT_LONG,0x0203")
ts.WriteLine ("SMTPAuthMethod=PT_LONG,0x0208")
ts.WriteLine ("SMTPUserName=PT_UNICODE,0x0204")
ts.WriteLine ("SMTPUseSPA=PT_LONG,0x0207")
ts.WriteLine ("ConnectionType=PT_LONG,0x000F")
ts.WriteLine ("ConnectionOID=PT_UNICODE,0x0010")
ts.WriteLine ("SMTPPort=PT_LONG,0x0201")
ts.WriteLine ("SMTPUseSSL=PT_LONG,0x0202")
ts.WriteLine ("ServerTimeOut=PT_LONG,0x0209")
ts.WriteLine ("LeaveOnServer=PT_LONG,0x1000")
ts.WriteLine ("")
ts.WriteLine ("[IMAP_I_Mail]")
ts.WriteLine ("AccountType=IMAP")
ts.WriteLine (";--- IMAP Account Settings ---")
ts.WriteLine ("AccountName=PT_UNICODE,0x0002")
ts.WriteLine ("DisplayName=PT_UNICODE,0x000B")
ts.WriteLine ("EmailAddress=PT_UNICODE,0x000C")
ts.WriteLine (";--- IMAP Account Settings ---")
ts.WriteLine ("IMAPServer=PT_UNICODE,0x0100")
ts.WriteLine ("IMAPUserName=PT_UNICODE,0x0101")
ts.WriteLine ("IMAPUseSPA=PT_LONG,0x0108")
ts.WriteLine ("Organization=PT_UNICODE,0x0107")
ts.WriteLine ("ReplyEmailAddress=PT_UNICODE,0x0103")
ts.WriteLine ("IMAPPort=PT_LONG,0x0104")
ts.WriteLine ("IMAPUseSSL=PT_LONG,0x0105")
ts.WriteLine ("; --- SMTP Account Settings ---")
ts.WriteLine ("SMTPServer=PT_UNICODE,0x0200")
ts.WriteLine ("SMTPUseAuth=PT_LONG,0x0203")
ts.WriteLine ("SMTPAuthMethod=PT_LONG,0x0208")
ts.WriteLine ("SMTPUserName=PT_UNICODE,0x0204")
ts.WriteLine ("SMTPUseSPA=PT_LONG,0x0207")
ts.WriteLine ("ConnectionType=PT_LONG,0x000F")
ts.WriteLine ("ConnectionOID=PT_UNICODE,0x0010")
ts.WriteLine ("SMTPPort=PT_LONG,0x0201")
ts.WriteLine ("SMTPUseSSL=PT_LONG,0x0202")
ts.WriteLine ("ServerTimeOut=PT_LONG,0x0209")
ts.WriteLine ("CheckNewImap=PT_LONG,0x1100")
ts.WriteLine ("RootFolder=PT_UNICODE,0x1101")
ts.WriteLine ("")
ts.WriteLine ("[INET_HTTP]")
ts.WriteLine ("AccountType=HOTMAIL")
ts.WriteLine ("Account=PT_UNICODE,0x0002")
ts.WriteLine ("HttpServer=PT_UNICODE,0x0100")
ts.WriteLine ("UserName=PT_UNICODE,0x0101")
ts.WriteLine ("Organization=PT_UNICODE,0x0107")
ts.WriteLine ("UseSPA=PT_LONG,0x0108")
ts.WriteLine ("TimeOut=PT_LONG,0x0209")
ts.WriteLine ("Reply=PT_UNICODE,0x0103")
ts.WriteLine ("EmailAddress=PT_UNICODE,0x000C")
ts.WriteLine ("FullName=PT_UNICODE,0x000B")
ts.WriteLine ("Connection Type=PT_LONG,0x000F")
ts.WriteLine ("ConnectOID=PT_UNICODE,0x0010")
'close down the file
ts.Close
Next I
End Sub

Open in new window


Thanks for your help with this.
Cheers,
Alex
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39801553
There is a much simpler way...create a template file with some place holders for your variables.  Read the entire file once into a string variable.  At the start of each row iteration, assign the template string to another string variable and replace the placeholder text with the text from your worksheet.

In the following example template file, you have two placeholders (%accname%, %email%).
;Automatically generated PRF file from the Microsoft Office Customization and Installation Wizard

; **************************************************************
; Section 1 - Profile Defaults
; **************************************************************

[General]
Custom=1
ProfileName=Outlook
DefaultProfile=Yes
OverwriteProfile=Yes
ModifyDefaultProfileIfPresent=FALSE

; **************************************************************
; Section 2 - Services in Profile
; **************************************************************

[Service List]
;ServiceX=Microsoft Outlook Client

;***************************************************************
; Section 3 - List of internet accounts
;***************************************************************

[Internet Account List]
Account1=IMAP_I_Mail

;***************************************************************
; Section 4 - Default values for each service.
;***************************************************************

;[ServiceX]
;FormDirectoryPage=
;-- The URL of Exchange Web Services Form Directory page used to create Web forms.
;WebServicesLocation=
;-- The URL of Exchange Web Services page used to display unknown forms.
;ComposeWithWebServices=
;-- Set to TRUE to use Exchange Web Services to compose forms.
;PromptWhenUsingWebServices=
;-- Set to TRUE to use Exchange Web Services to display unknown forms.
;OpenWithWebServices=
;-- Set to TRUE to prompt user before opening unknown forms when using Exchange Web Services.


;***************************************************************
; Section 5 - Values for each internet account.
;***************************************************************

[Account1]
UniqueService=No
AccountName=%accname%
IMAPServer=outlook.office365.com
SMTPServer=smtp.office365.com
IMAPUserName=%email%
EmailAddress=%email%
IMAPUseSPA=0
DisplayName=
ReplyEMailAddress=
SMTPUseAuth=1
SMTPAuthMethod=0
ConnectionType=0
RootFolder=
ConnectionOID=MyConnection
IMAPPort=993
IMAPUseSSL=1
ServerTimeOut=60
SMTPPort=587
SMTPUseSSL=1

;***************************************************************
; Section 6 - Mapping for profile properties
;***************************************************************

[Microsoft Exchange Server]
ServiceName=MSEMS
MDBGUID=5494A1C0297F101BA58708002B2A2517
MailboxName=PT_STRING8,0x6607
HomeServer=PT_STRING8,0x6608
OfflineAddressBookPath=PT_STRING8,0x660E
OfflineFolderPath=PT_STRING8,0x6610

[Exchange Global Section]
SectionGUID=13dbb0c8aa05101a9bb000aa002fc45a
MailboxName=PT_STRING8,0x6607
HomeServer=PT_STRING8,0x6608
RPCoverHTTPflags=PT_LONG,0x6623
RPCProxyServer=PT_UNICODE,0x6622
RPCProxyPrincipalName=PT_UNICODE,0x6625
RPCProxyAuthScheme=PT_LONG,0x6627
CachedExchangeConfigFlags=PT_LONG,0x6629

[Microsoft Mail]
ServiceName=MSFS
ServerPath=PT_STRING8,0x6600
Mailbox=PT_STRING8,0x6601
Password=PT_STRING8,0x67f0
RememberPassword=PT_BOOLEAN,0x6606
ConnectionType=PT_LONG,0x6603
UseSessionLog=PT_BOOLEAN,0x6604
SessionLogPath=PT_STRING8,0x6605
EnableUpload=PT_BOOLEAN,0x6620
EnableDownload=PT_BOOLEAN,0x6621
UploadMask=PT_LONG,0x6622
NetBiosNotification=PT_BOOLEAN,0x6623
NewMailPollInterval=PT_STRING8,0x6624
DisplayGalOnly=PT_BOOLEAN,0x6625
UseHeadersOnLAN=PT_BOOLEAN,0x6630
UseLocalAdressBookOnLAN=PT_BOOLEAN,0x6631
UseExternalToHelpDeliverOnLAN=PT_BOOLEAN,0x6632
UseHeadersOnRAS=PT_BOOLEAN,0x6640
UseLocalAdressBookOnRAS=PT_BOOLEAN,0x6641
UseExternalToHelpDeliverOnRAS=PT_BOOLEAN,0x6639
ConnectOnStartup=PT_BOOLEAN,0x6642
DisconnectAfterRetrieveHeaders=PT_BOOLEAN,0x6643
DisconnectAfterRetrieveMail=PT_BOOLEAN,0x6644
DisconnectOnExit=PT_BOOLEAN,0x6645
DefaultDialupConnectionName=PT_STRING8,0x6646
DialupRetryCount=PT_STRING8,0x6648
DialupRetryDelay=PT_STRING8,0x6649

[Personal Folders]
ServiceName=MSPST MS
Name=PT_STRING8,0x3001
PathToPersonalFolders=PT_STRING8,0x6700 
RememberPassword=PT_BOOLEAN,0x6701
EncryptionType=PT_LONG,0x6702
Password=PT_STRING8,0x6703

[Unicode Personal Folders]
ServiceName=MSUPST MS
Name=PT_UNICODE,0x3001
PathToPersonalFolders=PT_STRING8,0x6700 
RememberPassword=PT_BOOLEAN,0x6701
EncryptionType=PT_LONG,0x6702
Password=PT_STRING8,0x6703

[Outlook Address Book]
ServiceName=CONTAB

[LDAP Directory]
ServiceName=EMABLT
ServerName=PT_STRING8,0x6600
UserName=PT_STRING8,0x6602
UseSSL=PT_BOOLEAN,0x6613
UseSPA=PT_BOOLEAN,0x6615
DisableVLV=PT_LONG,0x6616
DisplayName=PT_STRING8,0x3001
ConnectionPort=PT_STRING8,0x6601
SearchTimeout=PT_STRING8,0x6607
MaxEntriesReturned=PT_STRING8,0x6608
SearchBase=PT_STRING8,0x6603

[Microsoft Outlook Client]
SectionGUID=0a0d020000000000c000000000000046
FormDirectoryPage=PT_STRING8,0x0270
WebServicesLocation=PT_STRING8,0x0271
ComposeWithWebServices=PT_BOOLEAN,0x0272
PromptWhenUsingWebServices=PT_BOOLEAN,0x0273
OpenWithWebServices=PT_BOOLEAN,0x0274
CachedExchangeMode=PT_LONG,0x041f
CachedExchangeSlowDetect=PT_BOOLEAN,0x0420

[Personal Address Book]
ServiceName=MSPST AB
NameOfPAB=PT_STRING8,0x001e3001
Path=PT_STRING8,0x001e6600
ShowNamesBy=PT_LONG,0x00036601

; ************************************************************************
; Section 7 - Mapping for internet account properties.  DO NOT MODIFY.
; ************************************************************************

[I_Mail]
AccountType=POP3
;--- POP3 Account Settings ---
AccountName=PT_UNICODE,0x0002
DisplayName=PT_UNICODE,0x000B
EmailAddress=PT_UNICODE,0x000C
;--- POP3 Account Settings ---
POP3Server=PT_UNICODE,0x0100
POP3UserName=PT_UNICODE,0x0101
POP3UseSPA=PT_LONG,0x0108
Organization=PT_UNICODE,0x0107
ReplyEmailAddress=PT_UNICODE,0x0103
POP3Port=PT_LONG,0x0104
POP3UseSSL=PT_LONG,0x0105
; --- SMTP Account Settings ---
SMTPServer=PT_UNICODE,0x0200
SMTPUseAuth=PT_LONG,0x0203
SMTPAuthMethod=PT_LONG,0x0208
SMTPUserName=PT_UNICODE,0x0204
SMTPUseSPA=PT_LONG,0x0207
ConnectionType=PT_LONG,0x000F
ConnectionOID=PT_UNICODE,0x0010
SMTPPort=PT_LONG,0x0201
SMTPUseSSL=PT_LONG,0x0202
ServerTimeOut=PT_LONG,0x0209
LeaveOnServer=PT_LONG,0x1000

[IMAP_I_Mail]
AccountType=IMAP
;--- IMAP Account Settings ---
AccountName=PT_UNICODE,0x0002
DisplayName=PT_UNICODE,0x000B
EmailAddress=PT_UNICODE,0x000C
;--- IMAP Account Settings ---
IMAPServer=PT_UNICODE,0x0100
IMAPUserName=PT_UNICODE,0x0101
IMAPUseSPA=PT_LONG,0x0108
Organization=PT_UNICODE,0x0107
ReplyEmailAddress=PT_UNICODE,0x0103
IMAPPort=PT_LONG,0x0104
IMAPUseSSL=PT_LONG,0x0105
; --- SMTP Account Settings ---
SMTPServer=PT_UNICODE,0x0200
SMTPUseAuth=PT_LONG,0x0203
SMTPAuthMethod=PT_LONG,0x0208
SMTPUserName=PT_UNICODE,0x0204
SMTPUseSPA=PT_LONG,0x0207
ConnectionType=PT_LONG,0x000F
ConnectionOID=PT_UNICODE,0x0010
SMTPPort=PT_LONG,0x0201
SMTPUseSSL=PT_LONG,0x0202
ServerTimeOut=PT_LONG,0x0209
CheckNewImap=PT_LONG,0x1100
RootFolder=PT_UNICODE,0x1101

[INET_HTTP]
AccountType=HOTMAIL
Account=PT_UNICODE,0x0002
HttpServer=PT_UNICODE,0x0100
UserName=PT_UNICODE,0x0101
Organization=PT_UNICODE,0x0107
UseSPA=PT_LONG,0x0108
TimeOut=PT_LONG,0x0209
Reply=PT_UNICODE,0x0103
EmailAddress=PT_UNICODE,0x000C
FullName=PT_UNICODE,0x000B
Connection Type=PT_LONG,0x000F
ConnectOID=PT_UNICODE,0x0010

Open in new window

The code is fairly simple.  In this example, the rngCell variable is pointing to the cell with the accname value and the Offset(0, 1) points to the email value.
strOutput = strTemplateText
strOutput = Replace(strOutput, "%accname%", rngCell.Value)
strOutput = Replace(strOutput, "%email%", rngCell.Offset(0, 1).Value)
'write the entire strOutput string to the appropriate filename for this row.

Open in new window

0
 
LVL 3

Author Closing Comment

by:alexcarter404
ID: 39809822
Worked it out for myself.
0
 
LVL 3

Author Comment

by:alexcarter404
ID: 39812093
Cheers aikimark, I generated the files before you got a chance to reply but I will bear this in mind next time I do anything like this.

Cheers,
Alex
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now