Excel DNA

[Paolo Mirabelli]

Ciao a tutti non so se è questo il posto giusto
sono un programmatore sto facendo una applicazione in Visual Basic ora fin qui tutto ok il problema nasce quando devo rendere distribuibile questa applicazione nel senso convertirla in XLL
la mia applicazione in pratica è formata cosi:
un bottone sulla barra click sopra si apre un menu si fanno delle scelte e si interroga un webservices il risultato verra inserito nel foglio excel.

come faccio a convertire tutto il mio progetto in XLL sto provando con Excel DNA ma niente nn riesco o provato per fare un test con una semplice funzione messa un un Module ma non mi carica nulla quando apro il file xll la funzione non la carica.
mi potete aiutare?

[Govert van Drimmelen]

Hi Paolo,

I suggest that you have a look at the migration guide by Patrick O'Beirne, which you can find here: http://sysmod.wordpress.com/2012/11/06/migrating-an-excel-vba-add-in-to-a-vb-net-xll-with-excel-dna-update/

He describes how one might move a project from VBA to VB.NET with Excel-DNA.

Another way to start is by using the NuGet package manager in Visual Studio, to install the "Excel-DNA" package, and the following the ReadMe.txt instructions. From there, you can add a macro like this:

Imports ExcelDna.Integration

Public Module MyAddIn

    <ExcelCommand(MenuName:="Test AddIn", MenuText:="Dump Data")> _

    Public Sub DumpData()

        Dim app = ExcelDnaUtil.Application

        app.Range("A1").Value = "Hello World!"

    End Sub

End Module

The "Test AddIn" menu will be found on the "Add-Ins" tab under Excel 2007 and later.

Regards,

Govert

[Paolo Mirabelli]

Ciao e grazie della risposta,

Io sto provando la strada siggeritami da voi quella dei package NutGet con visual studio ma ho un problema in pratica non riesco a caricare il riferimento a ExcelDNA.Integration   e quando compilo il progetto se provo ad aprire l ADDIN ho il seguente errore :

External library could not be registered - Path: AddInExcelGTR.dll
    Error: The library could not be found at this location.

non riesco ad andare avanti
grazie della diponibilià  a presto

[Govert van Drimmelen]

Hi,

It might well be that the NuGet package's PowerShell script doesn't work right on a localized version of Visual Studio.

You can check the following after installing the NuGet package:

* Your own Class Library project should have a reference to the library ExcelDna.Integration.dll, which is located in 

    <...your add-in project...>\packages\Excel-DNA.0.30.3\lib\ExcelDna.Integration.dll

