Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
Does anyone have any suggestions on how to solve the problem of having too
long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True .... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
You could build an array of names in your code--but even better, you could put
your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
Thank you very much for your reply
The For loop does not work for my case, could you please give me any suggestions on how to update the files one by one? because the filename is not structured in this format file + number. Thank you for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
The code I suggested doesn't rely on the names of the files looking like what
you posted. It relies on the names being in a column in a worksheet. Eric wrote: Thank you very much for your reply The For loop does not work for my case, could you please give me any suggestions on how to update the files one by one? because the filename is not structured in this format file + number. Thank you for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
Hi Dave Peterson:
Thank you very much for your suggestions There is a minor problem on closing file. The following coding is working, when the specific file is opened, there is no need for the drive and directory for closing the file. Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True For the following code, it may contains the drive and directory, and cause not able to close the file. Do you have any suggestions on how to fix the code please? for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell I look forward to your reply Thank you very much for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
There's nothing in that code that closes the workbook that relies on the name or
path of the file. If you're having trouble, then you may want to post the details of what happens. Eric wrote: Hi Dave Peterson: Thank you very much for your suggestions There is a minor problem on closing file. The following coding is working, when the specific file is opened, there is no need for the drive and directory for closing the file. Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True For the following code, it may contains the drive and directory, and cause not able to close the file. Do you have any suggestions on how to fix the code please? for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell I look forward to your reply Thank you very much for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
There is an error 1004 pointing to the following line
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) and the file cannot be opened at all On the other hands, if I try the following coding, the file can be opened, but it cannot close without any pop up error message. Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Do you have any suggestions? I look forward to your reply Thank you very much for any suggestions Eric "Dave Peterson" wrote: There's nothing in that code that closes the workbook that relies on the name or path of the file. If you're having trouble, then you may want to post the details of what happens. Eric wrote: Hi Dave Peterson: Thank you very much for your suggestions There is a minor problem on closing file. The following coding is working, when the specific file is opened, there is no need for the drive and directory for closing the file. Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True For the following code, it may contains the drive and directory, and cause not able to close the file. Do you have any suggestions on how to fix the code please? for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell I look forward to your reply Thank you very much for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
There is the coding for macro
Under the spreadsheet, in cell A1, I insert the name C:\WIN98\Desktop\Economic.xls ================================ Option Explicit Sub Temp2() Dim myRng As Range Dim myCell As Range Dim wkbk As Workbook With Worksheets("Lists") Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells Set wkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=3) wkbk.Close savechanges:=True Next myCell Workbooks("UpdateFiles").Close savechanges:=True End Sub ================================ Do you have any suggestions? Thank you very much for any suggestions Eric "Dave Peterson" wrote: There's nothing in that code that closes the workbook that relies on the name or path of the file. If you're having trouble, then you may want to post the details of what happens. Eric wrote: Hi Dave Peterson: Thank you very much for your suggestions There is a minor problem on closing file. The following coding is working, when the specific file is opened, there is no need for the drive and directory for closing the file. Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True For the following code, it may contains the drive and directory, and cause not able to close the file. Do you have any suggestions on how to fix the code please? for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell I look forward to your reply Thank you very much for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
The code starts processing the data in A2--I figured you'd put headers in A1.
But you could change this: Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) to: Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) I'd add a couple of checks: Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") 'still starting in row 2! set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=mycell.value, UpdateLinks:=3) on error goto 0 if wkbk is nothing then mycell.offset(0,1).value = "Failed to open!" else wkbk.Close savechanges:=True mycell.offset(0,1).value = "ok" end if next mycell 'better to include an extension Workbooks("UpdateFiles.xls").Close savechanges:=True End Sub (Untested, uncompiled.) Eric wrote: There is the coding for macro Under the spreadsheet, in cell A1, I insert the name C:\WIN98\Desktop\Economic.xls ================================ Option Explicit Sub Temp2() Dim myRng As Range Dim myCell As Range Dim wkbk As Workbook With Worksheets("Lists") Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells Set wkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=3) wkbk.Close savechanges:=True Next myCell Workbooks("UpdateFiles").Close savechanges:=True End Sub ================================ Do you have any suggestions? Thank you very much for any suggestions Eric "Dave Peterson" wrote: There's nothing in that code that closes the workbook that relies on the name or path of the file. If you're having trouble, then you may want to post the details of what happens. Eric wrote: Hi Dave Peterson: Thank you very much for your suggestions There is a minor problem on closing file. The following coding is working, when the specific file is opened, there is no need for the drive and directory for closing the file. Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True For the following code, it may contains the drive and directory, and cause not able to close the file. Do you have any suggestions on how to fix the code please? for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell I look forward to your reply Thank you very much for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
Thank you very much for your suggestions
It works now and is very nice to show "ok" on B column, but I would like to add the date on C column and time on D column for updated record. mycell.offset(0,1).value = "ok" Do you have any suggestions? Thank you very much for your suggestions Eric "Dave Peterson" wrote: The code starts processing the data in A2--I figured you'd put headers in A1. But you could change this: Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) to: Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) I'd add a couple of checks: Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") 'still starting in row 2! set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=mycell.value, UpdateLinks:=3) on error goto 0 if wkbk is nothing then mycell.offset(0,1).value = "Failed to open!" else wkbk.Close savechanges:=True mycell.offset(0,1).value = "ok" end if next mycell 'better to include an extension Workbooks("UpdateFiles.xls").Close savechanges:=True End Sub (Untested, uncompiled.) Eric wrote: There is the coding for macro Under the spreadsheet, in cell A1, I insert the name C:\WIN98\Desktop\Economic.xls ================================ Option Explicit Sub Temp2() Dim myRng As Range Dim myCell As Range Dim wkbk As Workbook With Worksheets("Lists") Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells Set wkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=3) wkbk.Close savechanges:=True Next myCell Workbooks("UpdateFiles").Close savechanges:=True End Sub ================================ Do you have any suggestions? Thank you very much for any suggestions Eric "Dave Peterson" wrote: There's nothing in that code that closes the workbook that relies on the name or path of the file. If you're having trouble, then you may want to post the details of what happens. Eric wrote: Hi Dave Peterson: Thank you very much for your suggestions There is a minor problem on closing file. The following coding is working, when the specific file is opened, there is no need for the drive and directory for closing the file. Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True For the following code, it may contains the drive and directory, and cause not able to close the file. Do you have any suggestions on how to fix the code please? for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell I look forward to your reply Thank you very much for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
How about putting the time and date in column C
mycell.offset(0,1).value = "ok" with mycell.offset(0,2) .numberformat = "mm/dd/yyyy hh:mm:ss" .value = now end with Eric wrote: Thank you very much for your suggestions It works now and is very nice to show "ok" on B column, but I would like to add the date on C column and time on D column for updated record. mycell.offset(0,1).value = "ok" Do you have any suggestions? Thank you very much for your suggestions Eric "Dave Peterson" wrote: The code starts processing the data in A2--I figured you'd put headers in A1. But you could change this: Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) to: Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) I'd add a couple of checks: Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") 'still starting in row 2! set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=mycell.value, UpdateLinks:=3) on error goto 0 if wkbk is nothing then mycell.offset(0,1).value = "Failed to open!" else wkbk.Close savechanges:=True mycell.offset(0,1).value = "ok" end if next mycell 'better to include an extension Workbooks("UpdateFiles.xls").Close savechanges:=True End Sub (Untested, uncompiled.) Eric wrote: There is the coding for macro Under the spreadsheet, in cell A1, I insert the name C:\WIN98\Desktop\Economic.xls ================================ Option Explicit Sub Temp2() Dim myRng As Range Dim myCell As Range Dim wkbk As Workbook With Worksheets("Lists") Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells Set wkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=3) wkbk.Close savechanges:=True Next myCell Workbooks("UpdateFiles").Close savechanges:=True End Sub ================================ Do you have any suggestions? Thank you very much for any suggestions Eric "Dave Peterson" wrote: There's nothing in that code that closes the workbook that relies on the name or path of the file. If you're having trouble, then you may want to post the details of what happens. Eric wrote: Hi Dave Peterson: Thank you very much for your suggestions There is a minor problem on closing file. The following coding is working, when the specific file is opened, there is no need for the drive and directory for closing the file. Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True For the following code, it may contains the drive and directory, and cause not able to close the file. Do you have any suggestions on how to fix the code please? for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell I look forward to your reply Thank you very much for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
Thank you very much for your suggestions
What if I would like to insert date only under column C, and to insert time only under column D. Do you have any suggestions? When I run a list of files, sometime, the process is stopped after opening some file without closing it. There is no error message, but when I re-run the same list again, then this error does not occur. I have no idea why sometime it will stop in the middle of the process. Do you have any suggestions on how to check this error? Thank you very much for any suggestions Eric "Dave Peterson" wrote: How about putting the time and date in column C mycell.offset(0,1).value = "ok" with mycell.offset(0,2) .numberformat = "mm/dd/yyyy hh:mm:ss" .value = now end with Eric wrote: Thank you very much for your suggestions It works now and is very nice to show "ok" on B column, but I would like to add the date on C column and time on D column for updated record. mycell.offset(0,1).value = "ok" Do you have any suggestions? Thank you very much for your suggestions Eric "Dave Peterson" wrote: The code starts processing the data in A2--I figured you'd put headers in A1. But you could change this: Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) to: Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) I'd add a couple of checks: Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") 'still starting in row 2! set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=mycell.value, UpdateLinks:=3) on error goto 0 if wkbk is nothing then mycell.offset(0,1).value = "Failed to open!" else wkbk.Close savechanges:=True mycell.offset(0,1).value = "ok" end if next mycell 'better to include an extension Workbooks("UpdateFiles.xls").Close savechanges:=True End Sub (Untested, uncompiled.) Eric wrote: There is the coding for macro Under the spreadsheet, in cell A1, I insert the name C:\WIN98\Desktop\Economic.xls ================================ Option Explicit Sub Temp2() Dim myRng As Range Dim myCell As Range Dim wkbk As Workbook With Worksheets("Lists") Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells Set wkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=3) wkbk.Close savechanges:=True Next myCell Workbooks("UpdateFiles").Close savechanges:=True End Sub ================================ Do you have any suggestions? Thank you very much for any suggestions Eric "Dave Peterson" wrote: There's nothing in that code that closes the workbook that relies on the name or path of the file. If you're having trouble, then you may want to post the details of what happens. Eric wrote: Hi Dave Peterson: Thank you very much for your suggestions There is a minor problem on closing file. The following coding is working, when the specific file is opened, there is no need for the drive and directory for closing the file. Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True For the following code, it may contains the drive and directory, and cause not able to close the file. Do you have any suggestions on how to fix the code please? for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell I look forward to your reply Thank you very much for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Too long coding for Macro
#1.
mycell.offset(0,1).value = "ok" with mycell.offset(0,2) .numberformat = "mm/dd/yyyy" .value = date end with with mycell.offset(0,3) .numberformat = "hh:mm:ss" .value = Time end with #2. How do you run the macro? If you're using a shortcut key combination, remove the shift key from that combination. But this won't explain why it sometimes works. This should cause the same problem each time you run it. But it's the only guess I have. Eric wrote: Thank you very much for your suggestions What if I would like to insert date only under column C, and to insert time only under column D. Do you have any suggestions? When I run a list of files, sometime, the process is stopped after opening some file without closing it. There is no error message, but when I re-run the same list again, then this error does not occur. I have no idea why sometime it will stop in the middle of the process. Do you have any suggestions on how to check this error? Thank you very much for any suggestions Eric "Dave Peterson" wrote: How about putting the time and date in column C mycell.offset(0,1).value = "ok" with mycell.offset(0,2) .numberformat = "mm/dd/yyyy hh:mm:ss" .value = now end with Eric wrote: Thank you very much for your suggestions It works now and is very nice to show "ok" on B column, but I would like to add the date on C column and time on D column for updated record. mycell.offset(0,1).value = "ok" Do you have any suggestions? Thank you very much for your suggestions Eric "Dave Peterson" wrote: The code starts processing the data in A2--I figured you'd put headers in A1. But you could change this: Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) to: Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) I'd add a couple of checks: Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") 'still starting in row 2! set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=mycell.value, UpdateLinks:=3) on error goto 0 if wkbk is nothing then mycell.offset(0,1).value = "Failed to open!" else wkbk.Close savechanges:=True mycell.offset(0,1).value = "ok" end if next mycell 'better to include an extension Workbooks("UpdateFiles.xls").Close savechanges:=True End Sub (Untested, uncompiled.) Eric wrote: There is the coding for macro Under the spreadsheet, in cell A1, I insert the name C:\WIN98\Desktop\Economic.xls ================================ Option Explicit Sub Temp2() Dim myRng As Range Dim myCell As Range Dim wkbk As Workbook With Worksheets("Lists") Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells Set wkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=3) wkbk.Close savechanges:=True Next myCell Workbooks("UpdateFiles").Close savechanges:=True End Sub ================================ Do you have any suggestions? Thank you very much for any suggestions Eric "Dave Peterson" wrote: There's nothing in that code that closes the workbook that relies on the name or path of the file. If you're having trouble, then you may want to post the details of what happens. Eric wrote: Hi Dave Peterson: Thank you very much for your suggestions There is a minor problem on closing file. The following coding is working, when the specific file is opened, there is no need for the drive and directory for closing the file. Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True For the following code, it may contains the drive and directory, and cause not able to close the file. Do you have any suggestions on how to fix the code please? for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell I look forward to your reply Thank you very much for any suggestions Eric "Dave Peterson" wrote: You could build an array of names in your code--but even better, you could put your names in a worksheet. Option Explicit Sub Temp2() Dim myRng as Range dim myCell as range dim wkbk as workbook with worksheets("sheet9999") set myRng = .range("a2",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3) wkbk.Close savechanges:=True next mycell Workbooks("UpdateFiles").Close savechanges:=True End Sub Eric wrote: Does anyone have any suggestions on how to solve the problem of having too long coding for Macro? I don't want to split the coding into 2 macro separately. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Coding is shown as below: Sub Temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3 Workbooks("File1.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3 Workbooks("File2.xls").Close savechanges:=True Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3 Workbooks("File3.xls").Close savechanges:=True ... Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3 Workbooks("File1000.xls").Close savechanges:=True Workbooks("UpdateFiles").Close savechanges:=True End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to solve too long coding for Macro ? | Excel Worksheet Functions | |||
coding macro | Excel Discussion (Misc queries) | |||
How to use the if statement for Macro coding? | Excel Worksheet Functions | |||
Hidding Macro names and coding | Excel Discussion (Misc queries) | |||
soft-coding lines in a macro | Excel Discussion (Misc queries) |