Free Microsoft Excel 2013 Quick Reference

How to reformat my table in excel Results

I've a table that looks like this:

ID Month Part Qty 1 May xx 2 1 May yy 1 2 May xx 1 2 May zz 2 3 June yy 1 3 June zz 1

that I'd like to look like this:

ID Month Part1 Qty Part2 Qty Part3 Qty 1 May xx 2 yy 1 2 May xx 1 n/a 0 zz 2 3 June n/a 0 yy 1 zz 1

How do I go about doing this? There may be n-parts so I'd need to add a new column for each one.

Thanks in advance.

How to use Visual Basic for Applications (VBA) to change UserForms in Excel

How to Programmatically Manipulate a UserForm

How to Programmatically Create UserForms in Visual Basic for Applications

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
β€’ How Can I Import a Fixed-Width Data File into Microsoft Excel?
β€’ How Can I Sort a Spreadsheet By Cell Color?
β€’ How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
β€’ How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
β€’ How Can I Read Custom Summary Information Properties for an Excel File?
β€’ How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
β€’ How Can I Import Multiple Worksheets into an Access Database?
β€’ How Can I Search For Values in an Excel Worksheet?
β€’ How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
β€’ How Can I Determine the Value of the Last Cell in an Excel Range?
β€’ How Can I Convert an Excel Serial Number to a Date and Then Back?
β€’ How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
β€’ How Can I Copy Data from Excel to PowerPoint?
β€’ How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
β€’ How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
β€’ How Can I Update and Then Break All the Links in an Excel Spreadsheet?
β€’ How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
β€’ How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
β€’ How Can I Add a Background Picture to an Excel Worksheet?
β€’ How Can I Replace Numeric Values in an Excel Spreadsheet?
β€’ How Can I Copy Data From One Spreadsheet to Another?
β€’ How Can I Replace Text in an Excel Spreadsheet?
β€’ How Can I Set the Print Area in an Excel Spreadsheet?
β€’ How Can I Delete Specified Rows in an Excel Spreadsheet?
β€’ How Can I Color Every Other Row in an Excel Spreadsheet?
β€’ How Can I Copy Selected Columns From a CSV File to an Excel File?
β€’ How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
β€’ How Can I Export an HTA Table to Excel?
β€’ How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
β€’ How Can I Assign a Background Color to Cells in a Spreadsheet and Then β€œSum” Those Cells?
β€’ How Can I Sort a Row in an Excel Spreadsheet?
β€’ How Can I Remove the Password When Opening an Excel Spreadsheet?
β€’ How Can I Import an Excel Spreadsheet Into an Access Database?
β€’ How Can I Save a Table in an Access Database as a Spreadsheet?
β€’ How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
β€’ How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
β€’ How Can I Configure Excel to Autosave Every 5 Minutes?
β€’ How Can I Save an Excel Chart as a Picture?
β€’ How Can I Change the Font Color in Excel If a Specified Condition is Met?
β€’ How Can I Convert an Excel Spreadsheet to XML?
β€’ How Can I Set the Default File Path in Excel to a User’s Home Directory?
β€’ How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
β€’ How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
β€’ How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
β€’ How Can I Add a Total Row to an Excel Spreadsheet?
β€’ How Can I Create a Custom Date Format in Microsoft Excel?
β€’ How Can I Keep the Screen From Updating While Running an Excel Script?
β€’ How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
β€’ How Can I Determine the Background Color of a Spreadsheet Cell?
β€’ How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
β€’ How Can I Copy a Worksheet to a New Spreadsheet?
β€’ How Can I Use Windows PowerShell to Automate Microsoft Excel?
β€’ How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
β€’ How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
β€’ How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
β€’ How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
β€’ How Can I Create a New Excel Spreadsheet at Midnight Each Night?
β€’ How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
β€’ How Can I Convert a Number to a Date in Excel?
β€’ How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
β€’ How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
β€’ How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
β€’ How Can I Determine the Last Row in an Excel Spreadsheet?
β€’ How Can I Remove All the Formatting from an Excel Spreadsheet?
β€’ How Can I Change the Footer in an Excel Spreadsheet?
β€’ How Can I Add Additional Worksheets to an Excel Workbook?
β€’ How Can I Sort Worksheets in a Workbook?
β€’ How Can I Import a Fixed-Width File into Microsoft Excel?
β€’ How Can I Add a Hyperlink to an Excel Spreadsheet?
β€’ How Can I Build an Array from a Column of Data in Excel?
β€’ How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
β€’ How Can I Center Text in an Excel Cell?
β€’ How Can I Change the Background Color of a Cell Depending on the Date?
β€’ How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
β€’ How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
β€’ How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
β€’ How Can I Insert a Column into a Spreadsheet?
β€’ How Can I Save a Single Excel Worksheet to a CSV File?
β€’ How Can I Password-Protect an Excel Spreadsheet?
β€’ How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
β€’ How Can I Randomly Assign a Font to Characters in a Word Document?
β€’ How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
β€’ How Can I Count the Number of Sentences and Paragraphs in a Word Document?
β€’ How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
β€’ How Can I Tally All the Items in a Microsoft Word Document?
β€’ How Can I Reset the Revision Number of a Word Document to 1?
β€’ How Can I Retrieve Field Values in a Microsoft Word Document?
β€’ How Can I Change the Paragraph Case in Microsoft Word?
β€’ How Can I Insert a Manual Line Break into a Microsoft Word Document?
β€’ How Can I Search For and Modify Formatted Text in a Word Document?
β€’ How Can I Search For Red Text in a Microsoft Word Document?
β€’ How Can I Change the Default File Save Format in Microsoft Word?
β€’ How Can I Put the File Name in the Footer of a Microsoft Word Document?
β€’ How Can I Add a Page X of Y Footer to a Microsoft Word Document?
β€’ How Can I Save Each Paragraph in a Word Document as a Database Record?
β€’ How Can I Replace a Specified Font in a Microsoft Word Document?
β€’ How Can I Apply a Theme to a Microsoft Word Document?
β€’ How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
β€’ How Can I Insert a Date Field in Word?
β€’ How Can I Add a New Item to the Microsoft Word AutoCorrect List?
β€’ How Can I Clear All the Formatting From a Microsoft Word Document?
β€’ How Can I Change the Default Highlight Color for a Microsoft Word Document?
β€’ How Can I Run a Macro After Opening Up Word?
β€’ How Can I Center-Align a Picture in a Word Document?
β€’ How Can I Change an Existing Hyperlink in a Microsoft Word Document?
β€’ How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
β€’ How Can I Insert Text Into an Existing Microsoft Word Bookmark?
β€’ How Can I Get a Total Page Count for All the Word Documents in a Folder?
β€’ How Can I Set the Document Orientation in Microsoft Word to Landscape?
β€’ How Can I Replace Text in a Microsoft Word Document?
β€’ How Can I Search For (and Reformat) Highlighted Text in a Word Document?
β€’ How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
β€’ How Can I Get a List of Available Metadata for Microsoft Office Documents?
β€’ How Can I Add Multiple Tables to a Word Document?
β€’ How Can I Search a Word Document for All the Words in Double Brackets?
β€’ How Can I Add Centered Page Numbers to the Footer of a Word Document?
β€’ How Can I Create a Table and Fill the First Column With a Range of Dates?
β€’ How Can I Rename a Word Document Using the First Three Characters in That Document?
β€’ How Can I Search for and Highlight Words in a Microsoft Word Document?
β€’ How Can I Right-Align a Single Column in a Word Table?
β€’ How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
β€’ How Can I Change the Font Name and Size for an Entire Word Document?
β€’ How Can I Set Word’s Revision View Mode to Final?
β€’ How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
β€’ How Can I Set Word’s Default File Location to be the User’s Home Directory?
β€’ How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
β€’ How Can I Change File Locations for Microsoft Word?
β€’ How Can I Insert a Symbol into a Word Document?
β€’ How Can I Hide a Specific Toolbar in Microsoft Word?
β€’ How Can I Extract Word Paragraphs That Use a Specific Style?
β€’ How Can I Insert Files into a Word Document?
β€’ How Can I Convert 1,000 .RTF Files to Word Documents?
β€’ How Can I Add a Hyperlink to a Word Document?
β€’ How Can I Put the User Name into the Footer of a Microsoft Word Document?
β€’ How Can I Determine Which Version of Word is Installed on a Computer?
β€’ How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
β€’ How Can I Import Multiple Worksheets into an Access Database?
β€’ How Can I Add a Record to a Database Using Windows PowerShell?
β€’ How Can I Delete a Set of Records From an Access Database?
β€’ How Can I Be Notified if an Access Database File Exceeds a Specific Size?
β€’ How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
β€’ How Can I List All the Fields and Data Types in an Access Database?
β€’ How Can I Append a Value to a Specified Field For Each Record in a Database?
β€’ How Can I Add the Contents of a Group of Text Files to an Access Database?
β€’ How Can I Import a .CSV File into an Access Database?
β€’ How Can I List All the Access Database Files on a Computer?
β€’ How Can I Print a Microsoft Access Report?
β€’ How Can I Compact an Access Database?
β€’ How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
β€’ How Can I List All the Tables in an Access Database?
β€’ How Can I Determine Which Version of Access was Used to Create a Database?
β€’ How Can I Delete a Table from a Jet Database?

