R
ricowyder
Dear users,
I have posted this also in microsoft.public.excel - since I really
need an expert not only in Excel but maybe also in macro programming,
I thought, I post it here, too. I was confronted with a very strange
problem. Can anybody help?
Thanks a lot
Rico
HERE WE GO:
Basically, I have 3 worksheets:
No. 1 contains all data
No. 2 contains all filtered data (macro special filter from No. 1)
No. 3 is the output, graphically optimized sheet, which has fixed
formulas to No. 2 (e.g. =Event1!A1)
Imagine, I had only these 3 sheets. Actually, I have many of them, but
there are all paired as No. 1 and 2.
No. 3 is the main sheets, which links too all No. 2's.
To update the file, I have created a macro, which compares one excel
file with the actual one. All worksheets named similarily are
replaced. The macro only looks for No. 1's and replaces all of them.
Since I have other sheets in this file, which are for another purpose,
I had to replace them by looking for the same Worksheet names.
Hence all No. 1 are replaced. No. 2 are not touched at all. Special
filter works still fine afterwards.
However, my cells in No. 3 show all 0 !!!!!
Here are two examples of formulas that both show "0":
=Event6!C2
=IF(ISNA(VLOOKUP("2006-05",Event2!A
,4,0)),0,VLOOKUP("2006-05",Event2!
A,4,0))
Fascinatingly, when I click into the cell and press enter, all of a
sudden the true value appears.
I need an expert! Please help!
Here is the macro code of replacing data:
Public Sub ImportAndReplaceMISData()
Dim bStatusBarWasShown As Boolean
bStatusBarWasShown = Application.DisplayStatusBar
Application.DisplayStatusBar = True 'show status bar
' get file path of new data file
Application.StatusBar = "Choose data file..."
Dim vNewDataFilePath As Variant
vNewDataFilePath = Application.GetOpenFilename("Excel Files
(*.xls), *.xls")
If vNewDataFilePath = False Then
Application.StatusBar = False 'reset status bar
Application.DisplayStatusBar = bStatusBarWasShown
Exit Sub
End If
'keep reference to KAO workbook
Dim wbKAO As Workbook
Set wbKAO = ActiveWorkbook
'open new data file
Application.StatusBar = "Opening data file..."
Dim wbNewData As Workbook
Set wbNewData = Application.Workbooks.Open(vNewDataFilePath)
If wbNewData Is Nothing Then
Application.StatusBar = "Error opening data file!"
MsgBox "Error opening data file!", vbOKOnly Or vbCritical,
"Data File Error!"
Application.StatusBar = False 'reset status bar
Application.DisplayStatusBar = bStatusBarWasShown
Exit Sub
End If
'loop over every sheet of the new data file
' > backup and replace sheets with outdated data
' > add new sheets
On Error GoTo UpdateData_CopyError
Application.StatusBar = "Replacing/copying data..."
Application.DisplayAlerts = False
Dim tNow As Date
tNow = Now
Dim sBackupName As String
sBackupName = "_Backup_" & Format(tNow, "yyyymmdd_hhmmss")
Dim wsNewData As Worksheet
For Each wsNewData In wbNewData.Worksheets
'try to replace old data
Dim bReplaced As Boolean
bReplaced = False
Dim wsOldData As Worksheet
For Each wsOldData In wbKAO.Worksheets
If wsOldData.Name = wsNewData.Name Then
wsOldData.Name = wsOldData.Name & sBackupName 'backup
old data
wsNewData.Copy After:=wsOldData
wsOldData.Delete
bReplaced = True
Exit For
End If
Next
'if no old data to replace, copy it
If Not bReplaced Then
wsNewData.Copy
After:=wbKAO.Worksheets(wbKAO.Worksheets.Count)
End If
Next
'finally, close the new data file and reset Excel
UpdateData_CopyError:
wbNewData.Close
Application.DisplayAlerts = True 'reset alerts
Application.StatusBar = False 'reset status bar
Application.DisplayStatusBar = bStatusBarWasShown
End Sub
I have posted this also in microsoft.public.excel - since I really
need an expert not only in Excel but maybe also in macro programming,
I thought, I post it here, too. I was confronted with a very strange
problem. Can anybody help?
Thanks a lot
Rico
HERE WE GO:
Basically, I have 3 worksheets:
No. 1 contains all data
No. 2 contains all filtered data (macro special filter from No. 1)
No. 3 is the output, graphically optimized sheet, which has fixed
formulas to No. 2 (e.g. =Event1!A1)
Imagine, I had only these 3 sheets. Actually, I have many of them, but
there are all paired as No. 1 and 2.
No. 3 is the main sheets, which links too all No. 2's.
To update the file, I have created a macro, which compares one excel
file with the actual one. All worksheets named similarily are
replaced. The macro only looks for No. 1's and replaces all of them.
Since I have other sheets in this file, which are for another purpose,
I had to replace them by looking for the same Worksheet names.
Hence all No. 1 are replaced. No. 2 are not touched at all. Special
filter works still fine afterwards.
However, my cells in No. 3 show all 0 !!!!!
Here are two examples of formulas that both show "0":
=Event6!C2
=IF(ISNA(VLOOKUP("2006-05",Event2!A
,4,0)),0,VLOOKUP("2006-05",Event2!A
,4,0))Fascinatingly, when I click into the cell and press enter, all of a
sudden the true value appears.
I need an expert! Please help!
Here is the macro code of replacing data:
Public Sub ImportAndReplaceMISData()
Dim bStatusBarWasShown As Boolean
bStatusBarWasShown = Application.DisplayStatusBar
Application.DisplayStatusBar = True 'show status bar
' get file path of new data file
Application.StatusBar = "Choose data file..."
Dim vNewDataFilePath As Variant
vNewDataFilePath = Application.GetOpenFilename("Excel Files
(*.xls), *.xls")
If vNewDataFilePath = False Then
Application.StatusBar = False 'reset status bar
Application.DisplayStatusBar = bStatusBarWasShown
Exit Sub
End If
'keep reference to KAO workbook
Dim wbKAO As Workbook
Set wbKAO = ActiveWorkbook
'open new data file
Application.StatusBar = "Opening data file..."
Dim wbNewData As Workbook
Set wbNewData = Application.Workbooks.Open(vNewDataFilePath)
If wbNewData Is Nothing Then
Application.StatusBar = "Error opening data file!"
MsgBox "Error opening data file!", vbOKOnly Or vbCritical,
"Data File Error!"
Application.StatusBar = False 'reset status bar
Application.DisplayStatusBar = bStatusBarWasShown
Exit Sub
End If
'loop over every sheet of the new data file
' > backup and replace sheets with outdated data
' > add new sheets
On Error GoTo UpdateData_CopyError
Application.StatusBar = "Replacing/copying data..."
Application.DisplayAlerts = False
Dim tNow As Date
tNow = Now
Dim sBackupName As String
sBackupName = "_Backup_" & Format(tNow, "yyyymmdd_hhmmss")
Dim wsNewData As Worksheet
For Each wsNewData In wbNewData.Worksheets
'try to replace old data
Dim bReplaced As Boolean
bReplaced = False
Dim wsOldData As Worksheet
For Each wsOldData In wbKAO.Worksheets
If wsOldData.Name = wsNewData.Name Then
wsOldData.Name = wsOldData.Name & sBackupName 'backup
old data
wsNewData.Copy After:=wsOldData
wsOldData.Delete
bReplaced = True
Exit For
End If
Next
'if no old data to replace, copy it
If Not bReplaced Then
wsNewData.Copy
After:=wbKAO.Worksheets(wbKAO.Worksheets.Count)
End If
Next
'finally, close the new data file and reset Excel
UpdateData_CopyError:
wbNewData.Close
Application.DisplayAlerts = True 'reset alerts
Application.StatusBar = False 'reset status bar
Application.DisplayStatusBar = bStatusBarWasShown
End Sub