excel 2003 vs 2007 menu and sheet issues
 

I need some help with the following 2 situations:

1) In Excel 2003/Windows XP, I created a pulldown on the menu bar that
launches a number of routines. When the same code is run on Excel 2007/
Windows 7, the menu is nowhere to be found. The only way to access the
macros is from the Add-in button on the ribbon (and even when that
button is selected, the user needs to select another sub-button to see
the macros. How do I add the macros on the command bar in Excel 2007
so that they're obvious to the user? (I don't have this as an add-in
as there is a detailed instruction sheet the user needs to have access
to)

2) The sub below asks the user to select a file to open, searches the
workbook for a sheet starting with the name of "master", then copies
that sheet to a second workbook containing the code to begin data
reduction.

The sub works perfectly in Excel 2003 running under Windows XP. When
it is run under Excel 2007/Windows 7, the following error message pops
up:

Run-time error 2004
Excel can not insert the sheets into the destination workbook because
it contains fewer rows and columns than the source workbook. To move
or copy the data to the destination workbook, you can select the data,
and then use the Copy and Paste commands to insert it into the sheets
of another workbook

This seems a bit odd as the sheet size in 2007 is much greater than
2003

So, can anyone explain why it doesn't work in Excel 2007 and what the
fix is?

Here's the code

Sub GetImportFile()

Dim Filt As Variant, FilterIndex As Integer, fileN As Variant
Dim shtSource As Worksheet, wbkSource As Workbook, wbkTarget As
Workbook
Dim SheetNum As Integer

Const Title = "Select a File to Import"
Const MasterSheetName = "Master"

Set wbkTarget = ActiveWorkbook

' set up list of file filters
Filt = "Excel 2003 Files (*.xls),*.xls," & _
"Excel 2007 Files (*.xlsx),*.xlsx," & _
"Comma Separated Files (*.csv), *.csv," & _
"ASCII Files (*.asc), *.asc," & _
"All Files (*.*),*.*"

FilterIndex = 1 ' display *.xls by default

'Get the file name
fileN = Application.GetOpenFilename(Filefilter:=Filt, _
FilterIndex:=FilterIndex, Title:=Title)

' exit if dialog box is canceled
If fileN = False Then
MsgBox "No file was selected"
Exit Sub
End If

On Error GoTo jump ' prevents error if fileN is already
open

Workbooks.Open Filename:=fileN

Set wbkSource = ActiveWorkbook

jump: ' Find master data sheet

For Each shtSource In Worksheets
If UCase(Left(shtSource.Name, 6)) = MasterSheetName Then
SheetNum = wbkTarget.Sheets.Count
shtSource.Copy After:=wbkTarget.Sheets(SheetNum)
GoTo LeaveThisRoutine
Exit Sub
End If
Next shtSource

MsgBox wbkSource.Name & " does not contain a tab starting with" &
vbCrLf & _
"'" & MasterSheetName & "'" & vbCrLf & vbCrLf & _
"Change the tab name on downloaded data" & vbCrLf & "to start
with " & _
MasterSheetName & " rerun the macro"

LeaveThisRoutine: wbkSource.Close
End Sub

excel 2003 vs 2007 menu and sheet issues
 

For #2:
What are the formats of the source/destination workbooks?
Are you running in compatability mode?

Tim

On Jan 30, 3:43*pm, c1802362 wrote:
I need some help with the following 2 situations:

1) In Excel 2003/Windows XP, I created a pulldown on the menu bar that
launches a number of routines. When the same code is run on Excel 2007/
Windows 7, the menu is nowhere to be found. The only way to access the
macros is from the Add-in button on the ribbon (and even when that
button is selected, the user needs to select another sub-button to see
the macros. How do I add the macros on the command bar in Excel 2007
so that they're obvious to the user? (I don't have this as an add-in
as there is a detailed instruction sheet the user needs to have access
to)