Microsoft Outlook
β€’ How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
β€’ How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
β€’ How Can I Set a Reminder on All My Outlook Appointments?
β€’ How Can I Schedule an All-Day Event in Microsoft Outlook?
β€’ How Can I Run a Script Any Time Outlook is Started?
β€’ How Can I Determine the Age, in Years, of an Outlook Contact?
β€’ How Can I Save the Attachments for All My New Outlook Messages?
β€’ How Can I Convert an Extension Number to an Actual Phone Number?
β€’ How Can I Create a New Folder in Microsoft Outlook?
β€’ How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
β€’ How Can I Get a List of Appointments for a Specific Month?
β€’ How Can I Save Emails That Are More Than One Month Old?
β€’ How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
β€’ How Can I Save All My Contacts as VCards?
β€’ How Can I Delete Unread Emails That Are More Than 6 Months Old?
β€’ How Can I Randomly Select an Email From an Outlook Mail Folder?
β€’ How Can I List All the Meetings Scheduled By a Specified Person?
β€’ How Can I Filter Outlook Messages By Email Address?
β€’ How Can I List All the Members of a Microsoft Outlook Distribution List?
β€’ How Can I Determine the Follow-Up Status of Outlook Emails?
β€’ How Can I Tell If Any of My Contacts Have a Birthday This Month?
β€’ How Can I Delete All the Messages in My Sent Items Folder?
β€’ How Can I Start Outlook If It Isn’t Already Running?
β€’ How Can I Connect to the Junk Mail Folder in Outlook?
β€’ How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
β€’ How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
β€’ How Can I Delete All the Email Sent From a Specific Person?
β€’ How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
β€’ How Can I Get Total Size and Number of Items in an Outlook Folder?
β€’ How Can I Convert an Outlook Email Message into a Text File?
β€’ How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
β€’ How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
β€’ How Can I Copy Data from Excel to PowerPoint?
β€’ How Can I Apply a New Template to a PowerPoint Presentation?
β€’ How Can I Configure PowerPoint to Print Handouts Instead of Slides?
β€’ How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
β€’ How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
β€’ How Can I Change the User Information in Microsoft Office?

