Solved

Excel Macro

Posted on 2013-01-30
7
217 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now