Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Sheet Exists - Tom Ogilvy
Hi Tom,
Remember last night you modified some code for me to include a message box for files that did not contain the sheet "timesheet" (code below). Is it possible to write the files that do not have that sheet to a log (maybe a text file, or even the immediate window) as well as the message box? I was hoping to add some code that would kick off this procedure automatically, and didn't want the message box to hold up the procedure waiting for the ok click. Thanks in advance! Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _ Paste:=xlPasteValues Else MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles End If '********************** 'Duplicate Test Here Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Sheet Exists - Tom Ogilvy
'to Immediate Window
Debug.Print wkbk.Name & " does not have the TSData sheet" "Steph" wrote in message ... Hi Tom, Remember last night you modified some code for me to include a message box for files that did not contain the sheet "timesheet" (code below). Is it possible to write the files that do not have that sheet to a log (maybe a text file, or even the immediate window) as well as the message box? I was hoping to add some code that would kick off this procedure automatically, and didn't want the message box to hold up the procedure waiting for the ok click. Thanks in advance! Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _ Paste:=xlPasteValues Else MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles End If '********************** 'Duplicate Test Here Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Sheet Exists - Tom Ogilvy
'maybe a text file
Sub OpenFiles_New() ... Call LogToFile(wkbk.Name & " does not have the TSData sheet") ... End Sub Private Sub LogToFile(msg As String) Dim f As Integer Dim path As String path = "C:\" f = FreeFile Open path & "\MyOutput.txt" For Append As #f Print #f, msg Close #f End Sub This is convenient, but it opens and close the file for every write. If you need more speed, store the names in ram, open the file, write them out, close the file. This will only be an issue for huge numbers of writes (10's - 100's of thousands or more), IMHO Bob. "Steph" wrote in message ... Hi Tom, Remember last night you modified some code for me to include a message box for files that did not contain the sheet "timesheet" (code below). Is it possible to write the files that do not have that sheet to a log (maybe a text file, or even the immediate window) as well as the message box? I was hoping to add some code that would kick off this procedure automatically, and didn't want the message box to hold up the procedure waiting for the ok click. Thanks in advance! Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _ Paste:=xlPasteValues Else MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles End If '********************** 'Duplicate Test Here Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Sheet Exists - Tom Ogilvy
Yeah, I know my name is not Tom, but I think he will understand. B^)
"Bob Kilmer" wrote in message ... 'maybe a text file Sub OpenFiles_New() ... Call LogToFile(wkbk.Name & " does not have the TSData sheet") ... End Sub Private Sub LogToFile(msg As String) Dim f As Integer Dim path As String path = "C:\" f = FreeFile Open path & "\MyOutput.txt" For Append As #f Print #f, msg Close #f End Sub This is convenient, but it opens and close the file for every write. If you need more speed, store the names in ram, open the file, write them out, close the file. This will only be an issue for huge numbers of writes (10's - 100's of thousands or more), IMHO Bob. "Steph" wrote in message ... Hi Tom, Remember last night you modified some code for me to include a message box for files that did not contain the sheet "timesheet" (code below). Is it possible to write the files that do not have that sheet to a log (maybe a text file, or even the immediate window) as well as the message box? I was hoping to add some code that would kick off this procedure automatically, and didn't want the message box to hold up the procedure waiting for the ok click. Thanks in advance! Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _ Paste:=xlPasteValues Else MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles End If '********************** 'Duplicate Test Here Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Sheet Exists - Tom Ogilvy
Hi Bob,
Thanks for the responses!! Didn't mean to single out only Tom....I just figured it would be easier since he edited the code last night. But I should have known....all you guys on this board are damn brilliant! To your first response, the immediate window is nice, but a record in a file would be nicer. As for the text file and speed, this code will copy 300-400 files....so that means the text file would be opened 300 -4oo times? Or only for each instance where TSdata does not exist? If the latter is the case, that should only occur 10-20 times at most, and therefore would be a great option. BUT, being as ignorant as I am regarding this stuff, how would I add your text file code into the code I originally posted? Thanks again Bob! -Steph "Bob Kilmer" wrote in message ... Yeah, I know my name is not Tom, but I think he will understand. B^) "Bob Kilmer" wrote in message ... 'maybe a text file Sub OpenFiles_New() ... Call LogToFile(wkbk.Name & " does not have the TSData sheet") ... End Sub Private Sub LogToFile(msg As String) Dim f As Integer Dim path As String path = "C:\" f = FreeFile Open path & "\MyOutput.txt" For Append As #f Print #f, msg Close #f End Sub This is convenient, but it opens and close the file for every write. If you need more speed, store the names in ram, open the file, write them out, close the file. This will only be an issue for huge numbers of writes (10's - 100's of thousands or more), IMHO Bob. "Steph" wrote in message ... Hi Tom, Remember last night you modified some code for me to include a message box for files that did not contain the sheet "timesheet" (code below). Is it possible to write the files that do not have that sheet to a log (maybe a text file, or even the immediate window) as well as the message box? I was hoping to add some code that would kick off this procedure automatically, and didn't want the message box to hold up the procedure waiting for the ok click. Thanks in advance! Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _ Paste:=xlPasteValues Else MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles End If '********************** 'Duplicate Test Here Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Sheet Exists - Tom Ogilvy
1. Copy the Sub LogToFile into the workbook.
2. Add this Call LogToFile(wkbk.Name & " does not have the TSData sheet") or this LogToFile wkbk.Name & " does not have the TSData sheet" before or after or inplace of the Msgbox line. The Sub opens and close the file each time it is called, which would be each time TSdata doesn't exist (the else case of your loop). I don't think the lag due to the multiple opens/closes would be noticeable to you, but, below, I have integrated the file open, print and close into the code, as I might do if it were mine. Option Explicit Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet 'var's for saving to a text file ''''''''''''''''''''''''' Dim f As Integer Dim fname As String Dim path As String ' change path or name to suit path = "C:\" fname = "MyOutput.txt" f = FreeFile ''''''''''''''''''''''''' Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", _ MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else ''''''''''''''''''''''''' 'Open the file before the loop ' ''For Append' means we will perpetually add to the file. 'If you want to overwrite each time, use 'For Output'. 'If you want a different file each time, use 'For Output' 'and assign a new, unique name each time the macros is 'run - with a date/time string like this, perhaps ' 'fname = "XYZ_" & Format(Now(),"yyyymmddhhmmss") & .txt ' Open path & fname For Append As #f ''''''''''''''''''''''''' For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row). _ PasteSpecial Paste:=xlPasteValues Else ''''''''''''''''''''''''' 'Print to text file Print #f, wkbk.Name & " does not have the TSData sheet" ''''''''''''''''''''''''' 'You could keep the debug.print here, also if you like. Debup.Print wkbk.Name & " does not have the TSData sheet" 'MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles ''''''''''''''''''''''''' 'close the file after the loop 'and before exiting the sub Close #f ''''''''''''''''''''''''' End If End Sub "Steph" wrote in message ... Hi Bob, Thanks for the responses!! Didn't mean to single out only Tom....I just figured it would be easier since he edited the code last night. But I should have known....all you guys on this board are damn brilliant! To your first response, the immediate window is nice, but a record in a file would be nicer. As for the text file and speed, this code will copy 300-400 files....so that means the text file would be opened 300 -4oo times? Or only for each instance where TSdata does not exist? If the latter is the case, that should only occur 10-20 times at most, and therefore would be a great option. BUT, being as ignorant as I am regarding this stuff, how would I add your text file code into the code I originally posted? Thanks again Bob! -Steph "Bob Kilmer" wrote in message ... Yeah, I know my name is not Tom, but I think he will understand. B^) "Bob Kilmer" wrote in message ... 'maybe a text file Sub OpenFiles_New() ... Call LogToFile(wkbk.Name & " does not have the TSData sheet") ... End Sub Private Sub LogToFile(msg As String) Dim f As Integer Dim path As String path = "C:\" f = FreeFile Open path & "\MyOutput.txt" For Append As #f Print #f, msg Close #f End Sub This is convenient, but it opens and close the file for every write. If you need more speed, store the names in ram, open the file, write them out, close the file. This will only be an issue for huge numbers of writes (10's - 100's of thousands or more), IMHO Bob. "Steph" wrote in message ... Hi Tom, Remember last night you modified some code for me to include a message box for files that did not contain the sheet "timesheet" (code below). Is it possible to write the files that do not have that sheet to a log (maybe a text file, or even the immediate window) as well as the message box? I was hoping to add some code that would kick off this procedure automatically, and didn't want the message box to hold up the procedure waiting for the ok click. Thanks in advance! Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _ Paste:=xlPasteValues Else MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles End If '********************** 'Duplicate Test Here Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Sheet Exists - Tom Ogilvy
Beautiful!! Thank you SO much Tom, uh, I mean Bob B^)
I really appreicate you time and help! Thanks again. -Steph "Bob Kilmer" wrote in message ... 1. Copy the Sub LogToFile into the workbook. 2. Add this Call LogToFile(wkbk.Name & " does not have the TSData sheet") or this LogToFile wkbk.Name & " does not have the TSData sheet" before or after or inplace of the Msgbox line. The Sub opens and close the file each time it is called, which would be each time TSdata doesn't exist (the else case of your loop). I don't think the lag due to the multiple opens/closes would be noticeable to you, but, below, I have integrated the file open, print and close into the code, as I might do if it were mine. Option Explicit Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet 'var's for saving to a text file ''''''''''''''''''''''''' Dim f As Integer Dim fname As String Dim path As String ' change path or name to suit path = "C:\" fname = "MyOutput.txt" f = FreeFile ''''''''''''''''''''''''' Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", _ MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else ''''''''''''''''''''''''' 'Open the file before the loop ' ''For Append' means we will perpetually add to the file. 'If you want to overwrite each time, use 'For Output'. 'If you want a different file each time, use 'For Output' 'and assign a new, unique name each time the macros is 'run - with a date/time string like this, perhaps ' 'fname = "XYZ_" & Format(Now(),"yyyymmddhhmmss") & .txt ' Open path & fname For Append As #f ''''''''''''''''''''''''' For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row). _ PasteSpecial Paste:=xlPasteValues Else ''''''''''''''''''''''''' 'Print to text file Print #f, wkbk.Name & " does not have the TSData sheet" ''''''''''''''''''''''''' 'You could keep the debug.print here, also if you like. Debup.Print wkbk.Name & " does not have the TSData sheet" 'MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles ''''''''''''''''''''''''' 'close the file after the loop 'and before exiting the sub Close #f ''''''''''''''''''''''''' End If End Sub "Steph" wrote in message ... Hi Bob, Thanks for the responses!! Didn't mean to single out only Tom....I just figured it would be easier since he edited the code last night. But I should have known....all you guys on this board are damn brilliant! To your first response, the immediate window is nice, but a record in a file would be nicer. As for the text file and speed, this code will copy 300-400 files....so that means the text file would be opened 300 -4oo times? Or only for each instance where TSdata does not exist? If the latter is the case, that should only occur 10-20 times at most, and therefore would be a great option. BUT, being as ignorant as I am regarding this stuff, how would I add your text file code into the code I originally posted? Thanks again Bob! -Steph "Bob Kilmer" wrote in message ... Yeah, I know my name is not Tom, but I think he will understand. B^) "Bob Kilmer" wrote in message ... 'maybe a text file Sub OpenFiles_New() ... Call LogToFile(wkbk.Name & " does not have the TSData sheet") ... End Sub Private Sub LogToFile(msg As String) Dim f As Integer Dim path As String path = "C:\" f = FreeFile Open path & "\MyOutput.txt" For Append As #f Print #f, msg Close #f End Sub This is convenient, but it opens and close the file for every write. If you need more speed, store the names in ram, open the file, write them out, close the file. This will only be an issue for huge numbers of writes (10's - 100's of thousands or more), IMHO Bob. "Steph" wrote in message ... Hi Tom, Remember last night you modified some code for me to include a message box for files that did not contain the sheet "timesheet" (code below). Is it possible to write the files that do not have that sheet to a log (maybe a text file, or even the immediate window) as well as the message box? I was hoping to add some code that would kick off this procedure automatically, and didn't want the message box to hold up the procedure waiting for the ok click. Thanks in advance! Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _ Paste:=xlPasteValues Else MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles End If '********************** 'Duplicate Test Here Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Sheet Exists - Tom Ogilvy
Thanks very much for the "flowers." Not only are we brilliant, but most of
us practice a bit, I would bet. <g BTW, please do not use End as you have. It is an abortive end and best to avoid except when an abortive end is better that the alternatives (rare). Prefer If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" Exit Sub Else To If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else Bob "Steph" wrote in message ... Beautiful!! Thank you SO much Tom, uh, I mean Bob B^) I really appreicate you time and help! Thanks again. -Steph "Bob Kilmer" wrote in message ... 1. Copy the Sub LogToFile into the workbook. 2. Add this Call LogToFile(wkbk.Name & " does not have the TSData sheet") or this LogToFile wkbk.Name & " does not have the TSData sheet" before or after or inplace of the Msgbox line. The Sub opens and close the file each time it is called, which would be each time TSdata doesn't exist (the else case of your loop). I don't think the lag due to the multiple opens/closes would be noticeable to you, but, below, I have integrated the file open, print and close into the code, as I might do if it were mine. Option Explicit Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet 'var's for saving to a text file ''''''''''''''''''''''''' Dim f As Integer Dim fname As String Dim path As String ' change path or name to suit path = "C:\" fname = "MyOutput.txt" f = FreeFile ''''''''''''''''''''''''' Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", _ MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else ''''''''''''''''''''''''' 'Open the file before the loop ' ''For Append' means we will perpetually add to the file. 'If you want to overwrite each time, use 'For Output'. 'If you want a different file each time, use 'For Output' 'and assign a new, unique name each time the macros is 'run - with a date/time string like this, perhaps ' 'fname = "XYZ_" & Format(Now(),"yyyymmddhhmmss") & .txt ' Open path & fname For Append As #f ''''''''''''''''''''''''' For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row). _ PasteSpecial Paste:=xlPasteValues Else ''''''''''''''''''''''''' 'Print to text file Print #f, wkbk.Name & " does not have the TSData sheet" ''''''''''''''''''''''''' 'You could keep the debug.print here, also if you like. Debup.Print wkbk.Name & " does not have the TSData sheet" 'MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles ''''''''''''''''''''''''' 'close the file after the loop 'and before exiting the sub Close #f ''''''''''''''''''''''''' End If End Sub "Steph" wrote in message ... Hi Bob, Thanks for the responses!! Didn't mean to single out only Tom....I just figured it would be easier since he edited the code last night. But I should have known....all you guys on this board are damn brilliant! To your first response, the immediate window is nice, but a record in a file would be nicer. As for the text file and speed, this code will copy 300-400 files....so that means the text file would be opened 300 -4oo times? Or only for each instance where TSdata does not exist? If the latter is the case, that should only occur 10-20 times at most, and therefore would be a great option. BUT, being as ignorant as I am regarding this stuff, how would I add your text file code into the code I originally posted? Thanks again Bob! -Steph "Bob Kilmer" wrote in message ... Yeah, I know my name is not Tom, but I think he will understand. B^) "Bob Kilmer" wrote in message ... 'maybe a text file Sub OpenFiles_New() ... Call LogToFile(wkbk.Name & " does not have the TSData sheet") ... End Sub Private Sub LogToFile(msg As String) Dim f As Integer Dim path As String path = "C:\" f = FreeFile Open path & "\MyOutput.txt" For Append As #f Print #f, msg Close #f End Sub This is convenient, but it opens and close the file for every write. If you need more speed, store the names in ram, open the file, write them out, close the file. This will only be an issue for huge numbers of writes (10's - 100's of thousands or more), IMHO Bob. "Steph" wrote in message ... Hi Tom, Remember last night you modified some code for me to include a message box for files that did not contain the sheet "timesheet" (code below). Is it possible to write the files that do not have that sheet to a log (maybe a text file, or even the immediate window) as well as the message box? I was hoping to add some code that would kick off this procedure automatically, and didn't want the message box to hold up the procedure waiting for the ok click. Thanks in advance! Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _ Paste:=xlPasteValues Else MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles End If '********************** 'Duplicate Test Here Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Sheet Exists - Tom Ogilvy
Good advice. Thank you!
As far as practice goes - I'm sure you get your fair share! I for one have learned an immense amount from this newsgroup. Some days, half my day is spent reading posts of interest, and printing them put as well! And approx 1 year later, I know a LOT more, and probably 0.0001% of what you guys know! Some day...... :^). Thanks again! "Bob Kilmer" wrote in message ... Thanks very much for the "flowers." Not only are we brilliant, but most of us practice a bit, I would bet. <g BTW, please do not use End as you have. It is an abortive end and best to avoid except when an abortive end is better that the alternatives (rare). Prefer If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" Exit Sub Else To If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else Bob "Steph" wrote in message ... Beautiful!! Thank you SO much Tom, uh, I mean Bob B^) I really appreicate you time and help! Thanks again. -Steph "Bob Kilmer" wrote in message ... 1. Copy the Sub LogToFile into the workbook. 2. Add this Call LogToFile(wkbk.Name & " does not have the TSData sheet") or this LogToFile wkbk.Name & " does not have the TSData sheet" before or after or inplace of the Msgbox line. The Sub opens and close the file each time it is called, which would be each time TSdata doesn't exist (the else case of your loop). I don't think the lag due to the multiple opens/closes would be noticeable to you, but, below, I have integrated the file open, print and close into the code, as I might do if it were mine. Option Explicit Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet 'var's for saving to a text file ''''''''''''''''''''''''' Dim f As Integer Dim fname As String Dim path As String ' change path or name to suit path = "C:\" fname = "MyOutput.txt" f = FreeFile ''''''''''''''''''''''''' Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", _ MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else ''''''''''''''''''''''''' 'Open the file before the loop ' ''For Append' means we will perpetually add to the file. 'If you want to overwrite each time, use 'For Output'. 'If you want a different file each time, use 'For Output' 'and assign a new, unique name each time the macros is 'run - with a date/time string like this, perhaps ' 'fname = "XYZ_" & Format(Now(),"yyyymmddhhmmss") & .txt ' Open path & fname For Append As #f ''''''''''''''''''''''''' For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row). _ PasteSpecial Paste:=xlPasteValues Else ''''''''''''''''''''''''' 'Print to text file Print #f, wkbk.Name & " does not have the TSData sheet" ''''''''''''''''''''''''' 'You could keep the debug.print here, also if you like. Debup.Print wkbk.Name & " does not have the TSData sheet" 'MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles ''''''''''''''''''''''''' 'close the file after the loop 'and before exiting the sub Close #f ''''''''''''''''''''''''' End If End Sub "Steph" wrote in message ... Hi Bob, Thanks for the responses!! Didn't mean to single out only Tom....I just figured it would be easier since he edited the code last night. But I should have known....all you guys on this board are damn brilliant! To your first response, the immediate window is nice, but a record in a file would be nicer. As for the text file and speed, this code will copy 300-400 files....so that means the text file would be opened 300 -4oo times? Or only for each instance where TSdata does not exist? If the latter is the case, that should only occur 10-20 times at most, and therefore would be a great option. BUT, being as ignorant as I am regarding this stuff, how would I add your text file code into the code I originally posted? Thanks again Bob! -Steph "Bob Kilmer" wrote in message ... Yeah, I know my name is not Tom, but I think he will understand. B^) "Bob Kilmer" wrote in message ... 'maybe a text file Sub OpenFiles_New() ... Call LogToFile(wkbk.Name & " does not have the TSData sheet") ... End Sub Private Sub LogToFile(msg As String) Dim f As Integer Dim path As String path = "C:\" f = FreeFile Open path & "\MyOutput.txt" For Append As #f Print #f, msg Close #f End Sub This is convenient, but it opens and close the file for every write. If you need more speed, store the names in ram, open the file, write them out, close the file. This will only be an issue for huge numbers of writes (10's - 100's of thousands or more), IMHO Bob. "Steph" wrote in message ... Hi Tom, Remember last night you modified some code for me to include a message box for files that did not contain the sheet "timesheet" (code below). Is it possible to write the files that do not have that sheet to a log (maybe a text file, or even the immediate window) as well as the message box? I was hoping to add some code that would kick off this procedure automatically, and didn't want the message box to hold up the procedure waiting for the ok click. Thanks in advance! Sub OpenFiles_New() 'Opens Files in Folder Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long Dim wkbk As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.*),*.*", _ Title:="Select Timesheets to Include in SAP PO Upload", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else For iFiles = LBound(GetFiles) To UBound(GetFiles) Workbooks.OpenText Filename:=GetFiles(iFiles) Set wkbk = ActiveWorkbook Set sh = Nothing On Error Resume Next Set sh = wkbk.Worksheets("TSData") On Error Resume Next If Not sh Is Nothing Then ' With ActiveWorkbook.Sheets("Timesheet").UsedRange ' .Value = .Value ' End With wkbk.Sheets("TSData").Range("A10:AG" & _ Sheets("Timesheet").Range("A20").End(xlUp).Row).Co py ThisWorkbook.Worksheets("Consol").Range("A" & _ Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _ Paste:=xlPasteValues Else MsgBox wkbk.Name & " does not have the TSData sheet" End If wkbk.Close Next iFiles End If '********************** 'Duplicate Test Here Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test if file exists | Excel Discussion (Misc queries) | |||
Test for Worksheet Exists | Excel Programming | |||
Test if folder exists, create if it doesn't? | Excel Programming | |||
Test if a folder exists, create if it doesn't? | Excel Programming | |||
Test if a folder exists | Excel Programming |