A macro question

  • Thread starter Thread starter John Kitchens
  • Start date Start date
J

John Kitchens

Hello,

I have a macro that when prompted opens up many different files that are
located in the same folder.

Each of these files that I am opening are files that have been previously
summarized through the use of a macro.

Example: I wll have 40 to 60 files that I save in the same folder on a daily
basis. At the end of the day I use a template and a macro that will open
all of the daily files and summarize them into one neat sheet for me. When
this macro is executed all of the 40 to 60 daily files are opened to give me
the total of my daily summary sheet.

I save each of these daily summaries as DS100104.xls etc, tommorrow
DS100204.xls etc.

Now what I have created a new template that will be a weekly, monthly,
quarterly and so forth summary.

I have modified the macro so that it will open page 1 of the DS files. It
works, but I am getting the following message when I open the DS files:

"Microsoft Excel"

"The workbook you opened contains automatic links to information in another
workbook. Do you want to update this workbook with changed made to the other
workbook?"

"To update all linked information, click Yes.
To keep the existing information, click No."

This is a copy of the macro that runs my program:

Sub GetSheets()
Dim i As Long
Dim varr As Variant
Dim wkbk As Workbook
Dim ws As Worksheet
Application.DisplayAlerts = False
varr = Application.GetOpenFilename(filefilter:="Excel Files, *.xls", _
MultiSelect:=True)
If IsArray(varr) Then
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open(varr(i))
Set ws = wkbk.Sheets("Summary")
ws.Unprotect
ws.Name = Left(wkbk.Name, Len(wkbk.Name) - 4)
ws.Cells.Style = "Normal"
ws.DrawingObjects.Delete
ws.Copy After:=ThisWorkbook.Worksheets("Start")

wkbk.Close SaveChanges:=False
Next
End If
Application.DisplayAlerts = True
End Sub

Sub UnhideSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End Sub


I am hoping that this macro can be modified so that "No" will automatically
be selected for each sheet so that the user doesn't have to click it each
time.

Thanks for any help I can get.

John
 
Hi
have a look in the VBA help for workbooks.open. You can specify the
Updatelink parameter to avoid this message
 
Along with what Frank wrote, I'd add a readonly option, too. If you're just
opening the workbook to copy stuff out of it, you'll be able to open it if
someone else has it open--or if it's marked readonlyrecommended:

Set wkbk = Workbooks.Open(varr(i), UpdateLinks = 0, ReadOnly:=True)

Take a look at help for more info (er, like Frank wrote).
 
If you position your cursor on the .open in workbooks.open(...), then hit F1,
you should see the help for Open.

If this doesn't work--VBA's help isn't installed, I'd rerun Office Setup and
make sure I chose to install VBA's help.
 
Thanks for the reply Frank. For some reason my help files are missing. Can
you tell me what the command would look like so that I can try it. I am new
to VBA and I can't seem to get this right.

John
 
Hello Dave,

I tried to adding the line that you gave me: Set wkbk =
Workbooks.Open(varr(i), UpdateLinks = 0, ReadOnly:=True)

but it didn't work. Below is a copy of my macro. When I try it I get a
"Compile error:
Variable not defined" message. I don't know what it is wrong. Do you have
any suggestions.

Thank you for all of your help.

Sincerely,
John Kitchens


Sub GetSheets()
Dim i As Long
Dim varr As Variant
Dim wkbk As Workbook
Dim ws As Worksheet
Application.DisplayAlerts = False
varr = Application.GetOpenFilename(filefilter:="Excel Files, *.xls", _
MultiSelect:=True)
If IsArray(varr) Then
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open(varr(i))
Set ws = wkbk.Sheets("Summary")
ws.Unprotect
ws.Name = Left(wkbk.Name, Len(wkbk.Name) - 4)
ws.Cells.Style = "Normal"
ws.DrawingObjects.Delete
ws.Copy After:=ThisWorkbook.Worksheets("Start")

wkbk.Close SaveChanges:=False
Next
End If
Application.DisplayAlerts = True
End Sub

Sub UnhideSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End Sub
 
I had a typo in my suggested line:

Set wkbk = Workbooks.Open(varr(i), UpdateLinks:=0, ReadOnly:=True)

Notice the extra colon in updatelinks:=0

Sorry.
 
Hello Dave,

When I made this latest change it works perfectly! Thank you so much.

I now have another problem I need help with. Should I post here or start a
new thread? (I am not sure of the rules of the forum)

Sincerely,

John Kitchens
 
Start a new thread.

I think that sometimes posts in existing threads get skipped over (I've been
told that this isn't true by others--but I know that I do it when it looks like
the discussion is going well).



John said:
Hello Dave,

When I made this latest change it works perfectly! Thank you so much.

I now have another problem I need help with. Should I post here or start a
new thread? (I am not sure of the rules of the forum)

Sincerely,

John Kitchens
Click to expand...
 
Back
Top