• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

Please help. Has to be simple issue with Macro

2009_04_23.xls
2009_04_24.xls

This won't work:

Set wbkPos = Workbooks.Open("I:\Position Tool\" & UCase(Format(dtFlow, "yyyy") & "\" & Format(dtFlow, "yyyy_mm_") & _
    Left(Format(dtFlow, "MMM"), 4)) & "\" & Format(dtFlow, "yyyy_mm_dd") & ".xls")

I set the macro up to open 6 months worth of workbooks and grab data.  Worked perferctly, ran through monthly folders and all and just randomly stops on 4/24.  Help!
0
BigWill5112
Asked:
BigWill5112
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
BigWill5112Author Commented:
Anything I can do?


<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <logFileName>error034400_01.xml</logFileName>
  <summary>Errors were detected in file 'I:\Position Tool\2009\2009_04_APR\2009_04_24.xls'</summary>
- <additionalInfo>
  <info>Excel recovered your formulas and cell values, but some data may have been lost.</info>
  <info>Excel found errors that may cause some recovered data to be put in the wrong cells. Please check your recovered data carefully.</info>
  </additionalInfo>
  </recoveryLog>
0
 
Brad Sims, CCNANetwork AdministratorCommented:
You can include an "On Error Resume" statement in your code.  But from the error you posted, it appears there may be some issue with the spreadsheet.
0
 
DaveCommented:
Can you pls post your full code and at least two files, including the "bad" file for us to test. It does sound like corruption in the file being opened
You should never use "On Error Resume Next" unless you are trapping a deliberate outcome to be conditionally dealt with.
Cheers
Dave
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aikimarkCommented:
how many workbooks have been opened at the moment of failure?
0
 
BigWill5112Author Commented:
John15-16,
You are correct sir.  After spending about an hour staring at 2009_04_24.xls I decided to actually open it, lol.  I get the "Excel found unreadable content in '2009_04_24.xls'.  Do you want to recover the contents of this workbook?  If you trust the source of this workbook, click yes."  When I click yes I get a "Repairs to '2009_04_24.xls'" dialogue box that says Excel was able to open the file by repairing or removing the unreadable content.
- Excel recovered your formulas and cell values, but some data my have been lost.
- Excel found error that may cause some recovred data to be put in the wrong cells.  Please check you recovered data carefully.

Click the following to view your log file listing repairs:  Link

The file has lost all formatting.  Is there anyway to get it back?  What should I do.  I have opened it by repairing it ,but have yet to save the repaired copy.  Maybe there is nothing I can do...
0
 
DaveCommented:
> Maybe there is nothing I can do.

Correct.

If you have all the data (without foratting) then you are relatively fortunate

If the other files have an indentical layout you can just select the worksheets from the second file, copy the cells in the sheets, then paste special - formats, to apply the same formatting layout over your recovered file

Cheers

Dave
0
 
BigWill5112Author Commented:
I will follow brettdj's advice.
0
 
aikimarkCommented:
@BigWill5112

Your http:#31345634 comment is not a solution to this problem.  The correct allocation of points should be:
250 to John15-16 for comment http:#30977039
250 to bretdj for comment http:#31345634

The first comment pointed to a broken workbook.  The second comment allowed you to transfer the formatting.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now