Did you change in the line
Files(*.xls), *.xls", , "Open Target Workbook (FileSplit Data)", , False)
If yes, then what is the error you are getting?

Also do you have FileSplit driver?

Do remember that in Excel 2007 workbooks with macros are named XLSM

MsgBox "Make sure you have downloaded and saved the FileSplit driver to
your computer before using.", vbOKOnly, "Error Opening Workbook"




"kevgret" wrote:

Hello again:

I think this is the macro that deals with the opening of the workbook.

I tried changing the .xls to .xlsx but it still would not open my 2007
workbook.

Any suggestions on how to change the macro below to read my 2007 workbook?



Option Explicit

Private Sub cboSelectWksht_DropButtonClick()
Dim strTargetWkbk As String, strTargetWksht As String
Dim objWkbk As Workbook, objWksht As Worksheet
Dim i As Integer, j As Integer

On Error GoTo ErrHandler

'flush combobox
j = Application.ActiveSheet.cboSelectWksht.ListCount
For i = 0 To j - 1
Application.ActiveSheet.cboSelectWksht.RemoveItem 0
Next i

strTargetWkbk = Application.ActiveSheet.txtTargetWkbk
If strTargetWkbk = "" Then 'driver is target workbook
strTargetWkbk = Application.ActiveWorkbook.Name

'add items to worksheet combo box
For Each objWksht In Workbooks(strTargetWkbk).Worksheets
strTargetWksht = objWksht.Name
If strTargetWksht < "NameMatch" Then
Application.ActiveSheet.cboSelectWksht.AddItem strTargetWksht
End If
Next objWksht
Else:
'verify workbook is open
For Each objWkbk In Workbooks
If objWkbk.Name = strTargetWkbk Then 'populate combobox with worksheets
For Each objWksht In Workbooks(strTargetWkbk).Worksheets
strTargetWksht = objWksht.Name
Application.ActiveSheet.cboSelectWksht.AddItem strTargetWksht
Next objWksht
End If
Next objWkbk
End If

If Application.ActiveSheet.txtTargetWkbk.Value = "" Then
Cells(28, 3) = "this"
End If

Cells(29, 3) = cboSelectWksht

SubExit:
Exit Sub

ErrHandler:
MsgBox "Error " & Err.Number & vbNewLine & vbNewLine & Err.Description,
vbOKOnly, "Driver Error"
GoTo SubExit

End Sub

Private Sub cmdOpenWkbk_Click()

Dim strActiveWkbk As String, strActiveWkSht As String, strTargetWkbkPath As
String
Dim i As Integer, j As Integer, strTargetWkbkFileName As String, strChk As
String
Dim w As Worksheet

On Error GoTo ErrHandler

