Solved

Excel Macro

Posted on 2013-01-30
7
241 Views
Last Modified: 2013-01-30
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
0
Comment
Question by:dminx13
  • 3
  • 3
7 Comments
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 250 total points
ID: 38836556
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
 
LVL 29

Accepted Solution

by:
gowflow earned 250 total points
ID: 38836566
0
 

Author Closing Comment

by:dminx13
ID: 38836736
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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 26

Expert Comment

by:redmondb
ID: 38836786
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
 

Author Comment

by:dminx13
ID: 38836850
So the xlExcel8 should work in my existing environment? Because I tried it and it said: Method 'SaveAs' of object '_Workbook' failed.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38837577
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
 

Author Comment

by:dminx13
ID: 38837690
Brian,
Thanks. I will try it when I go to work tomorrow and let you know.
Deanna
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question