2) The sub below asks the user to select a file to open, searches the
workbook for a sheet starting with the name of "master", then copies
that sheet to a second workbook containing the code to begin data
reduction.

The sub works perfectly in Excel 2003 running under Windows XP. When
it is run under Excel 2007/Windows 7, the following error message pops
up:

Run-time error 2004
Excel can not insert the sheets into the destination workbook because
it contains fewer rows and columns than the source workbook. To move
or copy the data to the destination workbook, you can select the data,
and then use the Copy and Paste commands to insert it into the sheets
of another workbook

This seems a bit odd as the sheet size in 2007 is much greater than
2003

So, can anyone explain why it doesn't work in Excel 2007 and what the
fix is?

Here's the code

Sub GetImportFile()

Dim Filt As Variant, FilterIndex As Integer, fileN As Variant
Dim shtSource As Worksheet, wbkSource As Workbook, wbkTarget As
Workbook
Dim SheetNum As Integer

Const Title = "Select a File to Import"
Const MasterSheetName *= "Master"

Set wbkTarget = ActiveWorkbook

' set up list of file filters
* Filt = "Excel 2003 Files (*.xls),*.xls," & _
* * * * "Excel 2007 Files (*.xlsx),*.xlsx," & _
* * * * "Comma Separated Files (*.csv), *.csv," & _
* * * * "ASCII Files (*.asc), *.asc," & _
* * * * "All Files (*.*),*.*"

FilterIndex = 1 * ' display *.xls by default

'Get the file name
fileN = Application.GetOpenFilename(Filefilter:=Filt, _
* * * * * FilterIndex:=FilterIndex, Title:=Title)

' exit if dialog box is canceled
If fileN = False Then
* MsgBox "No file was selected"
* Exit Sub
End If

On Error GoTo jump * * * * * * ' prevents error if fileN is already
open

Workbooks.Open Filename:=fileN

Set wbkSource = ActiveWorkbook

jump: ' Find master data sheet

*For Each shtSource In Worksheets
* * If UCase(Left(shtSource.Name, 6)) = MasterSheetName Then
* * * * * SheetNum = wbkTarget.Sheets.Count
* * * * * * shtSource.Copy After:=wbkTarget.Sheets(SheetNum)
* * * * * * * *GoTo LeaveThisRoutine
* * * * * Exit Sub
* * End If
* Next shtSource

MsgBox wbkSource.Name & " does not contain a tab starting with" &
vbCrLf & _
* * * * "'" & MasterSheetName & "'" & vbCrLf & vbCrLf & _
* * * * "Change the tab name on downloaded data" & vbCrLf & "to start
with " & _
* * * * MasterSheetName & " rerun the macro"

LeaveThisRoutine: wbkSource.Close
End Sub

excel 2003 vs 2007 menu and sheet issues
 

On Jan 30, 6:55*pm, Tim Williams wrote:
For #2:
What are the formats of the source/destination workbooks?
Are you running in compatability mode?

Tim

On Jan 30, 3:43*pm, c1802362 wrote:



The formats of the source and destination sheets are identical.
However, I'll try pasting the data from the downloaded file in as
values on a new sheet and see if that solves the problem. I'm not sure
what compatibility mode is. Can you elaborate?

Art

excel 2003 vs 2007 menu and sheet issues
 

On Jan 30, 6:07*pm, c1802362 wrote:
On Jan 30, 6:55*pm, Tim Williams wrote: For #2:
What are the formats of the source/destination workbooks?
Are you running in compatability mode?

Tim

On Jan 30, 3:43*pm, c1802362 wrote:

The formats of the source and destination sheets are identical.
However, I'll try pasting the data from the downloaded file in as
values on a new sheet and see if that solves the problem. I'm not sure
what compatibility mode is. Can you elaborate?

Art

This link outlines what Compatability Mode is:
http://technet.microsoft.com/en-us/l...ffice.12).aspx

Tim