Loop and SaveAs Question

[Kerry]

I am trying to save a MS Word Document that loops through an Excel worksheet
and saves the file in a folder. I would like to have a snippet of code that
will place this SaveAs file path to a column in my spread sheet. Does anyone
that this snippet?
Thank you in advance.

 

[dan dungan]

What have you tried?

How does an MS Word Document that loop through an Excel worksheet?

 

[Kerry]

I have a large mail merge file that utilizes an Excel file as a data source.
I am trying to compile several mail merge code sets that I downloaded here
and separate each file into an individual file and when that file is saved
into a folder, I would like to pass the file path to a column in my data
source (Excel Worksheet) so that when I develop an automated email with
attachment I can use the column in the email code to find the file. Does that
make sence?

 

[JLGWhiz]

Hi dan, you can run code from MS Word that will execute in Excel and vice
versa. It is part of working across applications. My question is where is
the spreadsheet located that the file name is to be entered on?

 

[JLGWhiz]

Is there a particular column that you want the data entered into? Do you
want it to overwrite the previous entry or do you want to develop a perpetual
list of saved files? Is there a reason you won't post the code you use to
save the file?

 

[Kerry]

I am just starting to compile some code from this discussion group to develop
a larger code set. Here is what I am doing.

I have 4000 rows of company contact information and a Word Document (90
Pages) that needs the Contact information placed into selected portions to
the document and then saved into an individual file and placed into a folder.
I found some code that will loop through the worksheet and create the folders
based on the Company Name. I then need to create an individual file not a
mail merge and place this file into the Company Name Folder and then
automatically email the file to the Company contacts (several addresses and
emails with several companies). I thought that it would be easiest to find
and tweak a code set her on the discussion board.

I do not have the code yet to create and save the file yet. Still looking
for one that is similar to my needs.

Here are my tasks:
1. Auto create new folders for each record based on CompanyName in Column C
and CompanyCity e.g. C:/Test/CompanyName-CompanyCity.doc. I have code that
will create the folder based on CompanyName only.
2. Create Word Document from .dot and save it to the new folder baced on
CompanyName-CompanyCity.doc. I do not have this code yet.
3. Then auto email this new file to the Company contact based on email
address in the worksheet column.
4. Need to place in the worksheet I think the name file path and the date
file ws emailed to company.

It takes to much time to do this manually. I have done it manually for 25
companies, so it is very time comsuming.

Here is my code to create folders that I found here;
Sub StartHere()

Dim rCell As Range, rRng As Range

Set rRng = Sheet1.Range("C1:C4000")

For Each rCell In rRng.Cells
CreateFolders rCell.Value, "C:\Test"
Next rCell

End Sub

Sub CreateFolders(sSubFolder As String, ByVal sBaseFolder As String)

Dim sTemp As String

'Make sure the base folder is ready to have a sub folder
'tacked on to the end
If Right(sBaseFolder, 1) <> "\" Then
sBaseFolder = sBaseFolder & "\"
End If

'Make sure base folder exists
If Len(Dir(sBaseFolder, vbDirectory))> 0 Then
'Replace illegal characters with an underscore
sTemp = CleanFolderName(sSubFolder)
'See if already exists: Thanks Dave W.
If Len(Dir(sBaseFolder & sTemp)) = 0 Then
'Use MkDir to create the folder
MkDir sBaseFolder & sTemp
End If
End If

End Sub

Function CleanFolderName(ByVal sFolderName As String) As String

Dim i As Long
Dim sTemp As String

For i = 1 To Len(sFolderName)
Select Case Mid$(sFolderName, i, 1)
Case "/", "\", ":", "*", "?", "<", ">", "|"
sTemp = sTemp & "_"
Case Else
sTemp = sTemp & Mid$(sFolderName, i, 1)
End Select
Next i

CleanFolderName = sTemp

End Function
'Source:
http://www.dailydoseofexcel.com/archives/2006/05/24/creating-folders-with-mkdir/

Thanks, Kerry

 

[Kerry]

Here are my Column Headings:
INDEX1 - (1 or 0 based on if file was sent already)
Firm_Name (Multiple Rows)
Address_1
Address_2
City
State
Zip
Country
Phone
Fax
Email
Website
Office_Type - (Main Office or Branch Office)
Prefix
First_Name
Middle_Initial
Last_Name
Suffix
Title
Personal_Email
WebSite
Co_Email
Personal_Email2
FilePath - (Need to insert based on FileCreate)
EmailDt - (Need to insert Date based on automated email date)

 

[dan dungan]

Hi Kerry

I found this discussion started by Terry G from Jan 9, 2003 in this
newsgroup with the subject--Returning a file name in a cell without
the path and tab names

It has some info like:

Private Sub Worksheet_Activate()
Cells(1, 1) = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
End Sub

or

cells(1,1).value = activeworkbook.fullname

I hope this is helpful.

Dan