In my Access 2003 application, I have a query that generates a large data set
and stores it in a temporary table. I also have an Excel spreadsheet that is
linked to this table. I am using Excel to view the data in a variety of
ways, and to format the font and background colors of the cells. I am also
using this format because I need to post this information to a portal so
others can download it.

My problem is this. Because of the formatting in the spreadsheet (column
headers, conditional formatting, ...) I cannot use the Access
Transferspreadsheet method to get my data into Excel. Because of this, I
have used Excel to link to the data table.

I've figured out how to open the Excel file (from Access), but cannot figure
out how to force it to refresh the link. The other problem is that when I
refresh the data manually in this spreadsheet, it changes the column widths
in the spreadsheet. I figure I can record a macro to reformat these columns
(how do I force an Excel macro to fire from within Access), but was wondering
whether there is a way to prevent this.

--
Email address is not valid.
Please reply to newsgroup only.

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
• How Can I Import a Fixed-Width Data File into Microsoft Excel?
• How Can I Sort a Spreadsheet By Cell Color?
• How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
• How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
• How Can I Read Custom Summary Information Properties for an Excel File?
• How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Search For Values in an Excel Worksheet?
• How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
• How Can I Determine the Value of the Last Cell in an Excel Range?
• How Can I Convert an Excel Serial Number to a Date and Then Back?
• How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
• How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
• How Can I Update and Then Break All the Links in an Excel Spreadsheet?
• How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Add a Background Picture to an Excel Worksheet?
• How Can I Replace Numeric Values in an Excel Spreadsheet?
• How Can I Copy Data From One Spreadsheet to Another?
• How Can I Replace Text in an Excel Spreadsheet?
• How Can I Set the Print Area in an Excel Spreadsheet?
• How Can I Delete Specified Rows in an Excel Spreadsheet?
• How Can I Color Every Other Row in an Excel Spreadsheet?
• How Can I Copy Selected Columns From a CSV File to an Excel File?
• How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
• How Can I Export an HTA Table to Excel?
• How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
• How Can I Assign a Background Color to Cells in a Spreadsheet and Then “Sum” Those Cells?
• How Can I Sort a Row in an Excel Spreadsheet?
• How Can I Remove the Password When Opening an Excel Spreadsheet?
• How Can I Import an Excel Spreadsheet Into an Access Database?
• How Can I Save a Table in an Access Database as a Spreadsheet?
• How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
• How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
• How Can I Configure Excel to Autosave Every 5 Minutes?
• How Can I Save an Excel Chart as a Picture?
• How Can I Change the Font Color in Excel If a Specified Condition is Met?
• How Can I Convert an Excel Spreadsheet to XML?
• How Can I Set the Default File Path in Excel to a User’s Home Directory?
• How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
• How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
• How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
• How Can I Add a Total Row to an Excel Spreadsheet?
• How Can I Create a Custom Date Format in Microsoft Excel?
• How Can I Keep the Screen From Updating While Running an Excel Script?
• How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
• How Can I Determine the Background Color of a Spreadsheet Cell?
• How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
• How Can I Copy a Worksheet to a New Spreadsheet?
• How Can I Use Windows PowerShell to Automate Microsoft Excel?
• How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
• How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
• How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
• How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
• How Can I Create a New Excel Spreadsheet at Midnight Each Night?
• How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
• How Can I Convert a Number to a Date in Excel?
• How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
• How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
• How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
• How Can I Determine the Last Row in an Excel Spreadsheet?
• How Can I Remove All the Formatting from an Excel Spreadsheet?
• How Can I Change the Footer in an Excel Spreadsheet?
• How Can I Add Additional Worksheets to an Excel Workbook?
• How Can I Sort Worksheets in a Workbook?
• How Can I Import a Fixed-Width File into Microsoft Excel?
• How Can I Add a Hyperlink to an Excel Spreadsheet?
• How Can I Build an Array from a Column of Data in Excel?
• How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
• How Can I Center Text in an Excel Cell?
• How Can I Change the Background Color of a Cell Depending on the Date?
• How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
• How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
• How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
• How Can I Insert a Column into a Spreadsheet?
• How Can I Save a Single Excel Worksheet to a CSV File?
• How Can I Password-Protect an Excel Spreadsheet?
• How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
• How Can I Randomly Assign a Font to Characters in a Word Document?
• How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
• How Can I Count the Number of Sentences and Paragraphs in a Word Document?
• How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
• How Can I Tally All the Items in a Microsoft Word Document?
• How Can I Reset the Revision Number of a Word Document to 1?
• How Can I Retrieve Field Values in a Microsoft Word Document?
• How Can I Change the Paragraph Case in Microsoft Word?
• How Can I Insert a Manual Line Break into a Microsoft Word Document?
• How Can I Search For and Modify Formatted Text in a Word Document?
• How Can I Search For Red Text in a Microsoft Word Document?
• How Can I Change the Default File Save Format in Microsoft Word?
• How Can I Put the File Name in the Footer of a Microsoft Word Document?
• How Can I Add a Page X of Y Footer to a Microsoft Word Document?
• How Can I Save Each Paragraph in a Word Document as a Database Record?
• How Can I Replace a Specified Font in a Microsoft Word Document?
• How Can I Apply a Theme to a Microsoft Word Document?
• How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
• How Can I Insert a Date Field in Word?
• How Can I Add a New Item to the Microsoft Word AutoCorrect List?
• How Can I Clear All the Formatting From a Microsoft Word Document?
• How Can I Change the Default Highlight Color for a Microsoft Word Document?
• How Can I Run a Macro After Opening Up Word?
• How Can I Center-Align a Picture in a Word Document?
• How Can I Change an Existing Hyperlink in a Microsoft Word Document?
• How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
• How Can I Insert Text Into an Existing Microsoft Word Bookmark?
• How Can I Get a Total Page Count for All the Word Documents in a Folder?
• How Can I Set the Document Orientation in Microsoft Word to Landscape?
• How Can I Replace Text in a Microsoft Word Document?
• How Can I Search For (and Reformat) Highlighted Text in a Word Document?
• How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
• How Can I Get a List of Available Metadata for Microsoft Office Documents?
• How Can I Add Multiple Tables to a Word Document?
• How Can I Search a Word Document for All the Words in Double Brackets?
• How Can I Add Centered Page Numbers to the Footer of a Word Document?
• How Can I Create a Table and Fill the First Column With a Range of Dates?
• How Can I Rename a Word Document Using the First Three Characters in That Document?
• How Can I Search for and Highlight Words in a Microsoft Word Document?
• How Can I Right-Align a Single Column in a Word Table?
• How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
• How Can I Change the Font Name and Size for an Entire Word Document?
• How Can I Set Word’s Revision View Mode to Final?
• How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
• How Can I Set Word’s Default File Location to be the User’s Home Directory?
• How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
• How Can I Change File Locations for Microsoft Word?
• How Can I Insert a Symbol into a Word Document?
• How Can I Hide a Specific Toolbar in Microsoft Word?
• How Can I Extract Word Paragraphs That Use a Specific Style?
• How Can I Insert Files into a Word Document?
• How Can I Convert 1,000 .RTF Files to Word Documents?
• How Can I Add a Hyperlink to a Word Document?
• How Can I Put the User Name into the Footer of a Microsoft Word Document?
• How Can I Determine Which Version of Word is Installed on a Computer?
• How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Add a Record to a Database Using Windows PowerShell?
• How Can I Delete a Set of Records From an Access Database?
• How Can I Be Notified if an Access Database File Exceeds a Specific Size?
• How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
• How Can I List All the Fields and Data Types in an Access Database?
• How Can I Append a Value to a Specified Field For Each Record in a Database?
• How Can I Add the Contents of a Group of Text Files to an Access Database?
• How Can I Import a .CSV File into an Access Database?
• How Can I List All the Access Database Files on a Computer?
• How Can I Print a Microsoft Access Report?
• How Can I Compact an Access Database?
• How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
• How Can I List All the Tables in an Access Database?
• How Can I Determine Which Version of Access was Used to Create a Database?
• How Can I Delete a Table from a Jet Database?

