EXCELer.org

Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth. - "Sherlock holmes" "speak softly and carry a big stick" - theodore roosevelt. Fear leads to anger, anger leads to hate, hate leads to suffering - Yoda

Hi,

I want to get the page no of a perticular cell in excel 2007.

Art Of Living Is Art Of Giving

Thanks & Regards, With Raghav

I am maintaining an old VB app that reads data from batches of spreadsheets. 

Some of these spreadsheets have linked data in external, inaccessible spreadsheets.  These have never caused an issue, because of the following code:

m_excelApplication = New Excel.Application
m_excelApplication.DisplayAlerts = False
m_excelApplication.Visible = False
m_excelApplication.AskToUpdateLinks = False
m_excelApplication.EnableEvents = False
m_excelApplication.ScreenUpdating = False
m_excelApplication.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable

Now that users of this application are upgrading from Office 2003 to Office 2010, these spreadsheets with linked data are popping up dialogs:

Security Alert - The identity of this web site of the integrity of this connection cannot be verified.

Microsoft Excel - Unable top open http://linked_spreadsheet_url

Why are these dialogs now appearing, when DisplayAlerts is set to false?

*Edit - I should note that a new application object is created for each spreadsheet being read in, so .DisplayAlerts is always False.

I am embedding the content of an excel file to my word document using the following code:

 Microsoft.Office.Interop.Excel.Application excel =null;
Microsoft.Office.Interop.Excel.Workbook tempWorkbook = null;

 excel =new Microsoft.Office.Interop.Excel.Application();
WorkBooks = excel.Workbooks;
tempWorkbook = WorkBooks.Open(excelDocument, Type.Missing, Type.Missing, Type.Missing, "password", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Microsoft.Office.Interop.WordApplication.ActiveWindow.Selection.InlineShapes.AddOLEObject(Type.Missing, excelDocument, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

in the finally block I release the COM objects:

  if (tempWorkbook != null)
                    {
                       
                        Marshal.FinalReleaseComObject(tempWorkbook);
                        tempWorkbook = null;
                    }
                    if (WorkBooks != null)
                    {
                       
                        Marshal.FinalReleaseComObject(WorkBooks);
                        WorkBooks = null;
                    }
                    if (excel != null)
                    {

                        excel.Quit();
                        Marshal.FinalReleaseComObject(excel);
                        excel = null;
                    }

But the excel process still runs.What am I doing wrong?

I'm trying to retrieve the information stored in in ActiveWorkbook.Connections("1-RES Shopping Lists").OLEDBCConnections

                 .CommandText = Array("Recipes$")

                 .Connection = Array(Data Source=C:\Users\robertsutor\Desktop\1-RES Shopping Lists.xlsm)  ' Only this portion

How do I get these values stored in a variable and what should the data type be for the variable?

In this code I'm trying to retrieve this information and display it in a MsgBox (VBA doesn't like the code lines in bold; Object doesn't support property or method):

Sub ConnectionString()
Dim strConnShtName As String
Dim strConnPath
strConnShtName = ActiveWorkbook.Connections("1-RES Shopping Lists").OLEBConnetion.CommandText
strConnPath = ActiveWorkbook.Connections("1-RES Shopping Lists").OLEBConnetion.Connection = Array("Data Source")
MsgBox ("Sheet Name:  " & strConnShtName & "Path:  " & "strConnPath")

End Sub

Any help would be appreciated.  I'm stumped??

HI all,

i have used excel to record a macro for a list of step of advance filter.  when i finished all my "click" steps to perform the advance filter, the result is correct.

But when i take the corresponding VBA code and re-run, it does't work , seems that it is no filter result.

i am doing advance filter on a date field, is the problem related to it?

the code i generated is listed below.

    Range("A7:P916").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("'Ad. filter'!Criteria"), Unique:=False

Data format of date is "14/9/2010", and i have set the Criteria as >=15/2/2011 and <=14/2/2012

i dunno why i can't generated the correct result , pls help

THANKS

Hi,

my company wants to request data to reported to us in xml file format, we will supply the xml schema file to our reporting population.  i wondered if excel enables users to generate an xml file given a supplied xml schema file?  in other words, if our reporting population have the xml schema file we supply and excel is it enough for them to generate a valid xml file using our schema?

I know it probably seems like a very straightforward question, but all i have been able to find online is how to generate an xml and schema file from excel - which isn't quite the same as generating an xml file from excel and a supplied schema file. 

Thanks,
Amanda :)

When developing a macro I often use Debug.Print to show results in the immediate window and then have to manualy delete the contents of the immediate window between executions of the macro.  Is there a VBA action to delete the contents of the immediate window?