Replies

I have an interesting delimma; when I right click on it I receive the following error message.

Is there a fix to the registery that can be made?

Thanks,
John

Yes, It is Excel 2003.

Regards,
John

I found something online:

Code:

Public Function EOMonth(InputDate As Date, Optional MonthsToAdd As Integer)
' Returns the date of the last day of month, a specified number of months
' following a given date.
   Dim TotalMonths As Integer
   Dim NewMonth As Integer
   Dim NewYear As Integer

   If IsMissing(MonthsToAdd) Then
      MonthsToAdd = 0
   End If

   TotalMonths = Month(InputDate) + MonthsToAdd
   NewMonth = TotalMonths - (12 * Int(TotalMonths / 12))
   NewYear = Year(InputDate) + Int(TotalMonths / 12)

   If NewMonth = 0 Then
      NewMonth = 12
      NewYear = NewYear - 1
   End If

   Select Case NewMonth
      Case 1, 3, 5, 7, 8, 10, 12
         EOMonth = DateSerial(NewYear, NewMonth, 31)
      Case 4, 6, 9, 11
         EOMonth = DateSerial(NewYear, NewMonth, 30)
      Case 2
         If Int(NewYear / 4) = NewYear / 4 Then
            EOMonth = DateSerial(NewYear, NewMonth, 29)
         Else
            EOMonth = DateSerial(NewYear, NewMonth, 28)
         End If
   End Select
End Function

Courtney,

Thanks for your response. Your suggestion only addresses the backup process of the Time Capsule; pointing to another drive and creating sparsebundles.

If you recall, TC can also be used to store data, music and etc.. With this in mind I am looking for a means of creating an entire drive image of the Time Capsule.

Regards,
John

Thanks for your suggestion..it worked.

Regards,
John

Try this (I’ve highlighted the separate SUBSTITUTE functions:

=SUBSTITUTE(SUBSTITUTE(A1,”SwapText_01″,””),”SwapText_02″,””)

Works perfectly.

Thank you,
John

Hans,

Thanks for the suggestion.

John

Hans,

I tried your suggestion and it returned a “#Value”; referencing another file. I then tried it within the same workbook; different sheet and received the #Value as well.

Regards,
John

You probably haven’t followed Steve’s instructions entirely correctly. See the attached version.

Sorry, my error as I did not leave F1 blank as instructed by Steve. All is well.

Thanks Steve and Hans,
John

Set your criteria to only F1:F2

F1 should be blank (since F2 will have a formula)
F2 should have:
=NOT(ISBLANK(B2))

Steve

Hi Steve,

I tried your suggestion without success. The end result of your suggestion lists all the items and does not pare down the information to what I have highlighted in green.

Regards,
John

I tried it on my PC, and it worked fine with spaces in the file name.
Are some of the files read-only, perhaps?

Great observation! Now I’m embarrassed.

I removed the read-only and it worked.

Thanks again.
John

Try specifying the full path:

Kill “C:MyFolderMySubfolderCopy of*.xls”

Han,
I should have been more clear as to what I’m trying to achieve.

oPath = “C:MyFolderMySubfolder”
oEraseFiles = “Copy of*.xls”

Kill oPath & oEraseFiles

The error message I receive is “Path/File” access error. I believe this to be related to the space.

Regards,
John

I’m sorry, I can’t explain that. What happens if you single-step through the code?

Stepping through the code provides enough time for the file attribute to change. All other lines of code run as expected.

Seem to be a timing difference in seconds.

Regards,
John

Hans,

It’s still happening.

My code example is:

[codebox]’File Attributes
FileAttribute = GetAttr(sPathOfSourceFile & strFileName) And vbHidden

If FileAttribute = 2 Then
SetAttr (sPathOfSourceFile & strFileName), vbNormal
Do While GetAttr(sPathOfSourceFile & strFileName) 0
DoEvents
Loop
End If
[/codebox]

Regards,
John

A non macro method (I’m using 2003 – Due to limitations of the Find routine, this method will not work in earlier versions):

Select the range you want to affect
Ctrl F to launch Find dialog
enter the data to find, check the match entire cell option
Click Find All
Highlight the results that appear
Click Close
Ctrl+”-” (launches delete dialog) choose Entire Row
OK out

You go me to think this out a little more. If one could pass the cell addresses to an array and then delete the each row of the array that may work.

If you select a number of cells by using the Ctrl Key and left click; one can delete all rows at the same time.

Thanks for taking another look,
John