strActiveWkbk = Application.ActiveWorkbook.Name
strActiveWkSht = Application.ActiveSheet.Name
strTargetWkbkPath = Application.GetOpenFilename("Microsoft Excel
Files(*.xls), *.xls", , "Open Target Workbook (FileSplit Data)", , False)

If strTargetWkbkPath < "False" Then 'open file and get filename from
returned path + filename
Workbooks.Open strTargetWkbkPath
i = Len(strTargetWkbkPath)
Do
strChk = Mid(strTargetWkbkPath, i, 1)
i = i - 1
Loop While (strChk < "\") And (strChk < "/")
strTargetWkbkFileName = Mid(strTargetWkbkPath, i + 2)
'set text box with filename
Workbooks(strActiveWkbk).Worksheets(strActiveWkSht ).txtTargetWkbk.Value
= strTargetWkbkFileName
Workbooks(strActiveWkbk).Worksheets(strActiveWkSht ).Cells(28, 3) =
strTargetWkbkFileName

'flush combobox
j =
Workbooks(strActiveWkbk).Worksheets(strActiveWkSht ).cboSelectWksht.ListCount
For i = 0 To j - 1

Workbooks(strActiveWkbk).Worksheets(strActiveWkSht ).cboSelectWksht.RemoveItem
0
Next i

'set cboSelectWksht with worksheets in target workbook
For Each w In Workbooks(strTargetWkbkFileName).Worksheets

Workbooks(strActiveWkbk).Worksheets(strActiveWkSht ).cboSelectWksht.AddItem
w.Name
Next w
End If
If Workbooks(strActiveWkbk).Worksheets(strActiveWkSht ).txtTargetWkbk.Value =
"" Then
Cells(28, 3) = "this"
End If

SubExit:
Exit Sub

ErrHandler:
If Err < 1004 Then
MsgBox "Error " & Err.Number & vbNewLine & vbNewLine & Err.Description,
vbOKOnly, "Driver Error"
Else
MsgBox "Make sure you have downloaded and saved the FileSplit driver to
your computer before using.", vbOKOnly, "Error Opening Workbook"
End If
GoTo SubExit

End Sub

Public Sub DataCheck_Click()
On Error GoTo ErrHandler

fsCheckData

ErrExit:
Cells(1, 1).Select
Exit Sub
ErrHandler:
MsgBox "Error " & Err.Number & vbNewLine & Err.Description, vbOKOnly,
"Driver Error"
GoTo ErrExit

End Sub




"kevgret" wrote:



"Gord Dibben" wrote:

Which version of Excel are you using?

I am using excel 2007.


What do you mean by "open a macro"?

There is a macro on my spreadsheet which links to another workbook. When I
click on the maco called "Open Target workbook" I get the option to select
the file where my data is stored for the macro. The problem is I can't
filter the file extensions for .xlsx. I only have the option to open a .xls
format spreadsheet.


Post the code.

Is this the code?

Sub fsCheckData()

fsInitializeVariables

' Check the Mapping and Desc fields to ensure we have the same rows in
both sheets
currRow = 0
For targetRow = targetMappingColumnFirstRow To targetMappingColumnLastRow
If Not
(Workbooks(targetWorkbook).Worksheets(targetWorksh eet).Rows(targetRow).Hidden) Then
If Not (Cells(fsStartRow + currRow, 1).Value = "") And _
(Not (Cells(fsStartRow + currRow, 1).Value =
Workbooks(targetWorkbook).Sheets(targetWorksheet). Cells(targetRow,
targetMappingColumn).Value) Or _
Not (Cells(fsStartRow + currRow, 2).Value =
Workbooks(targetWorkbook).Sheets(targetWorksheet). Cells(targetRow,
targetDescField1).Value) Or _
Not (Cells(fsStartRow + currRow, 3).Value =
Workbooks(targetWorkbook).Sheets(targetWorksheet). Cells(targetRow,
targetDescField2).Value)) Then
MsgBox ("Data Mismatch Error for Row " + Trim(Str(fsStartRow
+ currRow)) + Chr(13) + "Rows data in first three columns of this spreadsheet
must match those in target workbook.")
Exit Sub
End If
currRow = currRow + 1
End If
Next


' Now, re-copy the formula for the URL delimiter
' Reason: there have been bugs when adding / removing cells (ie: when
creating group / folder IDs)
' Easiest fix is to just re-copy as the last step in the process (when
the check / set data button is clicked)

' numberOfRows is the number of rows of client data in the
IntraLinksFileSplit worksheet
numberOfRows = targetMappingColumnLastRow - targetMappingColumnFirstRow
Range("P" + Trim(Str(fsTemplateRow)) + ":P" + Trim(Str(fsStartRow +
numberOfRows))).Select
Selection.FillDown
Range("P" + Trim(Str(fsStartRow)) + ":P" + Trim(Str(fsStartRow +
numberOfRows))).Select
Selection.Interior.ColorIndex = xlNone


targetNumRows = currRow - 1
'loop through every row and check for data
dataCheckList = ""
For currRow = fsStartRow To (fsStartRow + targetNumRows)
If Not (Cells(currRow, 1) = "") Then
If Not ((Cells(currRow, 5).Value = True) Or (Cells(currRow,
5).Value = False)) Then
dataCheckList = dataCheckList + "Cell E" +
Trim(Str(currRow)) + " must be True or False!" + Chr(13)
End If
If (Worksheets(fsSheetName).Cells(currRow, 5).Value = True) Then
'Upload=True
If (Worksheets(fsSheetName).Cells(currRow, 4).Value = "")
Then 'filename is empty
dataCheckList = dataCheckList + "Cell D" +
Trim(Str(currRow)) + " is empty!" + Chr(13)
End If
If (Worksheets(fsSheetName).Cells(currRow, 6).Value = "")
Then 'no workspace ID specified
dataCheckList = dataCheckList + "Cell F" +
Trim(Str(currRow)) + " is empty!" + Chr(13)
End If
If CStr(Worksheets(fsSheetName).Cells(currRow, 7).Value) =
"Error 2042" Then 'folderID lookup failed
dataCheckList = dataCheckList + "Cell G" +
Trim(Str(currRow)) + " is empty!" + Chr(13)
End If
If CStr(Worksheets(fsSheetName).Cells(currRow, 9).Value) =
"Error 2042" Then 'group ID
dataCheckList = dataCheckList + "Cell I" +
Trim(Str(currRow)) + " is empty!" + Chr(13)
End If
If Not ((Worksheets(fsSheetName).Cells(currRow, 11).Value =
"SEE") Or (Worksheets(fsSheetName).Cells(currRow, 11).Value = "CONTROL") Or
(Worksheets(fsSheetName).Cells(currRow, 11).Value = "S") Or
(Worksheets(fsSheetName).Cells(currRow, 11).Value = "C")) Then 'permission
type
dataCheckList = dataCheckList + "Cell K" +
Trim(Str(currRow)) + " must be SEE or CONTROL!" + Chr(13) +
Worksheets(fsSheetName).Cells(currRow, 11).Value
End If
If (Worksheets(fsSheetName).Cells(currRow, 12).Value = "")
Then 'pub title
dataCheckList = dataCheckList + "Cell L" +
Trim(Str(currRow)) + " is empty!" + Chr(13)
End If
If (Worksheets(fsSheetName).Cells(currRow, 13).Value = "")
Then 'effective date
dataCheckList = dataCheckList + "Cell M" +
Trim(Str(currRow)) + " is empty!" + Chr(13)
End If
If Not ((Worksheets(fsSheetName).Cells(currRow, 14).Value =
"DRMOn") Or (Worksheets(fsSheetName).Cells(currRow, 14).Value = "DRMOff") Or
(Worksheets(fsSheetName).Cells(currRow, 14).Value = "D2")) Then 'permission
type
dataCheckList = dataCheckList + "Cell N" +
Trim(Str(currRow)) + " must be DRMOn of DRMOff!" + Chr(13)
End If
If Not ((Worksheets(fsSheetName).Cells(currRow, 15).Value =
"SAT") Or (Worksheets(fsSheetName).Cells(currRow, 15).Value = "SAF") Or
(Worksheets(fsSheetName).Cells(currRow, 15).Value = "SAD")) Then 'send alert
type
dataCheckList = dataCheckList + "Cell O" +
Trim(Str(currRow)) + " must be SendAlertTrue or SendAlertFalse!" + Chr(13)
End If
If CStr(Worksheets(fsSheetName).Cells(currRow, 16).Value) =
"Error 2042" Then 'delimiter is empty
dataCheckList = dataCheckList + "Cell P" +
Trim(Str(currRow)) + " is empty!" + Chr(13)
End If
End If
End If
Next

'Make a call here to the function to check length of PDF names
dataCheckList = dataCheckList + CheckLengthOfPDFNames()


If (dataCheckList = "") Then
' Set the target column data