Ranking Cells Causing Excel File to Corrupt

[RonMcNatt]

Here's a Strange One . . .

I'm exporting a data table out of Microsoft Access into an Excel file using the TransferSpreadsheet function. There is no problems with this export.

Once the data comes into the Spreadsheet, I can perform "Sum" and other basic functions without a problem. I can retransfer new data to the spreadsheet without a problem.

However, if I add the "Ranking" Function to any of the data, when I retransfer the data from Access, the Excel file become corrupt and will not open.

Example:

Data Transferred: Sum Rank
10 10 20 3
15 15 30 2
20 20 40 1

I have tried every trick I can think of, any thoughts would be greatly apprecriated.

Ron McNatt

 

[xlbo]

Can't say I've ever had this problem before but have you tried saving the excel file before applying the ranking ??
Other than that, all I can think of is getting the data FROM excel by using a pivot table / Data>Get External Data.... Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[RonMcNatt]

Thanks for the comment, I have made sure to save the files before and after any changes. In regards to the pivot table, don't think it will solve my problem, but thanks again for the suggestion.

 

[DaleWatson123321]

Ron,

Here's a routine that should work. I've tested it and it works for me.

The approach used here is to eliminate the formulas prior to merging your Access data, and then the routine creates and copies both the SUM formula and the RANK formula for whatever number of records are imported.

Sub Merge_Data()
Application.ScreenUpdating = False
Clear_Formulas
'include your routine here for merging Access data
Copy_SumFormula
Copy_RankFormula
Application.CutCopyMode = False
Application.Goto Reference:="R1C1"
Application.ScreenUpdating = True
End Sub

Sub Clear_Formulas()
Application.Goto Reference:="R2C3"
FirstCell = ActiveCell.Address
LastCell = [A65536].End(xlUp).Offset(0, 3).Address
rng = FirstCell & " : " & LastCell
Range(rng).Name = "frmrange"
Range("frmrange&quot.ClearContents
End Sub

Sub Copy_SumFormula()
Application.Goto Reference:="R2C3"
ActiveCell.Formula = "=SUM(A2:B2)"
ActiveCell.Name = "frm"
FirstCell = ActiveCell.Address
LastCell = [A65536].End(xlUp).Offset(0, 2).Address
rng = FirstCell & " : " & LastCell
Range(rng).Name = "sumrng"
Range("frm&quot.Copy
Range("sumrng&quot.Select
ActiveSheet.Paste
End Sub

Sub Copy_RankFormula()
Application.Goto Reference:="R2C4"
ActiveCell.Formula = "=RANK(C2,sumrng)"
ActiveCell.Name = "frm2"
ActiveCell.Offset(1, 0).Select
FirstCell = ActiveCell.Address
LastCell = [A65536].End(xlUp).Offset(0, 3).Address
rng = FirstCell & " : " & LastCell
Range(rng).Name = "rankrng"
Range("frm2&quot.Copy
Range("rankrng&quot.Select
ActiveSheet.Paste
End Sub

I hope this works for you. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca