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:D,4,0)),0,VLOOKUP("2006-05",Event2!
A:D,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

Hi

This is a wild stab in the dark, maybe you need to force a
recalculation. See if adding "Application.Calculate" at the end of you
macro helps any.

hth

keith

See your other post, too.

ricowyder wrote:

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:D,4,0)),0,VLOOKUP("2006-05",Event2!
A:D,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

--

Dave Peterson