VBA in Xcelsius not working properly

other Xcelsius - Crystal Dashboard Design
1

Hello!!!

I’m having problems with my VBA code in Xcelsius.

The thing is, I want to transfer some data from a Excel spreadsheet to the Xcelsius spreadsheet.

For this, I created a button in Xcelsius spreadsheet that activates a VBA code.
This code gets a file choosed by the user, open it, copy the data to Xcelsius and then close the file.

The problem is, when I run my code from whe VBA editor, it works fine.
But when I run it from the button, by clicking the button, it gives me an error 1004, that the Open function from Workbooks could not be processed.

This problem happens with other funcions that uses Excel.Application, like Application.DisplayAlerts, or things like this.
But only in the click of the button. Runing from code behing works fine.

My code, in the button is:


Private Sub CommandButton1_Click()
    
    'Declare all of the variables needed
    Dim file_name
    Dim avarSplit As Variant
    Dim intIndex As Integer
    Dim source
    Dim WsTo As Worksheet, WsFrom As Worksheet
    
    'Get the name of this workbook
    file_name = ThisWorkbook.FullName
    
    'Gets the source of the Source file
    source = Excel.Application.GetOpenFilename( _
        Title:="Please choose a file", _
        FileFilter:="Excel Files *.xlsx (*.*),")
    
    'Open the source WorkBook
    Excel.Application.Workbooks.Open (source)
      
    'Process the source to get only the file name
    avarSplit = Split(source, "\")
    intIndex = UBound(avarSplit)
    source = avarSplit(intIndex)
    avarSplit = Split(source, ".")
    source = avarSplit(0)
    
   
    'Create the From and To worksheets
    Set WsTo = Workbooks(file_name).Sheets(3)
    Set WsFrom = Workbooks(source).Sheets(1)

    'Transfer the information from one to another
    WsTo.Range("C5", "C11") = WsFrom.Range("B9", "B15").Value
    WsTo.Range("D5", "D11") = WsFrom.Range("C9", "C15").Value
    WsTo.Range("E5", "E11") = WsFrom.Range("D9", "D15").Value
    WsTo.Range("F5", "F11") = WsFrom.Range("E9", "E15").Value
    WsTo.Range("G5", "G11") = WsFrom.Range("F9", "F15").Value
    WsTo.Range("H5", "H11") = WsFrom.Range("G9", "G15").Value
    WsTo.Range("I5", "I11") = WsFrom.Range("H9", "H15").Value
    WsTo.Range("J5", "J11") = WsFrom.Range("I9", "I15").Value
    WsTo.Range("K5", "K11") = WsFrom.Range("J9", "J15").Value
    WsTo.Range("L5", "L11") = WsFrom.Range("K9", "K15").Value
    
    'Close the openned source file
    Excel.Application.Workbooks(source).Close
    
End Sub

I hope someone can help.

Thanks!!!

JessBononi (BOB member since 2012-05-31)

2

Not something I’ve tried, but I’m not sure that xcelsius supports VBA like this.

I’ve always pulled excel data into a dashboard using an XML map connection.

Debbie

Debbie :uk: (BOB member since 2005-03-01)

3

Hi!

Thanks for the advice.

I don’t know how to do this =/.

I can transfer directly from a excel to the Xcelius using a xml?

Thanks!!!

JessBononi (BOB member since 2012-05-31)