Solved

Excel Macro

Posted on 2013-01-30
7
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 30

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

696 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