Cycling through workbooks

[Nico]

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]

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?

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!

 

[Joel]

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]

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?

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.

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!

 

[Joel]

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

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?

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.

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!