Microsoft Outlook
• How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
• How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
• How Can I Set a Reminder on All My Outlook Appointments?
• How Can I Schedule an All-Day Event in Microsoft Outlook?
• How Can I Run a Script Any Time Outlook is Started?
• How Can I Determine the Age, in Years, of an Outlook Contact?
• How Can I Save the Attachments for All My New Outlook Messages?
• How Can I Convert an Extension Number to an Actual Phone Number?
• How Can I Create a New Folder in Microsoft Outlook?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Get a List of Appointments for a Specific Month?
• How Can I Save Emails That Are More Than One Month Old?
• How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
• How Can I Save All My Contacts as VCards?
• How Can I Delete Unread Emails That Are More Than 6 Months Old?
• How Can I Randomly Select an Email From an Outlook Mail Folder?
• How Can I List All the Meetings Scheduled By a Specified Person?
• How Can I Filter Outlook Messages By Email Address?
• How Can I List All the Members of a Microsoft Outlook Distribution List?
• How Can I Determine the Follow-Up Status of Outlook Emails?
• How Can I Tell If Any of My Contacts Have a Birthday This Month?
• How Can I Delete All the Messages in My Sent Items Folder?
• How Can I Start Outlook If It Isn’t Already Running?
• How Can I Connect to the Junk Mail Folder in Outlook?
• How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
• How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
• How Can I Delete All the Email Sent From a Specific Person?
• How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
• How Can I Get Total Size and Number of Items in an Outlook Folder?
• How Can I Convert an Outlook Email Message into a Text File?
• How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
• How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Apply a New Template to a PowerPoint Presentation?
• How Can I Configure PowerPoint to Print Handouts Instead of Slides?
• How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
• How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
• How Can I Change the User Information in Microsoft Office?

