Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Linking multiple worksheets to access database Results

I have a workbook that contains multiple worksheets that I need linked as separate tables to an Access database. Using the TransferSpreadsheet command in VBA (from Excel), I loop thru the sheets and attempt to link each table separately. The tables are created with the correct names; however, the tables are all linked to the 1st visible worksheet in the Excel workbook. Following is the code -- how do I fix?

	VB:
	
 RefreshDBLinks() 
     
    Dim MyAccess As Access.Application 
    Dim NewDB As String 
    Dim Tbls() As String 
    Dim CurTable As String 
    Dim x As Worksheet 
    Dim ThisSheet As Worksheet 
    Dim OrigPos As Integer 
     
    Set MyAccess = CreateObject("Access.Application") 
     
    MyAccess.OpenCurrentDatabase NewDBName, True 
    MyAccess.Application.Visible = False 
     
    For Each x In ThisWorkbook.Worksheets 
        If Left(x.Name, 3) = "tbl" And x.Name  "tbl_Automation" Then 
            t = t + 1 
            Redim Preserve Tbls(t) 
            Tbls(t) = x.Name 
        End If 
    Next x 
     
    For i = 1 To UBound(Tbls()) 
        CurTable = Tbls(i) 
        MyAccess.DoCmd.DeleteObject acTable, CurTable 
        MyAccess.DoCmd.TransferSpreadsheet transfertype:=acLink, spreadsheettype:=acSpreadsheetTypeExcel5, _ 
        tablename:=CurTable, Filename:=NewName, hasfieldnames:=True 
         
         
    Next i 
    MyAccess.CloseCurrentDatabase 
     
    Set MyAccess = Nothing 
    Sheets("Summary").Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance for your feedback!

I have an excel project that involves multiple workbook and a summary workbook. Each of the multiple workbooks represents a predictive model for a specific product with unique attributes. Each product workbook has a worksheet that is linked to a specific query in an Access database.

All of the workbooks and the database are kept in one folder.

I have 2 questions:
1: I am looking for code to open all workbooks within a folder, but I do not want to open them all at once (too many). I would like sequential opening with a "Refresh All" and "Save & Close" before opening the next workbook in the folder.

2. When I copy the folder, the link to the database all still refer back to the original database, instead of changing to the new copied database. Is there a way to make the link move or a way to change it with VBA.

Thanks in advance for the assistance

I have created multiple Excel worksheets that currently function as automated
forms; they contain drop down menus, calculations, and multiple validations
to reduce user error. At this point the forms are printed; but what I am
hoping I can do is feed the entered data into an Access database.

Does anyone have any suggestions as to where I start in figuring out how to
do this? There are multiple users so that is also a consideration, ideally I
would like it to feed one database, but if for the initial set up I had to
feel individual ones and then link the data together, that would be okay as
well.

Thank you in advance for your assistance.
--
AB

I have an excel database with multiple worksheets in the program. I've
created pictures that I want to be able to access from the main
worksheet that is contained in others within that file when a certain
criteria is met asking for that particular illustration. Is there a way
to do that?
example I type 1 And it shows one illustration,, 2 another, etc.

Thank You.

--
MrSales
------------------------------------------------------------------------
MrSales's Profile: http://www.excelforum.com/member.php...o&userid=32572
View this thread: http://www.excelforum.com/showthread...hreadid=560079

Hi

I'm starting with a Spreadsheet that is being used to represent the
financial and audit information for individual projects. 1 spreadsheet
document = 1 project.

I have a worksheet that is linked to an Access Database. I need the
information from this Spreadsheet Document to be filtered into a Formatted
Worksheet within another Spreadsheet Document.
Currently I have the Worksheets (linked and formatted) in the same document
and I am using a VLOOKUP format to transfer the information.

However my problem occurs - because I need to 'save as' so that each
individual project is represented by one document. However the original link
to the access database - is not saved when I save as. Therefore I can only
create one project document at a time - when I actually need something close
to 300 documents linked to the database.

My original plan was to Mail Merge the Linked Worksheet to the formatted
worksheet - therefore creating multiple merges to the Linked Worksheet. But I
can't work out how to do this between two Spreadsheet documents - Is this
Possible At All?????

