-
VB Excel Problem
Hi everybody,
I have been having incredible problems with this function. It is part of a program and occasionally it runs fine, but most of the time it breaks and I get a message "running time error 9" or something like that. When I test it outside of the program it works fine.
It is a very simple function that takes 3 arguments: "History" excel file name (without ".xls"), "Productiv" excel file, and Path where the files could be sound. Then it compares content of each cells within one worksheet to the cells content of another worksheets, then it places "-" if cells are equal , and "NIO" if they are not equal in the third worksheet. Then it should search for "NIO" entries within third worksheet and return false if found or true if not found. Anotherwords if two worksheets are completely identical in content it returns true, otherwise false. Here is the code; I would appreciate any help, because I have spend a lot of time trying to figure it out. Thanks in advance.
-------------------- the Function ----------------------------------
Public Function BlattVergleich(sHistXlsDatei As String, sProdXlsDatei As String, sDateiPfad As String) As Boolean
Dim myRange As Range
Workbooks.Open FileName:=sDateiPfad & sHistXlsDatei & ".xls" 'open history file
Workbooks.Open FileName:=sDateiPfad & sProdXlsDatei & ".xls" 'open produktiv file
ActiveWindow.WindowState = xlNormal
Sheets(sProdXlsDatei).Select 'move history to productiv
Sheets(sProdXlsDatei).Move Before:=Workbooks(sHistXlsDatei & ".xls").Sheets(1)
Sheets(sProdXlsDatei).Select
Sheets(sProdXlsDatei).Name = "prod" ' Umbennenen die Blätte
Sheets(sHistXlsDatei).Select
Sheets(sHistXlsDatei).Name = "hist"
Sheets.Add ' create Vergleich sheet
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Vergleich"
Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(hist!RC=prod!RC, ""-"", ""NIO"")" ' compare hist vs. prod
Range("A1").Select
With Selection ' center Text
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom ' format text Lokalisierung
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = True ' format
Schriftart
Selection.Copy
Set myRange = Worksheets("Vergleich").Range("A1:AC350")
myRange.Select
ActiveSheet.Paste
Application.CutCopyMode = False
'If Application.CutCopyMode = xlCopy Then
' Application.CutCopyMode = False
'Else
' Application.CutCopyMode = False
' ActiveCell.FormulaR1C1 = "=IF(hist!RC=prod!RC, ""-"", ""NIO"")"
' Range("H29").Select
'End If
Sheets("Sheet1").Select
If Application.WorksheetFunction.CountIf(myRange, "NIO") > 0 Then
BlattVergleich = False
Else
BlattVergleich = True
End If
End Function
----------------------------- the use -----------------------------------
Program.....................................
Dim XlsApp As Object
Set XlsApp = CreateObject("Excel.Application")
If XlsApp.Visible = False Then
XlsApp.Visible = True ' Offen Excel Programm
End If
Dim IstGleich As Boolean
IstGleich = FehlerschwerpunkteObject.BlattVergleic (HistXlsName, ProdXlsName, sDateiOrdner)
.............................Program
-
Come on
-