My data has various dates in column A, which I group into months using the formula "=DATE(YEAR(A2),MONTH(A2),1)" in column B. Then I format column B to show the results in the format mmm-yy. So far so good.

I then use a pivot table to count the number of entries for each month and I have formatted the date column the same way. But every time I refresh the data (which I need to do frequently as data floods in from external sources), the format of the pivot table reverts to dd/mm/yyyy and I have to go and manually reformat it again.

I've checked all the options I can think of, but cannot find any way to keep the format as I have set it. Does anybody know of a way?

I'm using Excel 2003 on Windows 2000 Professional.

Thnaks in advance for help.

Example.xlsxYour
help with the following challenge I am having would be really appreciated. I
have tried to find a solution but have finally admitted defeat – this is way
out of my depth right now.

Itis probably best to look at the attached example sheet I have uploaded to getsome context of the problem. The below description will make more sense withthe example to hand.

I amprovided an excel sheet on a monthly basis. As shown as the first dataset inexample sheet.

Incolumn A are the different Groups. As an example I have given A-G but thiscould go into the hundreds.

Then,running across the sheet I have several different audiences. Each group hasnumerous audiences. For the sake of example I have given seven audiencesalthough again there could be hundreds of Audiences.

Withineach audience the data returned is consistent - there are seven numeric fieldsTotal and Attend A to F.

