C
c1802362
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
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