Microsoft Office Q&A: Ask anything (Word, Excel, PowerPoint, etc.)

Alexander James77 · 2012-03-17T08:48:56+00:00

Walter Jackson48 said: Perhaps this specific formula might prove useful to you: =OFFSET(COLONA;COLUMN()-COLUMN($E$1)+((ROW()-ROW($E$1))*(ROWS(COLONA)/1));0;1;1…

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#1221 · Jan 16, 2014

Kenneth Torres67 said:Yeah, I saw that,

Look, I think you saw it, but you clearly didn't read closely enough or check out the links provided.
You should probably hit up this page. Just click on "+Show all".
If you actually take the time to read, everything will make sense regarding what you have installed—but first things first, you gotta have a legal license for Office 2007.

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#1222 · Jan 16, 2014

Alex Thomas41 said:He wants page numbering in Word 2010 to start at 1 from the 9th page, plus have some custom text sitting in the header...

Look, we've already talked enough about page numbering in Word—whether you want it starting on page three, four, or nine, it doesn't matter, we've covered it.
Bottom line: you gotta use section breaks. One section keeps its own formatting and page numbering per section. Same goes for double page numbering by section.

Alex Thomas41 Newcomer

3 messages
joined Feb 2015

#1223 · Jan 17, 2014

awesome, thanks a ton!

bluecobra2 Newcomer

3 messages
joined Jan 2014

#1224 · Jan 17, 2014

Hey, anyone here actually know their way around graph theory? I need someone to fix up a graph for me—name your price, I'm paying.

David Roberts Newcomer

3 messages
joined Feb 2014

#1225 · Jan 18, 2014

I’ve got a massive mountain of Word 2010 docs here—seriously, too many—and I need to swap out the font color in specific spots across all of them. Is there any way to batch this somehow? I can't deal with opening them one by one... I'll lose my mind.

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#1226 · Jan 18, 2014

Donna Vaughn34 said:and I need to change the font color in specific parts across all of them.

Not exactly much info there. Just Google "Find / Replace with VBA" or "Modify text via VBA"
Here’s a quick example of how you can use VBA to swap out text colors for anything using a specific style across multiple files in one go. The actual text changes, but the color and style stay consistent.

- Changing a specific style's color across multiple docs in the same folder

Walter Jackson48 Active Member

234 messages
joined Feb 2010

#1227 · Jan 19, 2014

Sure, that’s one way to handle it, I suppose. But honestly, if you’re already going through the trouble of opening every single document just to manually color the text, then why stop there? Since the style is identical throughout the entire file anyway, just hit Ctrl+A to select everything and on the selected text, simply wipe the slate clean and change ALL the font characteristics at once. It’s much more efficient.
Of course, everyone has their own little "special" way of doing things, which is fine, I guess.

David Roberts Newcomer

3 messages
joined Feb 2014

#1228 · Jan 19, 2014

I'm a woman. 😉
So, here's the deal—I need more info. Say I've got 100 Word docs sitting there. Every single one is just a page long, where half the text is yellow and the other half is red. Now, I want to go through all those files and swap out the yellow text for green—all at once! I am NOT opening every single document manually, highlighting the yellow parts, and changing them one by one. I know you can swap fonts, but that still requires opening everything up individually. Total nightmare.

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#1229 · Jan 19, 2014

Donna Vaughn34 said:I'm a woman.

My bad
Dropped some VBA code into the tutorial, go ahead and use it.

- how to change text color from yellow to green in dozens of documents using VBA

Daniel Thompson9 Member

12 messages
joined Jan 2013

#1230 · Jan 20, 2014

Hey, does anyone know how I can actually dig up the SQL code for a table that's already been built in Access?
The whole thing was put together using the standard GUI.

I'm working with Access 2010 and an accdb file format.

Ronald Allen47 Newcomer

1 message
joined Jan 2014

#1231 · Jan 21, 2014

So, here’s the situation: a colleague of mine was working away in Excel, putting together all these complex interest calculations and linking them directly to various analytical cards, and then she just... moved on to her next chapter elsewhere, leaving me holding the bag. I recently upgraded my computer, and now it seems like all those vital connections have just vanished into thin air. Every time I try to open the file, I get that annoying little pop-up saying, 'This workbook contains links to other data sources,' but when I go ahead and click 'update,' nothing actually works—instead, the whole sheet just breaks and fills up with those frustrating #NAME? errors.
Does anyone happen to know where I should be looking or what specifically I need to tweak to get those links back in working order?

steelbison6 Newcomer

2 messages
joined Jan 2014

#1232 · Jan 21, 2014

Kyle Johnson2;48390594 said:😕 If you want to get rid of numbers 1 through 10, just head over to Find / Replace, right?

It’s actually a bit more convoluted than that, since I had to identify every single one of those summary digits ranging from 0 to 9 within blocks of 1 to 6 digits...
Anyway, thanks regardless, I managed to find a workaround through other means, though it still quite irritates me that a direct solution for this wasn't readily available 😁

David Roberts Newcomer

3 messages
joined Feb 2014

#1233 · Jan 21, 2014

Kyle Johnson2 said:My bad
I added some VBA code to the tutorial, so go ahead and use that

- how to change text color from yellow to green in dozens of documents using VBA>

Thanks a ton, it worked!!
Just one more thing—my files are all organized into different folders. Is there any way to swap the colors across all those folders at once instead of me having to manually click through every single one?

Here's what my current code looks like:
Option Explicit

Private Sub ChangeColorText()
Selection.Find.ClearFormatting
Selection.Find.Font.Color = RGB(191, 191, 191)
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Font.Color = RGB(173, 173, 173)
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchKashida = False
.MatchDiacritics = False
.MatchAlefhamza = False
.MatchControl = False
.MatchByte = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False end with Selection
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

Sub Start()
Dim file
Dim path as String path =
path = "where the documents are located"
file = Dir(path & "*.docx")
Do While file <> ""
open FileName:=path & file
call ChangeColorText ActiveDocument
Save ActiveDocument
ActiveDocument.Close
file = Dir
Loop

End Sub

Benjamin King2 Active Member

138 messages
joined Apr 2007

#1234 · Jan 21, 2014

Daniel Thompson9 said:Does anyone know how to check out the SQL code for an existing table in Access?
The table was built using the GUI.

I'm working with Access 2010 and an accdb file format.

What do you mean, "SQL code for a table"? A table is just a standard object—it doesn't have any code attached to it. You just use Design View to set up the structure.

Daniel Thompson9 Member

12 messages
joined Jan 2013

#1235 · Jan 21, 2014

Benjamin King2 said:Wait, what do you mean by "SQL code" for a table? A table is just a standard object—it doesn't have its own "code." You just jump into Design View and set up the structure there.

Look, imagine you've already got this table for Authors built out with columns for First Name and Last Name, all filled with actual data.

For instance...
First Name | Last Name
Pero Perić
Đuro Đurić

But here's the thing—what I actually need is this:

CREATE TABLE Author(
FirstName TEXT,
LastName text
)

INSERT INTO Author (FirstName, LastName) VALUES ('Pero', 'Perić')
INSERT INTO Author (FirstName, LastName) VALUES ('Đuro','Đurić')

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#1236 · Jan 21, 2014

Ronald Allen47 said:Does anyone know where I need to go or what I gotta change to get these links working again?

Your colleague linked this file to some source files, so it's pulling data from there. If you're missing one of those files, you're stuck.
Check the formulas to see exactly which files we're talking about.
Hit the edit link if the update doesn't work. Just set a new path to the source files.

Benjamin King2 Active Member

138 messages
joined Apr 2007

#1237 · Jan 21, 2014

Daniel Thompson9 said:So, you've already got this fully functional table for Authors—complete with First Name and Last Name columns and all the data sitting right there.

For instance:
First Name Last Name
Pero Perić
Đuro Đurić

But what I actually need is this:

CREATE TABLE Author(
FirstName TEXT,
LastName text
)

INSERT INTO Author (FirstName, LastName) VALUES ('Pero', 'Perić')
INSERT INTO Author (FirstName, LastName) VALUES ('Đuro','Đurić')

The thing is, Access doesn't even use that syntax for creating tables: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

I mean, what’s the actual point of what you just wrote? You have a table filled with those specific details, and now you want to create an identical table with the exact same data? 😕

Jack Hayes Newcomer

7 messages
joined Nov 2008

#1238 · Jan 22, 2014

Daniel Thompson9 said:So, you've already got an Author table set up with First Name and Last Name columns and all the data filled in.

For example:
First Name Last Name
Pero Perić
Đuro Đurić

What I actually need is this:

CREATE TABLE Author(
FirstName TEXT,
LastName text
)

INSERT INTO Author (FirstName, LastName) VALUES ('Pero', 'Perić')
INSERT INTO Author (FirstName, LastName) VALUES ('Đuro','Đurić')

In Microsoft Access, you can check the table name, column names, data types, keys... and use that info to build your CREATE TABLE code.
As for the INSERT part, just grab a CSV file with delimited data, like this:
FIRSTNAME;LASTNAME
Pero;Perić
Đuro;Đurić
...
Then, you just run a loop to read the file line by line and perform the inserts. Personally, I usually use that first row as my column headers, just like in my example above.

Daniel Thompson9 Member

12 messages
joined Jan 2013

#1239 · Jan 22, 2014

Benjamin King2 said:Access doesn't actually support that kind of syntax for building tables: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

I mean, what's even the point of what you just typed? You’ve already got a table full of all that data, so why on earth would you want to recreate the exact same table with the exact same info? 😕

Look, I get that the syntax isn't a perfect match—I was just throwing that out there as an example so you guys could see the end goal I'm aiming for. 🙂

So here's the deal: some students sent me this database they built, right? It's got maybe 30 different tables, all tangled up in these complex relationships, and there's tons of data in there—some tables have over 300 rows. Basically, they built the whole thing using the GUI, thinking they could just hand over the .accdb file, let the professor open it up, look around, and call it a day. But no! The professor wants to see the actual code—all those commands used to build the tables, set up the relations, and dump the data. Naturally, these kids aren't about to sit there and manually type out every single command to rebuild it from scratch. They just need a way to export everything into a .txt file or some kind of .sql script where all those commands are visible.

EDIT: Okay, just found out Access doesn't actually have a feature for that, so thanks to you and -Y- for looking into it.

Benjamin King2 Active Member

138 messages
joined Apr 2007

#1240 · Jan 22, 2014

Look, you can just fire up the Database Documenter and see how much ground you can actually cover with it. Honestly, the professor is a total jerk. 😁 There’s a graphical interface included—which exists specifically so you don't have to waste your life writing endless lines of code for things you could finish in three clicks of a mouse. And if you need to deal with relations, there's an Object Dependency tool that lets you visualize exactly how everything is linked together.

Microsoft Office Q&A: Ask anything (Word, Excel, PowerPoint, etc.)

🔗 Similar threads