Solved

Excel Macro

Posted on 2013-01-30
7
235 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 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