Apply VBA function in Word doc and create Excel file.
Hi Experts,
I have word documents with some lines of text and some table lines.
I would like to apply the following VBA function (taken from Access app) to each line.
How can I accomplish it?
Attaching example
.Untitled.png
I have word documents with some lines of text and some table lines.
I would like to apply the following VBA function (taken from Access app) to each line.
Function FixNameField(s As String) As String
s = LCase(s)
s = StrConv(s, vbProperCase)
s = Replace(s, " ", "_")
FixNameField = s
End FunctionHow can I accomplish it?
Attaching example
.Untitled.png
ASKER
How about using VBA code inside the word document.
I would believe its just a matter of looping thru all rows and applying the function...
I would believe its just a matter of looping thru all rows and applying the function...
Well Word+VBA is a perfectly viable option but i think it should be most useful to stick to the Access controlling ecosystem.
ASKER
At the moment I have nothing in Access except for the code I posted above.
Would prefer to keep this as simple as possible, rather copy paste code in Word VBA editor than have applications controlling each other, the less code involved the better...
Would prefer to keep this as simple as possible, rather copy paste code in Word VBA editor than have applications controlling each other, the less code involved the better...
ASKER
Hi Experts,
Any updates?
Maybe for second part we can use the following.
https://support.microsoft.com/en-us/office/transpose-data-from-rows-to-columns-or-vice-versa-in-excel-for-mac-9c16dd55-ed1a-4aa2-8b74-b1b9211e2ede
Any updates?
Maybe for second part we can use the following.
https://support.microsoft.com/en-us/office/transpose-data-from-rows-to-columns-or-vice-versa-in-excel-for-mac-9c16dd55-ed1a-4aa2-8b74-b1b9211e2ede
Just to make sure I'm on the same page, are you looking for something along the lines of the attached?
29210139 Example.xlsx
Any reason you're replacing blanks with underscores (which IMO makes it harder to read)?
29210139 Example.xlsx
Any reason you're replacing blanks with underscores (which IMO makes it harder to read)?
ASKER
looking for something along the lines of the attached?Yes.
Any reason you're replacing blanks with underscoresMy final goal is to import the Excel sheet into Caspio as a table and therefore need valid column names.
Got it. In that case, should you also be replacing ampersands and slashes?
ASKER
I use ampersand when concatenating words in a field, but right slashes should be removed or maybe replaced with something else...
Is an ampersand valid in a Caspio column name? If not, they will need to be removed as well.
Will try to whip something up in a bit.
Will try to whip something up in a bit.
ASKER
It is, anyways the code snipped above can be modified later.
Thanks
Thanks
Try the macro SetupHeaders in the attached.
CreateExcelHeaders.xlsm
It will ask you for the location of your Word doc, and will take the first column of the first table in the doc and create headers in the spreadsheet (after stripping out non-printable characters, and converting spaces, ampersands and slashes).
CreateExcelHeaders.xlsm
It will ask you for the location of your Word doc, and will take the first column of the first table in the doc and create headers in the spreadsheet (after stripping out non-printable characters, and converting spaces, ampersands and slashes).
ASKER
I will test it tom and keep you posted.
Thanks
Untitled.png
and will take the first column of the first table in the docWondering will this also handle simple lines w/o being in a table, like the attached?
Thanks
Untitled.png
Right now, the code only works with a table (like the sample you provided in your post).
It could be modified to work with simple lines, but the code needs to know what to look for to determine what should be included as the column headers (for example, it could look for the character in front of the lines).
It could be modified to work with simple lines, but the code needs to know what to look for to determine what should be included as the column headers (for example, it could look for the character in front of the lines).
ASKER
How about just have one column per line, and I will do manual adjustments later if needed.
So for example the attached would also work.
Thanks
Untitled.png
So for example the attached would also work.
Thanks
Untitled.png
The column headings would be the words following the square character on each line?
What is the ASCII value of that character?
What is the ASCII value of that character?
@bfuchs
If you post a version in MS Word format, I can try to modify the above code.
If you post a version in MS Word format, I can try to modify the above code.
ASKER
Hi,
Here are some, hope this covers all, if not may start new Q for the others dont worry-:)
Thanks
2-BACKUP CAREGIVER AGREEMENT-ADULT - WG COPY.zip
Here are some, hope this covers all, if not may start new Q for the others dont worry-:)
Thanks
2-BACKUP CAREGIVER AGREEMENT-ADULT - WG COPY.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent Sam!
Can you show me how to apply the function above to fix the column names?
Thanks
Can you show me how to apply the function above to fix the column names?
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
You are most welcome.
To give you the outline...you will treat Word as an object...scan the document and apply whatever transformations you want...the trouble is to identify the correct "spot" as Word is quite complex as Document/Object