I guess my question is

IS THERE A WAY TO LINK OR MERGE TWO SEPARATE SPREADSHEETS TOGETHER???

Thanks in advance - I hope

Rebecca

I have an excel database with multiple worksheets in the program. I've created pictures that I want to be able to access from the main worksheet that are contained in other worksheets within that file. I want to be able to access that picture from a particular cell by clicking on it like a hyperlink. Is there a way to do that?

Thank You.

Hello,

I have an excel file that is used every day to input production data in to the database. It contains over 20 userforms, 14 worksheets, and a TON of code. I have now been asked to setup the file to allow multiple users to input data into the same file at the same time.

Problem:
All of the sheets are referenced in the code multiple times, 5 of the sheets are tables and need to remain tables for administrative purposes, 2 of the tables are linked to another database. I can't share a workbook with tables (so it says).

Is there anyway to allow multiple people to input data at the same time? Maybe something like export the userforms to an exe file that references the excel file. Does anyone have any ideas?

Thanks in advance!

Here's what I'm trying to do. I have an Excel 2003 worksheet named "xTally" that I would like to upload to a specific table named TEMP_DWORTally in an SQL Server 2005 database. This table contains a trigger (confirmed to be functioning correctly) which serves as both a security and a data transformation layer performing lookups, etc. on new records and then transfering them to a permanent table. (The user name and password used to accomplish this have been given access to this table only.)

Here is the code I'm using in Excel VBA to perform the upload:


	VB:
	
 Upload() 
     
    Dim cxSOPDB As ADODB.Connection 
    Dim strXLSConnection As String 
    Dim strUpTally As String 
    Dim lngRecsAffected As Long 
     
    Set cxSOPDB = New ADODB.Connection 
     
     'Excel Data Connection
    strXLSConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" 
    strXLSConnection = strXLSConnection & "Data Source=" & ThisWorkbook.FullName & ";" 
    strXLSConnection = strXLSConnection & "Extended Properties='Excel 8.0;HDR=YES;IMEX=1'" 
     
     'Upload Data
    strUpTally = "INSERT INTO [ODBC;Driver={SQL Server};SERVER=(localhost);DATABASE=Sales;" 
    strUpTally = strUpTally & "UID=******;Pwd******;].TEMP_DWORTally ([LAN_ID], [TallyDate], [DayTimePeriod], [OrderClass],
[OrderCount]) " 
    strUpTally = strUpTally & "SELECT [LAN_ID], [TallyDate], [DayTimePeriod], [OrderClass], [OrderCount] FROM [xTally$];" 
     
    With cxSOPDB 
        .Open strXLSConnection 
        .Execute strUpTally, lngRecsAffected, adExecuteNoRecords 
        .Close 
    End With 
    Debug.Print "Records affected: " & lngRecsAffected 
     
    Set cxSOPDB = Nothing 
     
    Debug.Print Time 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
You'll notice that my connection string connects to the Excel file itself, and the SQL command links to the proper database table using ODBC. The reason I'm not connecting first to the server directly and then linking to the file is because I'm not aware of any method for having the server pull data out of Excel while the workbook is open. It's also worth noting that I'll eventually have a number of similar Excel files used by various people, so it seems much easier to send data to the database than it is to have the database pull data in from many source files on different computers.

