Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cycling through workbooks
Hello,
I'm creating various spreadsheets for managers listing accesses to various programs in a number of tabs, to avoid the out of memory error, I've broken it down into four stages. The first stage creates the a file for each manager and copies the first batch of records into a given manager's spreadsheet, and the rest are supposed to copy teh rest of the records from the other files, but I can't get it to open the original spreadsheets. The spreadsheets were successfully created with the following code: ActiveWorkbook.SaveAs Filename:="\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" & SPOC & " - " & QYear & " " & QName, FileFormat:=xlNormal, _ SPOC = Manager's name (ex. Smith, Bob) QYear = Year (ex. 2008) QName = Quarter (ex. Q2) I've been trying to use the following to open the workbook without luck: Workbooks.Open Filename:="\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" & SPOC & " - " & QYear & " " & QName & ".xls" It will cycle through some 500 manager names during the updating. Is there another way I should be doing this? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cycling through workbooks
Those strings that do the concatenation look the same to me.
What happens when you try to open one of the workbooks? Maybe your variables aren't what you think--or maybe the file that was created didn't have the name that you think. I would usually provide the .xls when doing the file|saveAs in code. But excel is pretty smart and should add it to the filename. One more thing, what version of excel are you using? You're not getting hit by one of those .xlsx or .xlsm vs .xls extension differences are you? Nico wrote: Hello, I'm creating various spreadsheets for managers listing accesses to various programs in a number of tabs, to avoid the out of memory error, I've broken it down into four stages. The first stage creates the a file for each manager and copies the first batch of records into a given manager's spreadsheet, and the rest are supposed to copy teh rest of the records from the other files, but I can't get it to open the original spreadsheets. The spreadsheets were successfully created with the following code: ActiveWorkbook.SaveAs Filename:="\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" & SPOC & " - " & QYear & " " & QName, FileFormat:=xlNormal, _ SPOC = Manager's name (ex. Smith, Bob) QYear = Year (ex. 2008) QName = Quarter (ex. Q2) I've been trying to use the following to open the workbook without luck: Workbooks.Open Filename:="\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" & SPOC & " - " & QYear & " " & QName & ".xls" It will cycle through some 500 manager names during the updating. Is there another way I should be doing this? Thanks! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cycling through workbooks
I susspect the problem may be with the lenght of the filename. I would first
try to open a window explorer (or My computer) and go to the directory where the file is located and double click on the filename. See if excel opens. if it doesn't then your filename is to long. I start having problems when the filename is around 128 characters. Moving the files to a shorter path name will fix this problem. "Nico" wrote: Hello, I'm creating various spreadsheets for managers listing accesses to various programs in a number of tabs, to avoid the out of memory error, I've broken it down into four stages. The first stage creates the a file for each manager and copies the first batch of records into a given manager's spreadsheet, and the rest are supposed to copy teh rest of the records from the other files, but I can't get it to open the original spreadsheets. The spreadsheets were successfully created with the following code: ActiveWorkbook.SaveAs Filename:="\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" & SPOC & " - " & QYear & " " & QName, FileFormat:=xlNormal, _ SPOC = Manager's name (ex. Smith, Bob) QYear = Year (ex. 2008) QName = Quarter (ex. Q2) I've been trying to use the following to open the workbook without luck: Workbooks.Open Filename:="\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" & SPOC & " - " & QYear & " " & QName & ".xls" It will cycle through some 500 manager names during the updating. Is there another way I should be doing this? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cycling through workbooks
Thanks for your responses.
I don't think it's the file names themselves that are the problem, but trying to open them in the macro this way does not appear to work. The file names save correctly in the path like this: Smith, Bob - 2008 Q2.xls Jones, Frank - 2008 Q2.xls Singh, Raj - 2008 Q2.xls etc... But when I try to open them with the macro to continue updating the records it failes to locate them. I suspect it may be the way I'm calling them, but I don't know of another way to do it? Any idea how to get this to work? "Joel" wrote: I susspect the problem may be with the lenght of the filename. I would first try to open a window explorer (or My computer) and go to the directory where the file is located and double click on the filename. See if excel opens. if it doesn't then your filename is to long. I start having problems when the filename is around 128 characters. Moving the files to a shorter path name will fix this problem. "Nico" wrote: Hello, I'm creating various spreadsheets for managers listing accesses to various programs in a number of tabs, to avoid the out of memory error, I've broken it down into four stages. The first stage creates the a file for each manager and copies the first batch of records into a given manager's spreadsheet, and the rest are supposed to copy teh rest of the records from the other files, but I can't get it to open the original spreadsheets. The spreadsheets were successfully created with the following code: ActiveWorkbook.SaveAs Filename:="\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" & SPOC & " - " & QYear & " " & QName, FileFormat:=xlNormal, _ SPOC = Manager's name (ex. Smith, Bob) QYear = Year (ex. 2008) QName = Quarter (ex. Q2) I've been trying to use the following to open the workbook without luck: Workbooks.Open Filename:="\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" & SPOC & " - " & QYear & " " & QName & ".xls" It will cycle through some 500 manager names during the updating. Is there another way I should be doing this? Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cycling through workbooks
Try using these tow test macros to help you isolate where the problem is
located. If they work then modify the 2nd macro changing the filename to your variable names until you find the source of the problem. If they don't work then try to find the problem with my code. Sub test1() Folder = "\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" FName = Dir(Folder & "*.xls") Do While FName < "" MsgBox (FName) FName = Dir() Loop End Sub Sub test2() Folder = "\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" FNames = Array("Smith, Bob - 2008 Q2.xls", _ "Jones, Frank - 2008 Q2.xls", _ "Singh, Raj - 2008 Q2.xls") For Each FName In FNames TestName = Dir(Folder & FName) If TestName = "" Then MsgBox ("Did not find File : " & FName) Else Set bk = Workbooks.Open(Filename:=Folder & FName) If bk Is Nothing Then MsgBox ("Could Not open file : " & FName) Else MsgBox ("Sucessfully opened file : " & FName) bk.Close savechanges:=False End If End If Next FName End Sub "Nico" wrote: Thanks for your responses. I don't think it's the file names themselves that are the problem, but trying to open them in the macro this way does not appear to work. The file names save correctly in the path like this: Smith, Bob - 2008 Q2.xls Jones, Frank - 2008 Q2.xls Singh, Raj - 2008 Q2.xls etc... But when I try to open them with the macro to continue updating the records it failes to locate them. I suspect it may be the way I'm calling them, but I don't know of another way to do it? Any idea how to get this to work? "Joel" wrote: I susspect the problem may be with the lenght of the filename. I would first try to open a window explorer (or My computer) and go to the directory where the file is located and double click on the filename. See if excel opens. if it doesn't then your filename is to long. I start having problems when the filename is around 128 characters. Moving the files to a shorter path name will fix this problem. "Nico" wrote: Hello, I'm creating various spreadsheets for managers listing accesses to various programs in a number of tabs, to avoid the out of memory error, I've broken it down into four stages. The first stage creates the a file for each manager and copies the first batch of records into a given manager's spreadsheet, and the rest are supposed to copy teh rest of the records from the other files, but I can't get it to open the original spreadsheets. The spreadsheets were successfully created with the following code: ActiveWorkbook.SaveAs Filename:="\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" & SPOC & " - " & QYear & " " & QName, FileFormat:=xlNormal, _ SPOC = Manager's name (ex. Smith, Bob) QYear = Year (ex. 2008) QName = Quarter (ex. Q2) I've been trying to use the following to open the workbook without luck: Workbooks.Open Filename:="\\[server and folder path]\Quarterly Attestations\Quarterly Reports\" & SPOC & " - " & QYear & " " & QName & ".xls" It will cycle through some 500 manager names during the updating. Is there another way I should be doing this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
Display 2 formulas from source workbooks to destination workbooks | Excel Discussion (Misc queries) | |||
Pivot Table Cycling Through Page Fields Automatically | Excel Discussion (Misc queries) | |||
Anyone out there knows of running cycling and swimming logs? | Excel Discussion (Misc queries) |