Thefigures I have entered for each Audience is simply test data. Althoughconsistent in the sheet in real life these figures will all be different.

I amunable to change the way which the data is supplied to me. I therefore need tocreate a method in excel of reformatting the data into a way that I need it.

Thesecond table shows how I would like to reformat the data.

ColumnA will show the GROUP of which there will always be repeated as many times asthere are audiences.

ColumnB will show the Audience which in case of example will be 1-7 but could raisesignificantly.

ThenC-I will show the actual data fields for that audience.

Anyhelp would be really appreciated.

Thanksin advance

Tom

Re EXCEL 2002: When I create a new pivot table on a new sheet I must reformat
the column widths to make them smaller and also center the data. I then save.
However, when I later do a refresh (!) in the data, my pivot table formats to
the default column widths and I have to reformat again. Can you offer
suggestions on how I can keep the pivot table in the changed format?

Hi,

I am trying to copy data from an excel sheet into a word document, and would like to learn how i could write a macro to save me time by not having to type it in manually.

Each page of the word document is split into a 4 row x 2 column table, and each row of the spreadsheet contains data to be input into 1 of these table's cells. There are 121 rows of data and 4 variables/columns.

Furthermore, the data extracted from the table is to be reformatted, so that it takes a vertical shape inside the cell.
ie. <Info A>
<Info B>
<Info C>
<Info D>

If anyone could assist me or set me in the right direction it would be greatly appreciated, as this is quite far out of my depth.

I have attached the files in case the above doesn't make much sense.

Thx & rgrds,

D

I'm pretty clever with Regular Expressions and can generally reformat
just about any Excel data by dumping it into a program like EditPlus
and running a ton of RegExp powered Find & Replace operations. However,
this latest spreadsheet has me pulling my hair out.

Here's what I have

a b c d e f
1 job 1 doc # job 2 doc # ... ...
2 sop 1 sop001 sop 3 sop003 ... ...
3 sop2 sop002 sop 4 sop004 ... ...
4 sop 5 sop005
5 sop 6 sop006

Cells a1, c1, e1, etc is a job title
Cells b1, d1, f1, etc is the static text 'Document Number'
Cells a2, a3, a4, etc are names of policies for the job in a1
Cells b2, b3, b4, etc are the policy numbers for the jobs in column a

There can be many jobs (a1, c1, e1, etc) and many policies per job.
(but always 1 policy # for each policy)

I need to get the data formatted as

a b c
1 job 1 sop 1 sop001
2 job 1 sop 2 sop002
3 job 2 sop 3 sop003
4 ... ... ...
5 ... ... ...

Any ideas? I came across David McRitchie's RotateCW macro, which
allowed me to pivot the table by 90 deg CCW (a bit easier to use
RegExp's on) but now Im left with alternating rows of policy #'s and
policies:

a b c d
1 doc # sop001 sop002
2 job 1 sop 1 sop 2
3 doc # sop003 sop004 ...
4 job 2 sop 3 sop 4 ...
5 ... ... ... ...
6 ... ... ... ...

and I'm not sure how to go about merging each group of rows into
something like:

a2 b2 b1 c2 c1 ...
a4 b4 b3 c4 c3 ...
.... ... ... ... ... ...
.... ... ... ... ... ...

which would at least make a good base for doing the rest of the
formatting in RegExp land.

Any help would be UBER appreciated.

Thanks,
Chris Bloom

Hi there,

I have a source file which contains x columns. For illustration sake, let's say it looks like this:

CATEGORY NAME
1 J
1 J
1 J
1 J
2 J
2 J
3 J

What I want to do is to take this file in (it will be in Excel format) and allow the user to pick what columns they want, and produce a table like this:

1 4 Names
J
J
J
J
2 2 Names
J
J
3 1 Name
J

I am used to Access VBA, and the way I see it is I have to do the following;

A) Count how many columns there are. Loop through to get the name and column reference of each.
B) Display columns as a choice to the user (through a form or something?) and allow them to pick (checkboxes?) what columns to include.
C) Upon clicking a button, copy only the columns that are needed, and then copy to a new sheet in the Excel file.
D) Apply the grouping (not quite sure how to do this in Excel)

I would prefer to do an Access Report (is 1000x easier!) but the stipulation is that Access will only be used to supply raw data and not to supply the end table (this is not up for discussion, as my preferred way is to do it via a report in Access)