The upload process itself works just fine, except that it seems to be loading the data to the database one row at a time -- which is a problem, since the trigger I have on the target table ends up firing for each row rather than at the end of the upload process. (I should note that it doesn't fire for each row when I run similar procedures directly within SQL Server, so it would seem to be an issue with either JET or ODBC.) I need to have all of the records uploaded at once in a single batch so that the trigger will fire only once per upload.

After banging my head on my desk for two days, I'm at a loss to explain why this problem is arising. Any help on the above would be much, much appreciated.

Thanks,

--Todeswalzer

Problem that I'm trying to help resolve this monday is;

12 users access a network share from multiple states. They each have a workbook with several sheets in it, one of which updates to a 13th Shared Workbook. Data from the Shared workbook is then picked imported into a database every three hours (24 horus a day due to mining sites) at 3,6,9,12 etc.

Presently user opens their workbook via shortcut, which takes 45-50 seconds due to conditions of their connection. They have button which currently Save's their workbook, opens the Shared Workbook (which takes updates from theirs) Saves it, closes it. Then resumes working on their own. Not to mention pauses that occur because they are read/writing from a shared workbook located elsewhere.

Some users bash this button, giving them a wonderful hurry up and wait experience while others done bother period until the database update occurs which shuts all the workbooks, uploaded shared, then unlocks them again.

I spent sometime brainstorming with folks in the office then thought I would pop the idea up here and see what people think might be a better to achieve

Most popular solution after the office brainstorm was;

1. Have their workbook copy to their own laptops
2. Have it intermitently update the Shared one - perhaps using a SaveAs to dump a worksheet to the Shared directory (as it only requires one sheet of the workbook) and have the Share linked to them, rather than user Workbooks.
3. Ensure that it does some data exchange with the Shared one every 2 hours 45 min to ensure that the data is current for the database access.

Any and idea's are welcomed

Thank you for your time,
WageSlave

Edited: Tired typing

Hello all I'm currently in a time crunch at work and need some help
developing a forumula to give me totals based of three criteria. I'm really
in a bind here and could use some help as fast as possible.

I'm using named ranges for my columnar data, and I'm attempting to use VBA
combo boxes to give me two of my search criteria. The third criteria is the
in adjacent cells.

My data sources and logic path is Contract Number --> Fiscal Year -->
Calendar Month.

My data is pulled from an Access database through several queries and the
ranges: conNum, finDate, and Total are defined using =offset.

What I'm trying to do, is total the invoices that I have by month, and the
user selects the contract that they want with the first combo box, and this
triggers the code to populate the second combo box with the fiscal years that
the contract has invoices for.

I've tried a couple different formula's but they always return an error.
For example this formula returns a #value error. I've done through each step
in the evaluations, and it's not returning the correct values for some of the
logic tests and then ultimately returns a #value when I get to the month
criteria.
=SUM((conNum="varCon")*(YEAR(finDate)="varYear")*( MONTH(finDate)+6=MONTH(Budget!$A12))*Total)

I've also tried this formula, which initially gave me a #name error but now
just returns 1/0/1900 no matter how I adjust the month cell.
=SUMPRODUCT(--(conNum="varCon"),--(YEAR(finDate)="varYear"),--(MONTH(finDate)+6="'Budget'!$A15"),Total)

Both of these formula give "User type not defined" errors from VBA and I'm
not entirely sure where to start looking and how to fix it. The reference to
budget!$A15 is a cell reference to the cell where the month names are stored.
A6:A17 where A6 = July and I dragged down the rest of the months. I'm
placing the formulas in B6:B17 hence the relative cell reference so I can
just drag down the forumula. All of the user configurable and interace is on
worksheet "budget" and I have the ranges and queries on worksheet "data".
If you require anymore information please don't hesitate to ask.

I've looked over several website and many of the links that have been posted
previously in this newgroup, and I've tried modifying several of the formulas
that I've come accross, but I'm still hitting this brick wall.

On a side note, I'm having a seperate issue with a VBA command button and
updating the .connection properties of some pivot tables and queries. The
thread is located he
http://www.microsoft.com/office/comm...f25&sloc=en-us

I have an Access Database set up with multiple queries. I have been copying and pasting the final data from each query into my Excel worksheets, so that I can populate my final report and graphs in Excel. This is a process that I run multiple times a day. I am trying to find a way to link the Excel cells to the Access queries to be updated everytime I run the queries

Microsoft ExcelHow 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?

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?

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?

I have two huge (53000 rows each) worksheets (file size 50MB)and I want to
build one pivot table from them. There are 15 columns of data each, with
exactly the same columns headers(only the data is different. How do I do this?

I have been trying multiple consolidation ranges and its not working. I
don't get the headers to choose from when I try to create the table. I get
Row, Column, value and page one as options.

Is there a way to do this without having to copy and paste from one to
another (impossible as it would have more than 65500 rows in Excel 2000)? The
only answer I can think of would be linking the two sheets to an Access
database and joining them there, then make a pivot table using the "External
data" option.

Anyone have a better idea?
Thanks

Raphael

Although I am very comfortable with computers, my Excel experience is quite limited to simple spreadsheets. My place of work is looking for a simpler way to track the use of our raw materials.

I have created this spreadsheet and have linked it to a database in Access, to create reports and queries.
http://i147.photobucket.com/albums/r.../Materials.jpg
(to the right of the Area(sq_in)OH column are Location and Consignment, both unimportant to the task at hand)

Ideally, this is what I would like to happen. ( I should mention, it should be kept as user friendly as possible, being that multiple people will be accessing the workbook.)

1. I would like to reference a specific material whether by selecting its PartNumber, ColorCode (to be determined in the future), or by referencing together its Material, Thickness, and Color.

2. Once the material is identified, I would like to input data to manipulate the UOMOH (Unit of Measure On Hands).
This is where it gets tricky...
I would like all of the materials' characteristics to be related. For example, it would be known that 1 UOMOH/Sheet of R 000000000125 has a Weight(lbs)OH of 50 and an Area(sq_in)OH of 7200(60X120). Therefore, if I have a piece of material R 000000000125 that is 10inX20in, I can enter that data end it would add/subtract 200sq_in from the Area(sq_in)OH. But, at the same time, recognize 200sq_in as 0.027 Sheets, adding/subtracting .027 from UOMOH, as well as adding/subtracting 1.35 from Weight(lbs)OH.

I would like to keep all the centralized data of materials on one worksheet, while having one or more separate worksheets for data entry.
Maybe something like this:

Are you adding/removing material? (select)
Select Your Material? (Maybe the use of drop down menus?)
Material W ___ Material L____
or
Material Weight____
Update Inventory (Yes/Clear Input fields)

Please understand I have spent several hours researching and educating myself on the subject. However, I believe I'm going to have to include macros and I can't seem to grasp how they work.

Any help will be appreciated. And if this is asking too much, I understand.
Thanks in advance.

Is there an example of a DB procedure that adds a col of nums from several
worksheets and multiple workbooks? I am new to excel sql database coding.
Currently I open the relevant files and get the numbers I need. I would
prefer to not open the files. It seems to me that my programing will be more
efficient if I access the excel database to collect information. It seems to
be that way with other programs also. ACAD, ect.

I need to sum data from (col 3), a number, based on (col 4 = name), a
string. I need to look through several sheets in several workbooks. I will
identify the workbooks in the code but the worksheets may change. ie. I keep
adding worksheets weekly. I want the programing to cycle through all sheets
in the workbooks that I define.

I suppose this is alot to ask. I have used oracle before so I understand
that. But using the sql to access an excel file is new stuff. I need to get a
book for this. Problem is, I think this is where I leave VBA and continue
with my VB.net programing. This way I can write all of my stuff in vb.net for
ACAD and EXCEL, Windows Applications, ect.

I there is a link to somewhere or an easy way to do this let me know.

Thanks for all the help you have provided so far. My boss think I am smart!

I have 4 different workbooks. One is called ‘Universal Pricing Sheet’ and contains a sheet called S1Pri which has commodity price data. The other 3 workbooks also contain S1Pri sheets (with the same data). Additionally, these 3 workbooks all have sheets called ‘Output’ which contain revenue information based on the data in the ‘Output’ sheets. The results on the ‘Output’ tab are driven by and dependent on the S1Pri tab in each of the respective files.

I need to integrate 3 sheets called ‘Output’ (all of which are contained in 3 different workbooks) and cash flow models within the workbooks into an SQL database or storage module (could be Excel, Access etc). The goal is to link these three output sheets in a manner such that the data in each output sheet is driven by one S1Pri sheet – either through the ‘Universal Pricing Sheet’ excel file, or a copy of such a file in the chosen storage module. So when inputs to ‘Universal Pricing Sheet’ are made the contents of each ‘Output’ tab should be modified as they would if I went directly into the Excel file and changed the same thing on each of the individual S1Pri sheets. However the underlying 3 excel files should never be modified in any way. I want to replicate these output tabs, in an efficient manner, without changing the underlying files.

Also, I want it to be possible to implement process on a large scale with many input files rather than simply creating 3 excel copies of the files and manually link the inputs of each file to the Universal Pricing Sheet.

I would just like some suggestions on how to approach this problem.

Thanks

Hi All,

I am using XML to integrate data in a SQL database and data that is in excel. My key problem is maintaining data integrity in the database with what the user can change in the spreadsheet. I am trying to create some code that will effectively do a “diff” on the existing data in the spreadsheet XMLMAP and the new data in the database that will be imported.

The logic is therefore to reload an XML file into a spreadsheet to check what has changed in the spreadsheet compared to what is being loaded. There are some limitations to Excel and XML as some of you probably know. The key limitation is that an XML import is effectively a bulk load of data and therefore I have to create a copy of what is currently in the spreadsheet in order to compare to the new imported data and apply the changes that have been made to the new XML.

I have just started using VB and have coding skills that are about 10 years old. So I am struggling with the language as well.

Of course there are multiple ways of doing this and have attempted several of them
1. Attempt to query the XML on the spreadsheet using XPath to create a copy into another table / spreadsheet before importing the new XML. I gave up on this approach because I was getting XPath errors when I was using the XMLDataQuery. The need to also use a different language was also a little overwhelming so I sought to try another approach.
2. Second approach was to use a listobject as the imported XML exists within a list object in a worksheet in a workbook, aka the containment hierarchy. I have cut this code below but am having issues. These are
a. I am confused about how to access the object through the containment hierarchy. As there could be multiple list objectives on a spreadsheet, I am trying to reference the listobject that has the xmlmap and not others. Thus there are two hierarchies, one from the workbook to the XML and the other from the worksheet to the listobject.
b. In my test code I have been able to find the xmlmap via an index as there is currently only one listobject in my test code. However, I am unable to assign the instance of the xml listobject to a new listobject to take a copy of the previously imported data. This seems to be anti objected oriented to me.

Skippets of the code are below
Private Sub CommandButton1Import_Click()
    Dim objMapToImport As XmlMap
    Dim CurrMap As ListObject
    Dim NewMap As ListObject
    Dim CurrWorksheet As Worksheet
    Dim NewWorksheet As Worksheet
        
‘ Find the handle for the xmlmap I am interested in      
    Set objMapToImport = ActiveWorkbook.XmlMaps("CostDetailTool_Map")

‘ Find the handle for the worksheet that has the data I am interested in      
    Set CurrWorksheet = ActiveWorkbook.Worksheets("IPVPN Links")
    
‘ Find the listobject on the worksheet I am interested in    
    Set CurrMap = CurrWorksheet.ListObjects(1)
    
‘Create a temporary worksheet and listobject to copy the data to     
    Set NewWorksheet = ActiveWorkbook.Worksheets.Add
    Set NewMap = NewWorksheet.ListObjects.Add
    
‘Set the data

    Set NewMap = CurrMap


End Sub
Result of this is a clean run of code but the map do not copy over.

What is my best approach with this?

Do I give up on the listobject approach and learn Xpath to access data in the map. Is there another approach? Do I need to go back to VB school? Ahhrrr

Please help if you can… Thanks in advance

vbexcelnewbie

Hey folks,

First time on here, directed by a guy at the PC Mech forums. I hope this is the right spot to put this; I unfortunately don't really know how to search for the answer to this question. Here it is:

We use Excel and Access 2007 at work to report on loads of information, most of which I deal with. This information is spread out across multiple workbooks and worksheets, much of it linked (to update charts and tables by date, etc). What I am trying to do is create a single workbook that contains a summary of all of that information to be accessed by managers at their leisure. This summary will include tables, charts, etc.The problem I have is...how can I do that and make it available to them without having all of the other workbooks open all the time? Do I have to create an Access Database specifically for this or can I avoid Access altogether and work some formulaic wizardry?

Sorry if the terminology is fuzzy...I am fairly new to Excel, and learning a LOT as I go. Thanks for the help!

Hitch


No luck finding an answer? You could always try Google.