Excel Macro

I've got a macro set up to save some excel files in 2003 but it only saves 16384 rows. Is there a way to alter the code to get all rows? I wonder if it has to do with the 'xlExcel5'?

Thanks!
Deanna

Dim sFileName As String
Sub SaveData()
'  This procedure is used to save data out of
'  excel from People Soft downloads

Dim sDir As String
Dim sFile As String
Dim sQuery As String
Dim i As Integer
Dim j As Integer
Dim sRange As String
Dim sCellA1 As String
Dim sXL As String


sXL = "Excel_File_Save.xls"
sQuery = Range("A1").Value

sDir = "None"
sFile = "None"

'  Determine the directory name as indicated on the spreadsheet

i = 1
j = 50

Do
    With Workbooks(sXL)
        With .Worksheets("Directories")
            sRange = .Range("A" + CStr(i))
        End With
    End With
    If Left(sQuery, 2) = sRange Then
        sDir = Workbooks(sXL).Worksheets("Directories").Range("B" + CStr(i))
    End If
    i = i + 1
Loop Until i = j

'  Determine the file name as indicated on the spreadsheet
i = 1
j = 100
Do
    With Workbooks(sXL)
        With .Worksheets("DataFiles")
            sRange = .Range("A" + CStr(i))
        End With
    End With
    If sQuery = sRange Then
        sFile = Workbooks(sXL).Worksheets("DataFiles").Range("C" + CStr(i))
        sCellA1 = Workbooks(sXL).Worksheets("DataFiles").Range("B" + CStr(i))
    End If
    i = i + 1
Loop Until i = j

'  If neither a file name or a directory were encountered,
'  exit the procedure
If sFile = "None" Or sDir = "None" Then
    MsgBox "No corresponding record in excel", vbOKOnly, "Error"
    Exit Sub
End If

'  Replace the proper vallue in cell A1
Range("A1").Value = sCellA1

sFileName = sDir + "\" + sFile

Call Text_Format
Call Save_File


End Sub

Public Sub Text_Format()
'  this procedure is used to format all data on the spreadsheet
'  to a text
Selection.CurrentRegion.Select
Selection.NumberFormat = "@"
Range("A1").Select
End Sub
Public Sub Save_File()
Dim test As Boolean
'  Procedure deletes existing file and saves the current file

'  Check to see if a previous file exists, if so,
'  delete the existing file
test = FileExists(sFileName)
If FileExists(sFileName) = True Then
    Kill sFileName
End If

ActiveWorkbook.SaveAs sFileName, xlExcel5, "", "", False, False
ActiveWorkbook.Close
End Sub
Public Function FileExists(filename) As Boolean
'  Function used to see if a file exists
    FileExists = (Dir(filename) <> "")
End Function
dminx13Asked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
0
 
redmondbConnect With a Mentor Commented:
Hi, dminx13.

Yes well spotted. Is there a reason for using that early a version? If not, you might want to change it to xlExcel8 instead.

(Or install the Compatibility Pack and use the current versions.)

Regards,
Brian.
0
 
dminx13Author Commented:
Thanks. I am at the whim of a Corporation and that is the version we are on. This summer we are moving to 2010. Would that be xlExcel8?

Thanks!
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
redmondbCommented:
Thanks, dminx13.

No, that's Excel 97/2003 (which is the default format for your Excel version). The 2010 versions are
xlsx - xlOpenXMLWorkbook
xlsm - xlOpenXMLWorkbookMacroEnabled
xlsb - xlExcel12

Edit: So your not so stick-in-the-mud corporation's current version could still potentially handle all of the current Excel file versions - including quite a few that 2007/2010/2013 don't handle.

Regards,
Brian.
0
 
dminx13Author Commented:
So the xlExcel8 should work in my existing environment? Because I tried it and it said: Method 'SaveAs' of object '_Workbook' failed.
0
 
redmondbCommented:
dminx13,

Unfortunately MS renamed the  format variables on a number of occasions. Try using 56 (the number, not the string) instead. Meanwhile, I'm digging out Excel 2003's codes?

(BTW, not using "Option Explicit"?!)

Edit: Wow, I found it scarily difficult to find the 2003 information on Microsoft.Com. Please see this link - no numbers, but let me know if you have any difficult producing them.

Regards,
Brian.
0
 
dminx13Author Commented:
Brian,
Thanks. I will try it when I go to work tomorrow and let you know.
Deanna
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.