Any ideas?

Thanks

My problem:
I have the survey data in an excel sheet, one respondent per row and one question per column.

What i would like to do is show the number of grades per question (1-5) based on several criteria (the first 6 questions) for publishing on a company intranet.

Could use a pivottable with reformatting of the data but the number of row would be extremely large (have 25000 respondents and 70 questions)

Have tried using productsum, works but to slow for interactivity.

Would be very grateful for tips of how to do this.
Thinking of creating separate pivot tables for each question but that would mean a lot of tables

/Johan

Hello.!

I work in a lab, and it is inventory-taking time. We recently started using a website to track all our chemicals, but the site we use does not let you choose a good format when you want to print a full inventory list, nor does it let you sort the list by anything other than chemical name. So I'm trying to paste it to excel and format it to something that is more useful so i can sort by location, storage color, etc and also print a hard-copy that will be easier to read and won't take 300 pieces of paper (see attached and you'll know what i mean).

I feel like this should be easy, but I'm pretty new with excel and keep having some troubles. I can (mostly) get the data into 2 workable columns and then transpose some of the useful stuff to rows, but can't figure out a way to have it transpose in an array or whatever so it auto-wraps to a new row every 14 cells.

Also the fact that the name of each chemical doesn't already have "name:" in front of it makes things annoying as well cause i used the colon as the delimeter in the text-to-column tool to split the data to two columns. Using the colon does screw up a lot of the notes though cause they often have colons in them too, so in those rows I get an extra 4 or 5 columns if we have multiple bottles of the same reagent.

Anyway I won't attach my botched attempts at getting this to work the proper way, but i can if needed. I have attached a sample before and after though. The before is completely raw how it pastes from the website.

thanks for any help :>

Hi!

When I paste an html table into Excel some cells are automatically
formatted to a different datatype.

My html table contains identifiers such as "6632/04" which I want to
store as text. Excel assumes that data like this is some sort of date
and displays "apr-32" instead when I paste the table. If I try to apply
the text format to the column, Excel converts the values to some type
of integer "1728420" which looks like it could be the integer value of
the date. It isn't possible to get the original value back!

How do I turn off Excels automatic formatting? The same behavior
appears in Excel 2003 and 2000.

Kind Regards,

Pete

I want to remove the front zero(s) from the front and put a space before the last two letters for the following column of data in Excel:

0001MG
0020MG
0100MG
1000MG

Final result will have this format and the data are right aligned.
1 MG
20 MG
100 MG
1000 MG

The numbers can vary (e.g. 0150MG, 0025MG,etc.) but the arrangement is always the same: four figures for the first 4 characters and two letters for the last two characters.
The format of the cells containing the data is: “General”

My table has 7 columns and these data are in the 5th column in a worksheet called “ProductSummary”. The number of rows for the records can vary from 10 to over a couple of hundred.

How can I use VBA to: 1) automatically select the table range in that worksheet and reformat all the data in the 5th column.

Thank you.

Dear Forum,

Please can you help with the following problem which has had me stumped for the past day?

I hope I can explain it sufficiently for you to give me guidance on the correct way forward? Here goes..

I have created a pivotchart and placed it on (say) sheet "GRAPH". Excel has created the associated pivottable and stored it on (say) sheet "PIVOT".

Because the formating of the graph is lost each time the pivotgraph is refreshed i.e page fields changed, i have written a small macro to reformat it which i have put in the "Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)" event in the "PIVOT" sheet. All appeared to work well.

However, on closer investigation, i noticed that the pivotchart wasn't being updated (even though the source pivottable was set to refresh on open - another problem for another day??) so on the pivotchart sheet i had to press the little red exclamation mark to update it and the chart changed before my eyes. I thought I would then record a macro to see what i had to do.

The code the macro recorder gave was "ActiveChart.PivotLayout.PivotTable.RefreshTable" so i simply inserted this into the chart.activate event of the "GRAPH" sheet and assumed it would kick in everytime the graph was activated.

However, it crashes everytime with an error message "unable to get the pivotfields propety of the pivottable class". If I put an msgbox in the relevant "Sub Worksheet_PivotTableUpdate" to display the target.name, its blank!

I've tried all sorts of work arounds like trying to update the pivottable in the graph activate event:-
dim pt as pivottable : set pt = ......... : pt.refreshtable etc
select the sheet before hand etc, etc but no joy.

Following the code through debug it always crashes with the same message. If I manually update the pivottable on the PIVOT sheet (right click "refresh data") the table gets refreshed which then triggers my graph reformat code and everything is fine.

