Re: Write Protection Of Data
You can load info from a file without opening...there is a "workaround" on this site. Just do a search.
However, I do not think you can import information back to a closed file. It has to be opened at some point.
Re: Write Protection Of Data
You can load info from a file without opening...there is a "workaround" on this site. Just do a search.
However, I do not think you can import information back to a closed file. It has to be opened at some point.
Re: Vlookup Results
In order for vlookup to work as well...i think you need the search column in ascending order. (i think).
Re: Write Protection Of Data
How can you have mulitple people reading and writing to the same file? won't it be read-only when the second user tries to access this data?
I think your going to have permisson problems here.
Or are you just opening>loading data> then closing?
Re: Copy Directory If File Exists
nevermind. Change "copyfolder' to "copyfile"
Thanks Again!!!!
Re: Copy Directory If File Exists
Thanks Bob,
One problem though. It only copies the directories under the root folder, but doesn't copy any files that live in the root folder.
I tried playing around with the path a little, but nothing seemed to work.
Any suggestions?
Re: Macro To Save File And Automatically Overwrite Old File
Sub SaveIt()
Dim strFirst As String, strLast As String
ChDir "C:\Documents and Settings\All Users\Desktop\TNT Desktop Locker\"
'*****************
Application.DisplayAlerts = False
'*****************
With Worksheets(1)
strFirst = .Range("C4")
strLast = .Range("C6")
End With
ThisWorkbook.SaveAs _
"C:\Documents and Settings\All Users\Desktop\TNT Desktop Locker\" & _
strFirst & " -TNT- " & strLast, xlWorkbookNormal
'*****************
Application.DisplayAlerts = True
'*****************
End Sub
Display More
[/QUOTE]
Re: Run-time Error Every Other Time The Macro Runs
It a nut shell...it forces you to declare all of your variables. Its not necessary, but I reccomend it. It helps if you make a simple error...will trap it for you.
Re: Run-time Error Every Other Time The Macro Runs
Ok. I did a little damage to it. Tough to edit some of the code since I wasn't quite sure what it was doing. (you had a few areas where you pasted twice, so wasn't sure if it was a mistake and did the best I could to decipher.)
This is untested. I suggest not overwriting your original code and just set this in a new module. I renamed with a "UD" for updated.
Option Explicit
Sub YesRegenUD()
' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
' the log, generates the new file and attaches it to an email
Application.Run "LogUnprotect"
'***************************************
Application.Run "RegenFormUnprotect" ' moved this to top
'***************************************
Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy Sheets("Regenerate Request").Range("A40")
'
' This sets all of the formulas in the Regenerated Sheet
With Sheets("Regenerate Request")
Range("C12").Formula = "=D40"
Range("C14").Formula = "=E40"
Range("C16").Formula = "=F40"
Range("C18").Formula = "=G40"
Range("C20").Formula = "=K40"
Range("C22").Formula = "=H40"
Range("C24").Formula = "=J40"
Range("G5").Formula = "=G10+.01"
Range("G10").Formula = "=A40"
Range("G12").Formula = "=B40"
Range("G14").Formula = "=C40"
Range("G18").Formula = "=I40"
' this copies the new request # and date into A40 and B40
Range("G5").Copy
Range("A40").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'
Range("G7").Copy
Range("B40").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("40:40").Copy
End With
' this selects row 40 from the Regen pg and inserts the info into the log as a new
' row then sorts the log by Request #
With Sheets("Request Log").Rows("10:10")
.Insert Shift:=xlDown
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Application.Run "SortLogByRequestNumber"
' This copies the Regen Request Form into another file
Sheets("Regenerate Request").Cells.Copy
Workbooks.Add
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'***************************************
' Not Sure why you are pasting twice...
'***************************************
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False
Application.CutCopyMode = False
Range("C10,C12,C14,C16,C18,C20,C22,C24,G5,G7,G10,G12,G14,G18").Locked = True
Dim strFileName As String
strFileName = ActiveSheet.Range("G1").Value
strFileName = Application.GetSaveAsFilename(strFileName, "Microsoft Excel Workbook (*.xls),*.xls")
ChDir "P:\DesignSheets\CostRequestLog\"
ActiveWorkbook.SaveAs strFileName
Application.Run "SendCostRequestEmail"
ActiveWorkbook.Close savechanges:=False
'This goes back to the main file and returns to the Request Log
Workbooks("10-3 D05 Cost Request Book.xls").Activate
Application.Run "RegenFormProtect"
Sheets("Request Log").Select
End Sub
Display More
I added a few comment cards with asterick lines for some areas.
Re: Run-time Error Every Other Time The Macro Runs
will get back to you in a bit. Will trump it down for you...but I am sure these guys will trim it even further. Like you, I am still a newbie...but I can assure you this...you are in the right place for help!!!
Back in a bit.
Is it possible to search a directory looking for a particular file, then if found, copy the directory and all its contents to another location?
it should be noted that not all of the files will be *.xls.
Re: Set-up Of "if" "or" Formula?
Is this statement checking on each line or is it a one time thing? What happens if its completed in all three cases?
Where will this formula live? Is it checking each case separatly or as one formula?
Re: Run-time Error Every Other Time The Macro Runs
omg its the attack of the evil excel macro recorder...
Have you tried just using the copy to destination feature?
Sub YesRegen()
' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
' the log, generates the new file and attaches it to an email
Application.Run "LogUnprotect"
Application.Run "RegenFormUnprotect"
Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy Sheets("Regenerate Request").Range("A40")
the more you select/activate things, the more the macro and efficiency slows down. you should never have to actually select anything. just call on it and execute your command.
Norie has mentioned this above. Try it...you wouldn't believe how much of that code (i say about 75%) is useless.
Re: Run Code On Mulitple Sheets
Thanks. It seems to operate now on a test update. going to run it for real now and see how it goes.
I also changed...
to....
not sure if this is really necessary, but it seemed to help amidst the other things i was trying. thanks for your help guys. will reply back with results if it works.
Thanks Again!!
Re: Run Code On Mulitple Sheets
the code runs, but doesn't make any of the changes (ie. adding the data validation on all the ws in each of the workbooks.)
as a matter of fact, it appears to simply open the files, then save and close them. almost as if its jumping right over the code.
I am going to reactivate screen updating and step through.
Re: Run Code On Mulitple Sheets
still not working for some reason.
I have a workbook that provides updates to our timesheet workbook. For some reason, I cannot get the code to execute the update. It doesn't crash, and appears to run fine, yet the update doesn't occur.
Can someone see where I am going wrong here?
Option Explicit
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim Cell As Range
Dim rngdata As Range
'
If MsgBox("You are about to Run and Update on all Timesheets, Do you want to proceed?", _
vbYesNo, "Timesheet Update Manager") = vbYes Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
'----------------------------------------------------------------------------------------------
'
Set wbCodeBook = ThisWorkbook
'
With Application.FileSearch
.NewSearch
'Change path to suit
Set rngdata = Sheets("FileCheck").Range("H2") 'This cell provides path for files to update
For Each Cell In rngdata
.LookIn = Cell.Value
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
'----------------------------------------------------------------------------------------------
'----------------------------------------------------------------------------------------------
'----------------------------------------------------------------------------------------------
' Update code execution area for all Employee Timesheets
'
'**********Enter Update Information Here*********
'
Dim ws As Worksheets
'
For Each ws In ActiveWorkbook.Sheets
ws.Unprotect Password:="light"
Range("D7:AH87").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween, Formula1:="0", Formula2:="24"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Invalid Entry"
.InputMessage = ""
.ErrorMessage = "Numerical value only. No Text may be entered."
.ShowInput = True
.ShowError = True
End With
ws.Protect Password:="light"
Next ws
'
'----------------------------------------------------------------------------------------------
'----------------------------------------------------------------------------------------------
'----------------------------------------------------------------------------------------------
'
wbResults.Close SaveChanges:=True
Next lCount
End If
Next Cell
End With
On Error GoTo 0
'
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox "Update Complete"
Else
MsgBox "Update NOT Executed!!"
End If
End Sub
Display More
Re: 2 Copies of The Same Workbook Open in Task Manager
Its not carshing during a macro, its crashing on "save".
Don't worry about this Dave...I appreciate your patience in trying to help, but this looks like a virtual memory problem that will be a real minder bender to fix.
Without you actually seeing it, I don't think there is an easier answer.
Going to "shelf" this for a while.
Thanks anyway.