none
Error al Adjuntar Archivo RRS feed

  • Pregunta

  • hola

    En la aplicacion creo un archivo excel lo guardo y luego ejecuto este codigo para enviar el archivo me funciona perfecto....pero ahora tengo el problema que seguido de este proceso debo crear un nuevo archivo EXCEL (completamente diferente al que ya envie y en este path "C:\Dynamics_Reportes\Compras\RqImpresas\TrabajoFin\" & Me.nombreArchivo ) y luego escribi una rutina muy similar a la que dejo y en el momento de adjuntar el archivo aunque el path es diferente me dice que el archivo que trato de adjuntar esta en uso....Mi pregunta como puedo hacer para terminar con cualquier proceso desde mi codigo que este ligado al archivo  antes de adjuntarlo 

       Private Function EnviarNotificacionTrabajo() As Boolean
            Dim result As Boolean = False
            Dim msg As MailMessage = New MailMessage()
            Dim pathArchivo As String = "C:\Dynamics_Reportes\Compras\RqImpresas\TrabajoFin\" & Me.nombreArchivo
            Dim pathImage As String = Path.Combine(System.Windows.Forms.Application.StartupPath, "LogBlack.bmp")
            Dim client As System.Net.Mail.SmtpClient = New System.Net.Mail.SmtpClient()
            Dim listaAdm As List(Of String) = New List(Of String)
            Try
                msg.From = New MailAddress(EmailData("MAIL"))
                msg.Subject = BodyAsuntoMail("ASUNTO")
                msg.SubjectEncoding = System.Text.Encoding.UTF8
                msg.Body = BodyAsuntoMail("BODYIMAGE")
                msg.BodyEncoding = System.Text.Encoding.UTF8
                msg.IsBodyHtml = True
                Dim SQL As String = "SELECT MAIL FROM SSOWEB..ANALISTAMAIL WHERE STATUS = @PARAMETRO1 AND ( CREDENCIAL = @PARAMETRO2 or CREDENCIAL = @PARAMETRO3 or CREDENCIAL =@PARAMETRO4) AND DEPARTAMENTO = 'COMPRAS'"
                listaAdm = EmailCompras(SQL, "MAIL", "ACTIVO", "ADMINISTRADOR", "STANDART", "USUARIO")
                For Each item As String In listaAdm
                msg.To.Add(New MailAddress(item))
                Next
                msg.Attachments.Add(New Attachment(pathArchivo))//AQUI EL ERROR 
                'Imagen //////////////////////////////////////////////////////////////////////////////////////////////////////
                Dim contentID As String = Path.GetFileName(pathImage).Replace(".", "") & EmailData("ZOFM")
                Dim attachmentPath As String = pathImage
                Dim inline As New Attachment(attachmentPath)
                inline.ContentDisposition.Inline = True
                inline.ContentDisposition.DispositionType = DispositionTypeNames.Inline
                inline.ContentId = contentID
                inline.ContentType.MediaType = EmailData("IMAGEEXT")
                inline.ContentType.Name = Path.GetFileName(pathImage)
                msg.Attachments.Add(inline)
                msg.Body = msg.Body.Replace(EmailData("IMAGE"), EmailData("CID") & contentID) & BodyAsuntoMail("BODY")
                '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                client.Credentials = New System.Net.NetworkCredential(EmailData("USUARIO"), EmailData("CLAVE"))
                client.Port = 587
                client.Host = EmailData("SMTP")
                client.EnableSsl = True
                client.Send(msg)
                msg.Dispose()
                result = True
            Catch ex As Net.Mail.SmtpException
                cont = cont + 1
                If cont = 1 Then : result = EnviarNotificacionTrabajo()
                ElseIf cont > 1 Then : cont = 0 : result = False : MessageBox.Show(ex.ToString) : End If
            End Try
            Return result
        End Function


    EFRAIN MEJIAS C VALENCIA - VENEZUELA

    sábado, 15 de agosto de 2015 18:25

Respuestas

  • Hola,

    yo probaría con modificar desde el comienzo buscando la posible falla en donde se crea el archivo Excel.

    Por lo que pude apreciar en tu rutina que exporta el Excel, falta liberar las variables y la instancia del de la app Excel.  

    Prueba con este Codigo:

            Private Function ExportarInformacionOCAnulada()
            Dim result As Boolean = False
            Dim ValorD As DatosUsuario = DatosUsuario.Instance()
            Dim f As Integer = 1 : Dim n As Integer = 0
            Dim oExcelp As Object
            Dim oBook As Object
            Dim oSheet As Object
            CrearDirectorio("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend")
            Try
                oExcelp = CreateObject("Excel.Application")
                oBook = oExcelp.Workbooks.Add
                oSheet = oBook.Worksheets(1)
    
    
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'INSERTAR IMAGEN EN EXCEL
                Dim Celda As Object = oSheet.Cells(1, 1)
                Dim Izquierda As Single
                Dim Arriba As Single
                Dim Ancho As Double = Celda.Offset(0, 1).Left - Celda.Left
                Izquierda = Celda.Left + Ancho / 2 - 50 / 2
                If Izquierda < 1 Then Izquierda = 1
                Dim Alto As Double = Celda.Offset(1, 0).Top - Celda.Top
                Arriba = Celda.Top + Alto / 2 - 40 / 2
                If Arriba < 1 Then Arriba = 1
                oSheet.Shapes.AddPicture("C:\Dynamics_Reportes\Compras\LogBlack.bmp", False, True, Izquierda, Arriba, 50, 40)
    
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 14
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "SERVICIOS Y SUMINISTROS DE ORIENTE   SSO. C.A"
                oSheet.Range("B" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 14
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "RIF : J - 30300746 - 5 "
                oSheet.Range("B" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 12
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "LISTADO DE ORDENES DE COMPRA ANULADAS"
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 12
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "FECHA DE NOTIFICACION: " & DateTime.Now
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                Dim userent As String = ValorD.Usuario()
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("B" & f).Value = "USUARIO: " & userent.ToUpper()
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("B" & f, "M" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gainsboro)
                oSheet.Range("N" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 14
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("A" & f, "AF" & f).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                oSheet.Range("B" & f).ColumnWidth = 5
                oSheet.Range("C" & f).ColumnWidth = 20
                oSheet.Range("D" & f).ColumnWidth = 25
                oSheet.Range("E" & f).ColumnWidth = 90
                oSheet.Range("F" & f).ColumnWidth = 30
                oSheet.Range("G" & f).ColumnWidth = 90
                oSheet.Range("H" & f).ColumnWidth = 20
                oSheet.Range("I" & f).ColumnWidth = 20
                oSheet.Range("J" & f).ColumnWidth = 25
                oSheet.Range("K" & f).ColumnWidth = 25
                oSheet.Range("L" & f).ColumnWidth = 20
                oSheet.Range("M" & f).ColumnWidth = 20
    
                oSheet.Range("B" & f).Value = "Nº"
                oSheet.Range("C" & f).Value = "OC"
                oSheet.Range("D" & f).Value = "ORIGEN"
                oSheet.Range("E" & f).Value = "PROVEEDOR"
                oSheet.Range("F" & f).Value = "ARTICULO"
                oSheet.Range("G" & f).Value = "DESCRIPCION"
                oSheet.Range("H" & f).Value = "CANTIDAD"
                oSheet.Range("I" & f).Value = "UM"
                oSheet.Range("J" & f).Value = "P.UNIT"
                oSheet.Range("K" & f).Value = "P.TOT"
                oSheet.Range("L" & f).Value = "DPTO"
                oSheet.Range("M" & f).Value = "MONEDA"
                Dim ITEMDESC As String = Nothing
                f = f + 1
                For Each row As DataRow In Me.t.Rows
                    oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                    oSheet.Range("A" & f, "M" & f).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft
                    n = n + 1
                    oSheet.Range("B" & f).Value = n.ToString
                    If Not IsDBNull(row("PONUMBER")) Then
                        oSheet.Range("C" & f).Value = CStr(row("PONUMBER"))
                    End If
                    If Not IsDBNull(row("CONFIRM1")) Then
                        oSheet.Range("D" & f).Value = CStr(row("CONFIRM1")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("VENDNAME")) Then
                        oSheet.Range("E" & f).Value = CStr(row("VENDNAME")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("ITEMNMBR")) Then
                        oSheet.Range("F" & f).Value = CStr(row("ITEMNMBR")).ToString.Trim.ToUpper
                    End If
                    If row("CONFIRM1").ToString Like "RDA" Then : ITEMDESC = "VNDITDSC" : Else : ITEMDESC = "ITEMDESC" : End If
                    If Not IsDBNull(row(ITEMDESC)) Then
                        oSheet.Range("G" & f).Value = CStr(row(ITEMDESC)).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("QTYORDER")) Then
                        oSheet.Range("H" & f).Value = CDec(row("QTYORDER"))
                        oSheet.Range("H" & f).NumberFormat = "#,##0.00"
                    End If
                    If Not IsDBNull(row("UOFM")) Then
                        oSheet.Range("I" & f).Value = CStr(row("UOFM")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("ORUNTCST")) Then
                        oSheet.Range("J" & f).Value = CDec(row("ORUNTCST")).ToString("N2")
                    End If
                    If Not IsDBNull(row("OREXTCST")) Then
                        oSheet.Range("K" & f).Value = CDec(row("OREXTCST")).ToString("N2")
                    End If
                    If Not IsDBNull(row("BUYERID")) Then
                        oSheet.Range("L" & f).Value = CStr(row("BUYERID")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("CURNCYID")) Then
                        oSheet.Range("M" & f).Value = CStr(row("CURNCYID")).ToString.Trim.ToUpper
                    End If
                    f = f + 1
                Next
    
                For I = f To f + 5 Step 1 : oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White) : f = f + 1 : Next
    
                oExcelp.ActiveWindow.Zoom = 75
                oExcelp.Rows.AutoFit()
                oExcelp.UserControl = True
                'Encontrar clave que bloquea el archivo
                Dim master As String = Nothing : master = PassWordMaestro(master)
                If master <> Nothing Then
                    oSheet.Protect(Password:=master, DrawingObjects:=True, Contents:=True, Scenarios:=True)
                    oSheet.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoSelection
                End If
                Me.nombreArchivo = NombreFile("EXCEL")
                Dim existe As Boolean = ExisteDirectorio("EXCEL")
                Try
                    If Not existe Then
                        oBook.SaveAs("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend\" & Me.nombreArchivo)
    
                    Else
                        System.IO.File.Delete("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend\" & Me.nombreArchivo)
                        oExcelp.UserControl = True
                        oBook.SaveAs("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend\" & Me.nombreArchivo)
    
                    End If
                Catch ex As System.Exception : End Try
    
    
                oExcelp = Nothing
                oBook = Nothing
                oSheet = Nothing
    
                result = True
            Catch ex As System.Exception : End Try
            Return result
        End Function

     

    Saludos desde Chile.

    domingo, 16 de agosto de 2015 2:05

Todas las respuestas

  •             msg.Attachments.Add(New Attachment(pathArchivo))//AQUI EL ERROR 


    Hola nos puedes pegar exactamente el error de excepción que se te produce?


    Saludos desde Chile.

    domingo, 16 de agosto de 2015 1:08
  • También para tener otro antecedente adjuntanos la solución que tienes para crear el archivo Excel.

    Saludos desde Chile.

    domingo, 16 de agosto de 2015 1:11
  • El error dice : No c puede tener acceso al archivo por q esta siendo utilizado por otro proceso

    el codigo donde creo el archivo

     Private Function ExportarInformacionOCAnulada()
            Dim result As Boolean = False
            Dim ValorD As DatosUsuario = DatosUsuario.Instance()
            Dim f As Integer = 1 : Dim n As Integer = 0
            Dim oExcelp As Object
            Dim oBook As Object
            Dim oSheet As Object
            CrearDirectorio("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend")
            Try
                oExcelp = CreateObject("Excel.Application")
                oBook = oExcelp.Workbooks.Add
                oSheet = oBook.Worksheets(1)


                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'INSERTAR IMAGEN EN EXCEL
                Dim Celda As Object = oSheet.Cells(1, 1)
                Dim Izquierda As Single
                Dim Arriba As Single
                Dim Ancho As Double = Celda.Offset(0, 1).Left - Celda.Left
                Izquierda = Celda.Left + Ancho / 2 - 50 / 2
                If Izquierda < 1 Then Izquierda = 1
                Dim Alto As Double = Celda.Offset(1, 0).Top - Celda.Top
                Arriba = Celda.Top + Alto / 2 - 40 / 2
                If Arriba < 1 Then Arriba = 1
                oSheet.Shapes.AddPicture("C:\Dynamics_Reportes\Compras\LogBlack.bmp", False, True, Izquierda, Arriba, 50, 40)

                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 14
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "SERVICIOS Y SUMINISTROS DE ORIENTE   SSO. C.A"
                oSheet.Range("B" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 14
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "RIF : J - 30300746 - 5 "
                oSheet.Range("B" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 12
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "LISTADO DE ORDENES DE COMPRA ANULADAS"
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 12
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "FECHA DE NOTIFICACION: " & DateTime.Now
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                Dim userent As String = ValorD.Usuario()
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("B" & f).Value = "USUARIO: " & userent.ToUpper()
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("B" & f, "M" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gainsboro)
                oSheet.Range("N" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 14
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("A" & f, "AF" & f).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                oSheet.Range("B" & f).ColumnWidth = 5
                oSheet.Range("C" & f).ColumnWidth = 20
                oSheet.Range("D" & f).ColumnWidth = 25
                oSheet.Range("E" & f).ColumnWidth = 90
                oSheet.Range("F" & f).ColumnWidth = 30
                oSheet.Range("G" & f).ColumnWidth = 90
                oSheet.Range("H" & f).ColumnWidth = 20
                oSheet.Range("I" & f).ColumnWidth = 20
                oSheet.Range("J" & f).ColumnWidth = 25
                oSheet.Range("K" & f).ColumnWidth = 25
                oSheet.Range("L" & f).ColumnWidth = 20
                oSheet.Range("M" & f).ColumnWidth = 20

                oSheet.Range("B" & f).Value = "Nº"
                oSheet.Range("C" & f).Value = "OC"
                oSheet.Range("D" & f).Value = "ORIGEN"
                oSheet.Range("E" & f).Value = "PROVEEDOR"
                oSheet.Range("F" & f).Value = "ARTICULO"
                oSheet.Range("G" & f).Value = "DESCRIPCION"
                oSheet.Range("H" & f).Value = "CANTIDAD"
                oSheet.Range("I" & f).Value = "UM"
                oSheet.Range("J" & f).Value = "P.UNIT"
                oSheet.Range("K" & f).Value = "P.TOT"
                oSheet.Range("L" & f).Value = "DPTO"
                oSheet.Range("M" & f).Value = "MONEDA"
                Dim ITEMDESC As String = Nothing
                f = f + 1
                For Each row As DataRow In Me.t.Rows
                    oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                    oSheet.Range("A" & f, "M" & f).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft
                    n = n + 1
                    oSheet.Range("B" & f).Value = n.ToString
                    If Not IsDBNull(row("PONUMBER")) Then
                        oSheet.Range("C" & f).Value = CStr(row("PONUMBER"))
                    End If
                    If Not IsDBNull(row("CONFIRM1")) Then
                        oSheet.Range("D" & f).Value = CStr(row("CONFIRM1")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("VENDNAME")) Then
                        oSheet.Range("E" & f).Value = CStr(row("VENDNAME")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("ITEMNMBR")) Then
                        oSheet.Range("F" & f).Value = CStr(row("ITEMNMBR")).ToString.Trim.ToUpper
                    End If
                    If row("CONFIRM1").ToString Like "RDA" Then : ITEMDESC = "VNDITDSC" : Else : ITEMDESC = "ITEMDESC" : End If
                    If Not IsDBNull(row(ITEMDESC)) Then
                        oSheet.Range("G" & f).Value = CStr(row(ITEMDESC)).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("QTYORDER")) Then
                        oSheet.Range("H" & f).Value = CDec(row("QTYORDER"))
                        oSheet.Range("H" & f).NumberFormat = "#,##0.00"
                    End If
                    If Not IsDBNull(row("UOFM")) Then
                        oSheet.Range("I" & f).Value = CStr(row("UOFM")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("ORUNTCST")) Then
                        oSheet.Range("J" & f).Value = CDec(row("ORUNTCST")).ToString("N2")
                    End If
                    If Not IsDBNull(row("OREXTCST")) Then
                        oSheet.Range("K" & f).Value = CDec(row("OREXTCST")).ToString("N2")
                    End If
                    If Not IsDBNull(row("BUYERID")) Then
                        oSheet.Range("L" & f).Value = CStr(row("BUYERID")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("CURNCYID")) Then
                        oSheet.Range("M" & f).Value = CStr(row("CURNCYID")).ToString.Trim.ToUpper
                    End If
                    f = f + 1
                Next

                For I = f To f + 5 Step 1 : oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White) : f = f + 1 : Next

                oExcelp.ActiveWindow.Zoom = 75
                oExcelp.Rows.AutoFit()
                oExcelp.UserControl = True
                'Encontrar clave que bloquea el archivo
                Dim master As String = Nothing : master = PassWordMaestro(master)
                If master <> Nothing Then
                    oSheet.Protect(Password:=master, DrawingObjects:=True, Contents:=True, Scenarios:=True)
                    oSheet.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoSelection
                End If
                Me.nombreArchivo = NombreFile("EXCEL")
                Dim existe As Boolean = ExisteDirectorio("EXCEL")
                Try
                    If Not existe Then
                        oBook.SaveAs("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend\" & Me.nombreArchivo)
                      
                    Else
                        System.IO.File.Delete("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend\" & Me.nombreArchivo)
                        oBook.SaveAs("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend\" & Me.nombreArchivo)
                      
                    End If
                Catch ex As System.Exception : End Try
                oExcelp.UserControl = True
                oExcelp.Quit()
                result = True
            Catch ex As System.Exception : End Try
            Return result
        End Function


    EFRAIN MEJIAS C VALENCIA - VENEZUELA

    domingo, 16 de agosto de 2015 1:26
  • Creo que donde guardo el archivo en el SaveAs me falta algo que libere el archivo de la aplicacion

    EFRAIN MEJIAS C VALENCIA - VENEZUELA

    domingo, 16 de agosto de 2015 1:28
  • Hola,

    yo probaría con modificar desde el comienzo buscando la posible falla en donde se crea el archivo Excel.

    Por lo que pude apreciar en tu rutina que exporta el Excel, falta liberar las variables y la instancia del de la app Excel.  

    Prueba con este Codigo:

            Private Function ExportarInformacionOCAnulada()
            Dim result As Boolean = False
            Dim ValorD As DatosUsuario = DatosUsuario.Instance()
            Dim f As Integer = 1 : Dim n As Integer = 0
            Dim oExcelp As Object
            Dim oBook As Object
            Dim oSheet As Object
            CrearDirectorio("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend")
            Try
                oExcelp = CreateObject("Excel.Application")
                oBook = oExcelp.Workbooks.Add
                oSheet = oBook.Worksheets(1)
    
    
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'INSERTAR IMAGEN EN EXCEL
                Dim Celda As Object = oSheet.Cells(1, 1)
                Dim Izquierda As Single
                Dim Arriba As Single
                Dim Ancho As Double = Celda.Offset(0, 1).Left - Celda.Left
                Izquierda = Celda.Left + Ancho / 2 - 50 / 2
                If Izquierda < 1 Then Izquierda = 1
                Dim Alto As Double = Celda.Offset(1, 0).Top - Celda.Top
                Arriba = Celda.Top + Alto / 2 - 40 / 2
                If Arriba < 1 Then Arriba = 1
                oSheet.Shapes.AddPicture("C:\Dynamics_Reportes\Compras\LogBlack.bmp", False, True, Izquierda, Arriba, 50, 40)
    
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 14
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "SERVICIOS Y SUMINISTROS DE ORIENTE   SSO. C.A"
                oSheet.Range("B" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 14
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "RIF : J - 30300746 - 5 "
                oSheet.Range("B" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 12
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "LISTADO DE ORDENES DE COMPRA ANULADAS"
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 12
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("B" & f).Value = "FECHA DE NOTIFICACION: " & DateTime.Now
                f = f + 1
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                Dim userent As String = ValorD.Usuario()
                oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("B" & f).Value = "USUARIO: " & userent.ToUpper()
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("A" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Range("A" & f, "AF" & f).Merge(True)
                f = f + 1
                oSheet.Range("B" & f, "M" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gainsboro)
                oSheet.Range("N" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                oSheet.Rows.Item(f).Font.Size = 14
                oSheet.Rows.Item(f).Font.Bold = 1
                oSheet.Range("A" & f, "AF" & f).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                oSheet.Range("B" & f).ColumnWidth = 5
                oSheet.Range("C" & f).ColumnWidth = 20
                oSheet.Range("D" & f).ColumnWidth = 25
                oSheet.Range("E" & f).ColumnWidth = 90
                oSheet.Range("F" & f).ColumnWidth = 30
                oSheet.Range("G" & f).ColumnWidth = 90
                oSheet.Range("H" & f).ColumnWidth = 20
                oSheet.Range("I" & f).ColumnWidth = 20
                oSheet.Range("J" & f).ColumnWidth = 25
                oSheet.Range("K" & f).ColumnWidth = 25
                oSheet.Range("L" & f).ColumnWidth = 20
                oSheet.Range("M" & f).ColumnWidth = 20
    
                oSheet.Range("B" & f).Value = "Nº"
                oSheet.Range("C" & f).Value = "OC"
                oSheet.Range("D" & f).Value = "ORIGEN"
                oSheet.Range("E" & f).Value = "PROVEEDOR"
                oSheet.Range("F" & f).Value = "ARTICULO"
                oSheet.Range("G" & f).Value = "DESCRIPCION"
                oSheet.Range("H" & f).Value = "CANTIDAD"
                oSheet.Range("I" & f).Value = "UM"
                oSheet.Range("J" & f).Value = "P.UNIT"
                oSheet.Range("K" & f).Value = "P.TOT"
                oSheet.Range("L" & f).Value = "DPTO"
                oSheet.Range("M" & f).Value = "MONEDA"
                Dim ITEMDESC As String = Nothing
                f = f + 1
                For Each row As DataRow In Me.t.Rows
                    oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                    oSheet.Range("A" & f, "M" & f).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft
                    n = n + 1
                    oSheet.Range("B" & f).Value = n.ToString
                    If Not IsDBNull(row("PONUMBER")) Then
                        oSheet.Range("C" & f).Value = CStr(row("PONUMBER"))
                    End If
                    If Not IsDBNull(row("CONFIRM1")) Then
                        oSheet.Range("D" & f).Value = CStr(row("CONFIRM1")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("VENDNAME")) Then
                        oSheet.Range("E" & f).Value = CStr(row("VENDNAME")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("ITEMNMBR")) Then
                        oSheet.Range("F" & f).Value = CStr(row("ITEMNMBR")).ToString.Trim.ToUpper
                    End If
                    If row("CONFIRM1").ToString Like "RDA" Then : ITEMDESC = "VNDITDSC" : Else : ITEMDESC = "ITEMDESC" : End If
                    If Not IsDBNull(row(ITEMDESC)) Then
                        oSheet.Range("G" & f).Value = CStr(row(ITEMDESC)).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("QTYORDER")) Then
                        oSheet.Range("H" & f).Value = CDec(row("QTYORDER"))
                        oSheet.Range("H" & f).NumberFormat = "#,##0.00"
                    End If
                    If Not IsDBNull(row("UOFM")) Then
                        oSheet.Range("I" & f).Value = CStr(row("UOFM")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("ORUNTCST")) Then
                        oSheet.Range("J" & f).Value = CDec(row("ORUNTCST")).ToString("N2")
                    End If
                    If Not IsDBNull(row("OREXTCST")) Then
                        oSheet.Range("K" & f).Value = CDec(row("OREXTCST")).ToString("N2")
                    End If
                    If Not IsDBNull(row("BUYERID")) Then
                        oSheet.Range("L" & f).Value = CStr(row("BUYERID")).ToString.Trim.ToUpper
                    End If
                    If Not IsDBNull(row("CURNCYID")) Then
                        oSheet.Range("M" & f).Value = CStr(row("CURNCYID")).ToString.Trim.ToUpper
                    End If
                    f = f + 1
                Next
    
                For I = f To f + 5 Step 1 : oSheet.Range("A" & f, "AF" & f).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White) : f = f + 1 : Next
    
                oExcelp.ActiveWindow.Zoom = 75
                oExcelp.Rows.AutoFit()
                oExcelp.UserControl = True
                'Encontrar clave que bloquea el archivo
                Dim master As String = Nothing : master = PassWordMaestro(master)
                If master <> Nothing Then
                    oSheet.Protect(Password:=master, DrawingObjects:=True, Contents:=True, Scenarios:=True)
                    oSheet.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoSelection
                End If
                Me.nombreArchivo = NombreFile("EXCEL")
                Dim existe As Boolean = ExisteDirectorio("EXCEL")
                Try
                    If Not existe Then
                        oBook.SaveAs("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend\" & Me.nombreArchivo)
    
                    Else
                        System.IO.File.Delete("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend\" & Me.nombreArchivo)
                        oExcelp.UserControl = True
                        oBook.SaveAs("C:\Dynamics_Reportes\Compras\RqImpresas\OcSend\" & Me.nombreArchivo)
    
                    End If
                Catch ex As System.Exception : End Try
    
    
                oExcelp = Nothing
                oBook = Nothing
                oSheet = Nothing
    
                result = True
            Catch ex As System.Exception : End Try
            Return result
        End Function

     

    Saludos desde Chile.

    domingo, 16 de agosto de 2015 2:05