I'm sure i'm doing something wrong but it doesn't appear to like the chart and table being on different sheets?

I hope you have followed my words above but essentially my question is how can i update my pivotchart (through code) based on a pivottable on another sheet?

I really look forward to your replies.

Thanks in anticipation,

Pete


	VB:
	
 PivotTable) 
     
    msgbox Target.name

In working on a database to remove redundancies and make it more efficent I
have run into a problme that I just do not know how to fix. Often when
restructuring tables it is easier to throw the data into EXCEL and then
import it back into the DB.

Becuase I must preserve the data that is currently in the DB rebuilding
tables can be madding. In this case I need to reformat the primary key which
is a field containing some SSNs, and some made up numbers of various lengths.

I want to reformat the feild to 9 charactors, but need to insert leading
zeros to the made up numbers. Example: one number is 118, and would need to
be reformated as 000000118.

There a several different number lengths which makes my job all the harder.
Is there a way to do this that I am not seeing? Any help will be greatly
appreciated...
--
Jacqueline

I have been using excel for several years and recently upgraded to 2003.
I have word documents containing tables of text in the form 45/23/03, when
copied & pasted into excel, any that appear to be a date get converted to
dates and appear as a date code. How can I stop this?
I have columns of data eg 01020003, A203034, 2255003, which I enter as text,
format as text and then may wish to sort/copy or use as the souce or table
index in vlookup etc. whenever I copy or edit these excel 'automatically'
converts the numbers to numeric etc, drops leading zeroes and so forth, so
that they no longer sort properly and don't work in lookup etc. Is there any
way to stop this automatinc reformatting of my data as it is seriously
unproductive.
Conversely, I have working formulae in cells and when I perform a minor edit
they then display as text (this stopped hapenning after I switched off the
'allow editing in cell' option, although I was editing in the formula bar out
of habit anyway).

I just reinstalled Excel 2007 after a HD reformat, and now I'm trying to recreate all my little personal preferences.

1 - when I create a formula by pointing to a cell in a pivot table, I get some atrocious formula that's useless to me. How can I switch this off to get a normal cell reference.

2 - also - when I "Open File", I want to always see "details" as the default view. I can do this for My Computer from the control panel but that doesn't affect the view format in the Open file within Excel. When I click Organize in the Open dialogue box, Folder and Search Options is not available.

Thanks

Here's my problem:

I use a macro to reformat "headers" on a file that has hundreds of "tables" aligned vertically in 1 worksheet. The original file is comma delimited text so when I pull it into Excel, the words in my "headers" become fragmented. My method of fixing this has been to fix the first header, copy it (range of say a1:h3) and then use my macro below to paste it at every occurance of "Table" - I first save off column A and then repaste that after I run my macro to resore all the original table numbers.

very brief example looks like this:
Table 1 - Misc
(data headers starting in col. C)
data...
Table 2 - More misc
(data headers starting in col. C)

data...
Table 3 - Even more misc
(data headers starting in col. C)

data...
===============

The problem occurs when I have the word "table" trigger a paste in a place other than where it should (as in another column other than A or another row within the table that happens to have the word in its stub).

I hope this is understandable. I'm not sure how esle to explain it.

My macro is below. Can anyone offer suggestions on how I can further qualify this "paste" to only the "Table"s I need?

Thank you!
Lisa

Here's my macro:
====================

Sub pasteit()
'
' pasteit Macro
' Macro recorded 6/23/2002 by Lisa
' Macro revised 8/11/2004 by Lisa
'
' Keyboard Shortcut: Ctrl+d
'
Cells.Find(What:="Table", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
ActiveSheet.Paste

Do Until ActiveCell.Address = "$A$1"
Cells.FindNext(After:=ActiveCell).Activate
ActiveSheet.Paste
ActiveSheet.Paste
Loop

MsgBox "The Header Macro is done!", , "FYI 1 of 2"
MsgBox "Don't forget to restore Column A to original!", , "FYI 2 of 2"
End

End Sub
======================

There's a table on a website http://www.allorings.com/size_cross_...nce_framed.htm (I'm clicking "export to ms excel" from the context menu in IE)
which I'm trying to import into excel to do some comparisons and calculations with. My problem is that when Excel "gets the external data" it reformats things like 1/16 (as in one sixteenth) as "Jan 16". Of course when I try to reformat it as text then it gives me a number corresponding to January 16.

How do I get Excel NOT to screw with the formatting when importing data from the web???

many thanks
Guy