* Your project should have a file called AddInExcelGTR-AddIn.dna, which has an entry that looks like this:

    <ExternalLibrary Path="AddInExcelDTR.dll" LoadFromBytes="true" Pack="true" />

  (The name in that 'Path' is important - it must match the Assembly Name that you've chosen for your library. If you've change the assembly name for your project, you need to change the Path entry as well.

* Finally, you can check what's in the output directory. You need at least:

  - AddInExcelGTR-AddIn.xll

  - AddInExcelGTR-AddIn.dna

  - AddInExcelGTR.dll

 (Where the .xll and .dna must have the same name, and the <ExternalLibrary .../> must match your .dll name.

  There might also be a AddInExcelGTR-AddIn-packed.xll file, which you can ignore for now.)

-Govert

[Paolo Mirabelli]

Ciao questa è la cartella debug del mio progetto ho ripetuto tutto non ho nessun errore
ma non mi carica la funzione presente nel module

<DnaLibrary Name="Test Add-In" RuntimeVersion="v4.0">
  <ExternalLibrary Path="Test.dll" LoadFromBytes="true" Pack="true" />
 </DnaLibrary>

Module:
Imports ExcelDna.Integration
Module Test
    <ExcelFunction(Description:="My first .NET function")> _
    Public Function HelloDna(name As String) As String
        Return "Hello " & name
    End Function
End Module

alla compilazione ho nella cartella debug correttamente

Test.dll

Test-Addin.dna

Test-Addin.xll

Test-Addin-packed.xll

potresti darci uno sguardo grazie

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_XND-Bo9WkU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

[Govert van Drimmelen]

Hi Paolo,

Ah! Your Module must be "Public":

Public Module Test 

              .... 

End Module

-Govert

[Paolo Mirabelli]

Grazie ho risolto era proprio questo

grazie tanto

[Paolo Mirabelli]

Ciao scusami per un altra domanda in pratica io ho una applicazione scritta in VB la quale ha dei form uno per la login e uno è un menu

la login chiama un webservices per effettuare la login se è corretta apre il form menu qui si compone una stringa attraverso la scelta di combobox e poi chiamo una funzione che sta dentro il Module submit che vede quello che è stato selezionato nel menu ed invia una stringa ad un altro webservices restFull.

si puo esportare tutto questo in XLL?

come mi comporto con i form, devo esportare solo le funzioni che stanno in Module in xll e tutto il resto i form?

--
Mirabelli Paolo

[Govert van Drimmelen]

Hi Paolo,

The forms will have to be created again in Visual Studio as Windows Forms forms.

There's no easy way to move a form over from VBA to VB.NET.

If you only need a few fields - like for a login - and you're targeting Excel 2007+, then you might use the Ribbon instead of a form.

You can put textboxes and dropdowns on the ribbon too.

-Govert

[Paolo Mirabelli]

Ok ma quindi il mio XLL puo contenere anche Form + Module con funzioni ?

avete un esempio del genere

[Paolo Mirabelli]

Ciao senti ma se io devo stampare un xml in un foglio devo mettere tutto in una Sub e non in una function perchè non ho alcun return.

Ma cosi facendo non vedo la Sub poi dal foglio non riesco a richiamarla.

--
Mirabelli Paolo

[Govert van Drimmelen]

Hi Paolo,

You can easily have Excel-DNA add a menu for your Sub:

    <ExcelCommand(MenuName:="My Menu", MenuText:="Dump Data")> _

    Public Sub DumpData()

       ....

    End Sub

On Excel 2007+ the menu will appear under the Add-Ins tab.

With Excel-DNA you can also make your own custom ribbon. But this is the easiest way to give access to your Sub.

-Govert

[Paolo Mirabelli]

Ok ma come posso passare un parametro in input?

[Govert van Drimmelen]

Maybe show an InputBox?

Or make a Form?

-Govert

[Paolo Mirabelli]

Ok un ultima domanda ma per distribuire l XLL devo distribuire l'intera cartella debug del progetto o solamente il file .xll

[Govert van Drimmelen]

Hi Paolo,

Excel-DNA has a utility called ExcelDnaPack that can make a standalone .xll file from your project.

If you used the NuGet package, you should also find a -packed.xll file in the output directory. This file will internally contain your .dll file (because of the <ExternalLibrary ... Pack="true" /> directive). It should work without needing any other files. To test, copy it to an empty directory and try to open the add-in.

If you need to add other .dlls that you reference into the -packed.xll file, you need to add some <Reference Path=... Pack="true" /> entries into the .dna file.

-Govert

[Paolo Mirabelli]

Grazie mille molto gentile e disponibile non sapevo nulla di VB e XLL ma sono riuscito a fare l' ADDIN

dinuovo grazie ciao

[Paolo Mirabelli]

Ciao ma si possono usare Auto_close e Auto_open nell xll,
a me servirebbe alla chiusura salvare dei dati per poi riprenderli alla apertura

--
Mirabelli Paolo

[Govert van Drimmelen]

Hi Paolo,

I don't understand your question at all.

-Govert

[Paolo Mirabelli]

le funzioni Auto_close e Auto_open che si usano in VBA possono essere utilizate in VB

Io devo alla chiusura del mio Xll salvare dei dati e poi recuperarli all apertura del file

[Govert van Drimmelen]

In an Excel-DNA add-in, you can make a public class that implements IExcelAddin. This interface gives you AutoOpen and AutoClose methods.

* AutoOpen() runs when the add-in is loaded.

* AutoClose() only runs when the add-in is uninstalled from the add-ins dialog box, and unloaded from the Excel session. It does not run when Excel closes.

There are other ways of finding out that Excel is shutting down, but it's a bit more difficult. To know that a workbook is closed, you can use the COM Automation objects and related events.

-Govert 

[Paolo Mirabelli]

Ciao sto cercando di salvare dei dati su un foglio Excel per poi riprenderli dalla mia applicazione

il codice per il salvataggio è questo:
<ExcelCommand(MenuName:="GTRAnalytics", MenuText:="Save/Load")> _
    Public Sub save_load()
        Dim saveFileDialog As New SaveFileDialog()
        saveFileDialog.Filter = "File excel (*.xls)|*.xls|Tutti i file(*.*)|*.*"
        saveFileDialog.ShowDialog()
        If saveFileDialog.FileName <> "" Then
            Dim excel As Microsoft.Office.Interop.Excel.Application
            excel = ExcelDnaUtil.Application
           Dim oWbk = excel.Workbooks.Add
            Dim oWsh = oWbk.Worksheet

            'scrivo il  foglio
            Dim i As Integer
            i = 0
            For i = 0 To UBound(qkList)
               
                Dim cellRange As Microsoft.Office.Interop.Excel.Range
                cellRange = oWsh.Cells(i + 1, 1) 'sheet
                cellRange.Value2 = qkList(i)
                cellRange = oWsh.Cells(i + 1, 9) 'sheet
                cellRange.Value2 = qkListMonitor(i)

            Next

            oWbk.SaveAs(saveFileDialog.FileName)
            oWbk.Close(False)
            excel.Quit()

Ma non funzione non mi crea il file dove sbaglio?

[Govert van Drimmelen]

Hi Paolo,

I suggest you wrap the whole thing in a try-catch block, and display any exceptions that might be thrown. Maybe something is going wrong along the way.

-Govert

[Paolo Mirabelli]

Mi dice impossibile trovare il membro publico 'WorkSheet' nel  Tipo 'WorkBook'

[Paolo Mirabelli]

Ciao grazie del supporto che mi stai danto sei veramente gentile.

Ho un altro piccolo problema ho il mio che ha un menu con le voci save/load, enable/disabled ecc ora funziona quasi tutto bene tranne save/load

questa voce "save/load" mi permette di salvare un file con dei dati dentro  e funziona benissimo oppure caricare un file precedentemente salvato (load) in questo caso riesco a caricare il file benissimo senza nessun problema ma spariscono le voci del menu ti allego due immagini che riassumono quello che ho detto pre.png prima del caricamento post.png dopo aver caricato il file

--
Mirabelli Paolo

[Govert van Drimmelen]

Hi Paolo,

Do you still have the line that says "excel.Quit()"?

It looks dangerous.

-Govert

[Paolo Mirabelli]

come si fanno allora a chiudere i processi, dopo aver aperto un file con "OpenFileDialog" mi restano processi appesi in memoria e questo mi da un eccezione come faccio a chiudere i processi appesi

ho provato questi:
  mioExcel.Quit()
            wb.Close()
            wb = Nothing
            sheet = Nothing
            mioExcel = Nothing

            Marshal.ReleaseComObject(mioExcel)
          
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()

ma non funzionano?

[Govert van Drimmelen]

Are you trying to do this from inside a macro in Excel?

Will the user press the button and then Excel must close?

Or are you automating Excel from an external program?

-Govert

[Paolo Mirabelli]

In pratica io ho visto che se apro un file con "OpenFileDialog"   e poi chiudo Excel dall'incona  "X"   si chiude normalmente ma restano appesi in memoria due processi di excel, per questo provavo a killarli in quel modo

devo fare in modo che non restano processi appesi perchè nel mio codice vi è una sub refresh che si esegue ogni tot minuti quindi restando aperto in memoria cerca di eseguire la sub refresh che però va male visto che nn trova più nessun sheet.

Non capisco dove sbaglio ? e nn riesco a far in modo che questi processi si chiudono tutti normalmente

[Govert van Drimmelen]

Hi Paolo,

There's only one way to get it right , and that is to never talk to the Excel COM interfaces from another thread, only use the main Excel thread.

If you follow that rule, Excel will close and exit properly, and you never have to call Marshal.ReleaseComObject, or GC.Collect() or anything.

Sometimes a timer callback might fire on another thread, in which case you can use the ExcelAsyncUtil.QueueAsMacro helper to get your code to run on the main Excel thread. You can also put tracing in to see what thread different parts of you code might be running on. (Checking ManagedThreadId is fine.)

Only the main thread should ever call Excel.

-Govert

[Paolo Mirabelli]

Ok ma questa è il mio openfile:
Public Sub openExcelFile(ByVal FileName As String)
        Try
          
            Dim mioExcel As Object
            mioExcel = CreateObject("Excel.Application")
            Dim wb As Microsoft.Office.Interop.Excel.Workbooks
            wb = mioExcel.Workbooks
            wb.Open(FileName)
            Dim sheet = wb(1).Worksheets(1)
            Dim limit As Integer
            limit = sheet.Range("A65000").End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row()

            Dim i As Integer
            i = 0

            For i = 0 To limit - 1
                Dim qk As String
                qk = sheet.cells(i + 1, 1).value
                Dim s As String
                s = sheet.cells(i + 1, 1).value
                Dim v = Split(s, "#")
                qkList(i) = v(0)

                Dim m As String
                m = v(1) 'wb(1).Worksheets(1).cells(i + 1, 9).value
                If m.Equals("Monitor") Then
                    qkListMonitor(i) = "true"
                Else
                    qkListMonitor(i) = "false"
                End If
                index = index + 1

            Next

            refresh()

            wb.Close()
            wb = Nothing
            sheet = Nothing
            mioExcel = Nothing

            ' Marshal.ReleaseComObject(mioExcel)

            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()

            'cleanProcess(mioExcel)
         

            If Not timerONOF Then
                InitializeTimer()
                timerONOF = True 'è stato schedulato il primo refresh
            End If
        Catch ex As Exception
            MsgBox("Exception" & vbCrLf & ex.Message)
        End Try
    End Sub

come devo fare?

[Govert van Drimmelen]

Does this code run in an Excel add-in?

Or are you driving Excel from another application?

-Govert

[Paolo Mirabelli]

Dentro  Excel Addin.

[Paolo Mirabelli]

e quella funzione si trova nel Module Public

--
Mirabelli Paolo

[Govert van Drimmelen]

OK, from inside an Excel Add-In:

1. You should never call CreateObject ("Excel. Application "). This will make a new Excel process, which is probably not what you want. To get the Excel Application object that you are running in, you call ExcelDnaUtil.Application.

2. You should never call Marshal.ReleaseComObject(...).

3. You never need to call GC.Collect() or GC.WaitForPendingFinalizers() to deal with COM references.

4. You need to be aware on what threads things are running. If you have a timer, you need to know whether the timer callback is on the main thread or on a ThreadPool thread.

5. If you want to start code running from another thread (like the timer callback) you can call ExcelAsyncUtil.QueueAsMacro(...). That will switch to the main thread and run the code there, where you can safely call the COM automation interfaces.

Here's a macro that will quit Excel:

    <ExcelCommand(MenuName:="Testing Quit", MenuText:="Quit Excel")>

    Sub QuitExcel()

        ExcelDnaUtil.Application.Quit()

    End Sub

Just try it on its own, and see that the Excel process exits properly. Then you can add back your other stuff and see when it goes wrong.

-Govert

[Paolo Mirabelli]

C'è qualcosa che mi sfugge pero io devo dal mio AddIn aprire un file elaborarlo e quindi stampare i dati nello sheet del.

se io per aprire il file faccio riferimento sempre a ExcelDnaUtil.Application. cosi sto selezzionando l excel principale e non quello del file che sto cercando di caricare a me mi serve elaborare il file che carico prima di stamparlo nell excel principale.

quindi mi serve far riferimento ad excel e sheet del file salvato dat.xls sul desktop

[Paolo Mirabelli]

Ma si puo usare openfiledialog in Excel DNA non è che ce qual cos'altro per Excel DNA per aprire un file se non faccio createObject nn riesco ad accedere il file che devo elaborare e caricare in excel principale

--
Mirabelli Paolo

[Govert van Drimmelen]

Hi Paolo,

I'm sorry, but I don't really understand what you are doing.

I won't be able to help you more with this problem. I hope you find a way.

-Govert

[Paolo Mirabelli]

ok ma è corretto cosi per apire un file e leggerne il contenuto?

Public Sub openExcelFile(ByVal FileName As String)
        Try
          
            Dim mioExcel As Object

            mioExcel = CreateObject("Excel.Application") 'ExcelDnaUtil.Application

            End If
        Catch ex As Exception
            MsgBox("Exception" & vbCrLf & ex.Message)
        End Try
    End Sub

[Patrick O'Beirne]

On 20/02/2014 17:09, Paolo Mirabelli wrote:
> ok ma č corretto cosi per apire un file e leggerne il contenuto?

Use low-level I/O.


Public Function GetFileContents(ByVal FullPath As String, _
Optional ByRef ErrInfo As String = "") As String

Dim strContents As String = vbNullString
Dim objReader As StreamReader
Try
objReader = New StreamReader(FullPath)
strContents = objReader.ReadToEnd()
objReader.Close()
Catch Ex As Exception
ErrInfo = Ex.Message
End Try
Return strContents
End Function

Public Function SaveTextToFile(ByVal strData As String, _
ByVal FullPath As String, _
Optional ByRef ErrInfo As String = "", _
Optional ByVal append As Boolean = False) As Boolean

Dim bAns As Boolean = False
Dim objReader As StreamWriter
Try
objReader = New StreamWriter(FullPath, append)
objReader.Write(strData)
objReader.Close()
bAns = True
Catch Ex As Exception
ErrInfo = Ex.Message 'passed back ByRef

End Try
Return bAns
End Function

[Paolo Mirabelli]

Ciao ma per fare un operazione alla chiusura è giusto cosi :

Imports ExcelDna.Integration
Public Class ClassUtil
    Implements IExcelAddIn
    Public Sub AutoOpen() Implements ExcelDna.Integration.IExcelAddIn.AutoOpen
        MsgBox("Open")
    End Sub
    Public Sub AutoClose() Implements ExcelDna.Integration.IExcelAddIn.AutoClose
        ExcelDnaUtil.Application.Quit()
    End Sub
  
End Class

Auto open funziona benissimo

Auto Close non funziona non viene richiamato come mai?

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_XND-Bo9WkU/unsubscribe.

To unsubscribe from this group and all its topics, send an email to exceldna+unsubscribe@googlegroups.com.

To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

[Patrick O'Beirne]

Si, bene.
"Imports ExcelDna.Integration" means that this is now implied so you do
not really need
ExcelDna.Integration.IExcelAddIn.AutoOpen
it would be sufficient to say
IExcelAddIn.AutoOpen

AutoClose must be defined with at least an empty body even if not used.
http://exceldna.codeplex.com/wikipage?title=AutoClose%20and%20Detecting%20Excel%20Shutdown

Autoclose is called when the addin is closed, not when a workbook is
closed. Put Application.Quit in your main line code.

Hope I'm right :-)
P

[Paolo Mirabelli]

Come faccio ad effettuare ExcelDnaUtil.Application.Quit()   quando chiudo excel?

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_XND-Bo9WkU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+unsubscribe@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

[Patrick O'Beirne]

Am I understanding you right, Paolo: (the meaning may be lost in translation)

The user has just told Excel to quit from the user interface ...
and when that happens you want to run code to tell Excel to quit ?
Why?

To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.

To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

[Paolo Mirabelli]

Ho una function refresh che si esegue "running" ogni tot minuti.

ora mi restano appesi processi Excel in ram quindi chiudendo excel mi restano processi e dopo 5 minuti si esegue il refresh causando un eccezione perche non trova piu il file excel.

per questo vorrei cercare di chiudere tutti i processi excel quando esco da excel.

avevo pensato auto close ma questo nn si esegue quando chiudo excel percio non funziona.

hai un consiglio per evitare che questa funzione refresh si esegui anche quando ho chiuso excel?

[Paolo Mirabelli]

sorry form my english this is the situation

I have a function(refresh) in my code tha is running every 5 minutes.
when i close excel  a process remain in ram, so after 5 minutes this function (refresh) run and generates a exception because misses sheet and data.

So I must stop this proces

How can  I fix?

--
Mirabelli Paolo

[Patrick O'Beirne]

Let me check:

"ora mi restano appesi processi Excel in ram quindi chiudendo excel mi restano processi "

Start at the beginning: you start Excel and load the ExcelDna addin.
You quit Excel.
Are you saying that after you quit there are other Excel processes still running?
If so, this may be because your code is calling CreateObject ("Excel. Application "). This will make a new Excel process, which is probably not what you want. To get the Excel Application object that you are running in, you get the ExcelDnaUtil.Application object.

P

[Paolo Mirabelli]

Ok ma io uso CreatObject in openfile

Public Sub openExcelFile(ByVal FileName As String)
        Try

            Dim mioExcel As Object

            ' sheet = Nothing
            '  wb.Close()
            ' wb = Nothing
            ' mioExcel.Quit()
            'mioExcel = Nothing
            'wb.Close()

            'wb = Nothing
            'sheet = Nothing
            'mioExcel = Nothing

            ' Marshal.ReleaseComObject(mioExcel)

            'GC.Collect()
            'GC.WaitForPendingFinalizers()
            'GC.Collect()
            'cleanProcess(mioExcel)
         

            If Not timerONOF Then
                InitializeTimer()
                timerONOF = True 'è stato schedulato il primo refresh
            End If
        Catch ex As Exception
            MsgBox("Exception" & vbCrLf & ex.Message)
        End Try
    End Sub

Perchè devo accedere ad un altro file esterno per elaborarlo ed importarlo nel mio sheet

se uso ExcelDna.Application al posto di creatObject non funziona

come risolvo?

[Patrick O'Beirne]

On 21/02/2014 11:59, Paolo Mirabelli wrote:
> se uso ExcelDna.Application al posto di creatObject non funziona

provare questo (non testato)

Imports ExcelDna.Integration.ExcelDnaUtil ' for Application
object
Dim mioExcel As Object
mioExcel = ExcelDnaUtil.Application
Dim wb As object
wb = mioExcel.Workbooks.Open(FileName)
Dim sheet = wb.Worksheets(1)

[Paolo Mirabelli]

Funziona ma facendo cosi il file che apro si apre in una istanza diversa di Excel, avendo cosi due istanze di Excel
mentre io dovrei aprire il file sempre nella stessa instanza di Excel.

Come vedi nell imagine quello di sinistra sarebbe il mio Excel Principale (Main) quello di destra il file che cerco di aprire ma al posto di importarlo in Main lo apre in un nuovo Excel.

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_XND-Bo9WkU/unsubscribe.

To unsubscribe from this group and all its topics, send an email to exceldna+unsubscribe@googlegroups.com.

To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

[Patrick O'Beirne]

On 21/02/2014 13:21, Paolo Mirabelli wrote:

Funziona ma facendo cosi il file che apro si apre in una istanza diversa di Excel, avendo cosi due istanze di Excel

When you use CreateObject you are launching a new instance of Excel. You can do that, if you wish, but if so then at the end of the procedure use mioExcel.Quit to close that instance, leaving the instance holding the ExcelDna addin open.

mentre io dovrei aprire il file sempre nella stessa instanza di Excel.

To do that, use ExcelDnaUtil.Application always. Did you try my code below? I have not, I'm just dropping into email to reply while busy on other tasks, so all I can do is suggest ideas to try.

Come vedi nell imagine quello di sinistra sarebbe il mio Excel Principale (Main) quello di destra il file che cerco di aprire ma al posto di importarlo in Main lo apre in un nuovo Excel.

the image shows Cartel1 on the left and test1 on the right. It looks like you are using Excel 2003. How was that picture taken? Holding down Alt-Tab and pressing PrtScrn?
If these are two separate instances then you must be opening the file in an instance of Excel other than the one the ExcelDna addin is loaded into. And as far as I can see that is being done by CreateObject.

2014-02-21 13:27 GMT+01:00 Patrick O'Beirne <obeir...@gmail.com>:

On 21/02/2014 11:59, Paolo Mirabelli wrote:

se uso ExcelDna.Application al posto di creatObject non funziona

provare questo (non testato)

        Imports ExcelDna.Integration.ExcelDnaUtil   ' for Application object
            Dim mioExcel As Object
            mioExcel = ExcelDnaUtil.Application
            Dim wb As object
            wb = mioExcel.Workbooks.Open(FileName)
            Dim sheet = wb.Worksheets(1)

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_XND-Bo9WkU/unsubscribe.

To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.

To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

--

You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

[Paolo Mirabelli]

No sto usando il codice suggeritomi da voi:

            Dim mioExcel As Object
            mioExcel = ExcelDnaUtil.Application

            Dim wb As Object
            wb = mioExcel.Workbooks.Open(FileName)
         
            Dim sheet = mioExcel.ActiveSheet 'wb(1).Worksheets(1)

ma mi apre test1 in una istanza diversa, come mai?

          

[Patrick O'Beirne]

Come mai, davvero.

If I get time at the weekend I'll try it. Not much code there, should be
simple enough to check.
Excel 2003, right?

[Paolo Mirabelli]

Si ho provato si Excel 2003 k 2010 con stesso risultato la funzione è questa

 Public Sub openExcelFile(ByVal FileName As String)
        Try

            Dim mioExcel As Object
            mioExcel = ExcelDnaUtil.Application 'CreateObject("Excel.Application")

            Dim wb As Object
            wb = mioExcel.Workbooks.Open(FileName)
         
            Dim sheet = mioExcel.ActiveSheet 'wb(1).Worksheets(1)

            Dim limit As Integer
            limit = sheet.Range("A65000").End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row()

            MsgBox(limit)

            Dim i As Integer
            i = 0
            For i = 0 To limit - 1
                Dim qk As String
                qk = sheet.cells(i + 1, 1).value
                Dim s As String
                s = sheet.cells(i + 1, 1).value
                Dim v = Split(s, "#")
                qkList(i) = v(0)

                Dim m As String
                m = v(1) 'wb(1).Worksheets(1).cells(i + 1, 9).value
                If m.Equals("Monitor") Then
                    qkListMonitor(i) = "true"
                Else
                    qkListMonitor(i) = "false"
                End If
                index = index + 1

            Next
            refresh()
                  

            If Not timerONOF Then
                InitializeTimer()
                timerONOF = True 'è stato schedulato il primo refresh
            End If
        Catch ex As Exception
            MsgBox("Exception" & vbCrLf & ex.Message)
        End Try
    End Sub

in pratica dovrebbe leggere il file passato con FileName e mettere il contenuto in un array il resto lo fa refresh.

ma il problema è appunto che apre due instanze di Excel e non nella stessa. Grazie

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_XND-Bo9WkU/unsubscribe.

To unsubscribe from this group and all its topics, send an email to exceldna+unsubscribe@googlegroups.com.

To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

[Patrick O'Beirne]

This works for me in Excel 2010 with no second instance showing. I had
to DIM three variables and comment out two lines to get it to compile.

So maybe your timer function is launching another instance of Excel?

---------------------

MirPaolo81.dna

<DnaLibrary Language="VB" RuntimeVersion="v4.0">
<ExternalLibrary Path="MirPaolo81.dll" />
</DnaLibrary>

---------------------

Paolo.vb

Imports ExcelDna.Integration ' for ExcelDnaUtil,
ExcelCommand, XlCall, etc
Imports Microsoft.Office.Interop.Excel ' Interface types from PIA eg
Workbook, Range

Public Module Paolo

' we use ExcelCommand to attach this macro to a menu button in Excel
<ExcelCommand(MenuName:="Test &XLDNA", MenuText:="Test &XLDNA
Paolo")> _
Public Sub testPaolo()
openExcelFile("F:\DOCS\SCC3\ExcelDna\MirPaolo81\MirPaolo81\Paolo.vb")
End Sub

Public Sub openExcelFile(ByVal FileName As String)

Dim qklist(1000) As String, qkListMonitor(1000) As String

Try

Dim mioExcel As Object
mioExcel = ExcelDnaUtil.Application
'CreateObject("Excel.Application")
Dim wb As Object
wb = mioExcel.Workbooks.Open(FileName)

' put a debug break point on this line to check Excel instances

Dim sheet = mioExcel.ActiveSheet 'wb(1).Worksheets(1)
Dim limit As Integer
limit =
sheet.Range("A65000").End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row()

MsgBox(limit) ' and/or check Excel instances at this pause
Dim i As Integer, index As Integer

i = 0
For i = 0 To limit - 1
Dim qk As String
qk = sheet.cells(i + 1, 1).value
Dim s As String
s = sheet.cells(i + 1, 1).value

Dim v = Split(s & "#", "#")
qklist(i) = v(0)

Dim m As String
m = v(1) 'wb(1).Worksheets(1).cells(i + 1, 9).value
If m.Equals("Monitor") Then
qkListMonitor(i) = "true"
Else
qkListMonitor(i) = "false"
End If
index = index + 1

Next
'refresh()


'If Not timerONOF Then
' InitializeTimer()
' timerONOF = True 'è stato schedulato il primo refresh
'End If
Catch ex As Exception
MsgBox("Exception" & vbCrLf & ex.Message)
End Try
End Sub

End Module

[Paolo Mirabelli]

Ok come dite voi, ho un solo processo in Ram ma me lo apre in una altra finestra di Excel e non nella Cartel2.

come mai?

questo è il codice

 Public Sub openExcelFile(ByVal FileName As String)

        Dim mioExcel As Object
        MsgBox("test")
        mioExcel = ExcelDnaUtil.Application ' CreateObject("Excel.Application") 'ExcelDnaUtil.Application
        MsgBox("excel creato")
        mioExcel.screenupdating = True
        mioExcel.Visible = True
        Dim wb As Object
        Try

            wb = mioExcel.Workbooks.Open(FileName)

            Dim sheet = mioExcel.ActiveSheet 'wb(1).Worksheets(1)
            Dim limit As Integer
            limit = sheet.Range("A65000").End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row()

            Dim i As Integer

            i = 0
            For i = 0 To limit - 1
                Dim qk As String
                qk = sheet.cells(i + 1, 1).value
                Dim s As String
                s = sheet.cells(i + 1, 1).value

                Dim v = Split(s, "#")
                qkList(i) = v(0)

                Dim m As String
                m = v(1) 'wb(1).Worksheets(1).cells(i + 1, 9).value
                If m.Equals("Monitor") Then
                    qkListMonitor(i) = "true"
                Else
                    qkListMonitor(i) = "false"
                End If
                index = index + 1
            Next
            refresh()

            'wb.close()
            'wb = Nothing
            'mioExcel.Quit()
            'mioExcel = Nothing
            ' cleanProcess(mioExcel)
            ' GC.Collect()

            If Not timerONOF Then
                InitializeTimer()
                timerONOF = True 'è stato schedulato il primo refresh
            End If
      
        Catch ex As Exception
            MsgBox("Exception" & vbCrLf & ex.Message)
       
        End Try
    End Sub

mi sembra identico al vostro dove sbaglio?

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_XND-Bo9WkU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+unsubscribe@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

[Patrick O'Beirne]

On 24/02/2014 09:09, Paolo Mirabelli wrote:
> Ok come dite voi, ho un solo processo in Ram ma me lo apre in una
> altra finestra di Excel e non nella Cartel2.
> come mai?

What is Cartel2?

Just to be sure, let me check the basics:

1) You are compiling that code to a dll?
2) You copied the Exceldna.xll to a file with the same name as your dll
but with .xll extension?
3) You are using a .dna file? Show us what you have in that.
4) You are installing the xll as an addin in Excel?

Try this:
Start Excel. Check the addin is there, eg the menu item I showed you is
visible in the Addin tab.

Close any automatic blank workbook. Now run the code by clicking the
button. Do you get one workbook window with one sheet for the file open
in it? That's what should happen.

if, on the other hand. what you want to do is open a file and place the
content in a sheet inside an already opened workbook, then you need to
use a QueryTable instead.

> mi sembra identico al vostro dove sbaglio?

Not identical:

I declared Dim qklist(1000) As String, qkListMonitor(1000) As String
which you did not. I imagine they are module variables you declared
elsewhere?

Neither did you declare timerONOF, nor did you show the InitalizeTimer()
code.

>
> questo è il codice
> Public Sub openExcelFile(ByVal FileName As String)
> Dim mioExcel As Object
> MsgBox("test")
> mioExcel = ExcelDnaUtil.Application '
> CreateObject("Excel.Application") 'ExcelDnaUtil.Application
> MsgBox("excel creato")

Strictly speaking, an instance of Excel is not created there ...
mioExcel simply points to the instance of Excel in which the addin is
running.

Therefore there is no need to add these lines, the Excel instance you
previously started and into which the xll is opened is already running
and visible.

> mioExcel.screenupdating = True
> mioExcel.Visible = True

Patrick

[Paolo Mirabelli]

I miei passi sno

1 apro Excel importo l'addin

2  apro un nuovo foglio excel bianco

3 faccio openfile con la mia funzione openExcelFile 

4 ora il file che ho selezionato lo devo aprire nel file bianco aperto prima

serve il query table? come si fa? qualche suggerimento?

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_XND-Bo9WkU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+unsubscribe@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

[Patrick O'Beirne]

On 24/02/2014 16:40, Paolo Mirabelli wrote:
> I miei passi sno
> 1 apro Excel importo l'addin
> 2 apro un nuovo foglio excel bianco

Why a new blank workbook? Your function opens the text file as a new
workbook. Did you try what I said - run the code with NO blank workbook
open?

You get exactly the same effect if you do File > Open from the Excel
user interface.

> 3 faccio openfile con la mia funzione openExcelFile

> 4 ora il file che ho selezionato lo devo aprire nel file bianco aperto
> prima
>
> serve il query table? come si fa? qualche suggerimento?

Ah... look up the Excel VBA object model for QueryTables.
Or, in Excel, record a VBA macro and learn from the code ... eg

Start a new blank workbook, which is what you want to do.
Start the recorder then :
Data Tab
> Get External Data group
> click From Text
> select your text file
> Delimited, Next
> Other, enter #, Next
> select Text column type, Finish
> click OK to select the existing worksheet location.

Then stop the recorder and read the code.

Good luck

Patrick

[Paolo Mirabelli]

ciao ti ho inviato una mail con i file del progetto qui non mi fa allegare .rar appena puoi se puoi dargli uno sguardo.

funziona ma subito dopo aver aperto il file va in crash

Patrick

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_XND-Bo9WkU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+unsubscribe@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

[Paolo Mirabelli]

Ciao a tutti la funzione apri file mi da anche un altro problema in pratica mi spariscono le voci dei comandi menu, come mai?

 Public Sub openExcelFile(ByVal FileName As String)

        Dim mioExcel As Object
        mioExcel = ExcelDnaUtil.Application ' CreateObject("Excel.Application") 'ExcelDnaUtil.Application
        ' mioExcel.workbooks(1).worksheets(1).Visible = True
        mioExcel.screenupdating = True
      
        ' mioExcel.Visible = False
        'rendo il file visibile

        Dim wb As Object
        Try
            wb = mioExcel.Workbooks.Open(FileName)

            Dim ws As Object
            MsgBox("test")
            'rendo visibile lo sheet nascosto
            'For Each ws In wb.worksheets
            '    ws.Visible = True
            ' Next ws
            '////
            Dim sheet = mioExcel.ActiveSheet

            Dim limit As Integer
            limit = sheet.Range("A65000").End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row()

            'faccio una copia dei qk in list

            For k = 0 To limit - 1
                Dim s As String
                s = sheet.cells(k + 1, 1).value
                list(k) = s
            Next

            'pulisco il foglio
            For n = limit To 1 Step -1
                sheet.cells(n, 10).EntireRow.Delete()
            Next
            'stampo dati login
            Dim cellRange As Microsoft.Office.Interop.Excel.Range
            cellRange = sheet.cells(1, 8)
            cellRange.Value2 = "V1.0.0 - GTR Analytics - Traded Prices Web"
            cellRange = sheet.Cells(2, 9)
            cellRange.Value2 = "Logged as: " & UserFormOpen.getUserName()
            cellRange = sheet.Cells(3, 8)
            cellRange.Value2 = "Refresh:"
            cellRange = sheet.Cells(3, 9)
            If enabled Then
                cellRange.Value2 = "Enabled"
            Else
                cellRange.Value2 = "Disabled"
            End If
            'fine stampa dati login
            'stampa contratti sul foglio

            Dim i As Integer
            i = 0
            For i = 0 To limit - 1
                ' Dim qk As String

                'qk = list(i) 'sheet.cells(i + 1, 1).value
                Dim s As String
                s = list(i) 'sheet.cells(i + 1, 1).value

                Dim v = Split(s, "#")
                qkList(i) = v(0)

                Dim m As String
                m = v(1) 'wb(1).Worksheets(1).cells(i + 1, 9).value
                If m.Equals("Monitor") Then
                    qkListMonitor(i) = "true"
                Else
                    qkListMonitor(i) = "false"
                End If
                index = index + 1
            Next
            refresh()

            If Not timerONOF Then
                InitializeTimer()
                timerONOF = True 'è stato schedulato il primo refresh
            End If

        Catch ex As Exception
            MsgBox("Exception" & vbCrLf & ex.Message)
        End Try

    End Sub

Non  faccio nessuna close, faccio anche il salvataggio ma con il salvataggio non ho nessun problema mentre con l'apertura di un file sparisce il menu dell'AddIn, mi sapreste aiutare?

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_XND-Bo9WkU/unsubscribe.

To unsubscribe from this group and all its topics, send an email to exceldna+unsubscribe@googlegroups.com.

To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mirabelli Paolo

[Patrick O'Beirne]

I'm just catching up on email today after a couple of days away.

Paolo, so far I have proposed three approaches:

1) Use file I?O - i.e. Stream, so that you process the text file as
strings, no need to use a temporary workbook.

2) Your present method opens a new workbook because that is what the
method Workbooks.Open(...) does.

3) Use a Querytable to set up a Data Import. That allows for a very
simple .Refresh method that can update the worksheet from the file as
often as you wish without closing and re-opening the text file each time.

I'll be on a client site and away for the next three or four weeks so I
am unlikely to be able to look at your attachments.

All I can suggest now is that you use Visual Studio or similar and
step-debug your app line by line to locate the cause of the crash.

http://www.sysmod.com/vba-to-vb.net-xll-add-in-with-excel-dna.pdf


Good luck,

Patrick