Solved

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

Posted on 2014-01-20
10
403 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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