Styjun

Did the Ottoman empire suppress the printing press?

Can I play a mimic PC?

Addressing unnecessary daily meetings with manager?

In Spider-Man: Far From Home, is this superhero name a reference to another comic book?

What happens when adult Billy Batson says "Shazam"?

When an electron changes its spin, or any other intrinsic property, is it still the same electron?

Is there a nice way to implement a conditional type with default fail case?

Why do you use the "park" gear to park a car and not only the handbrake?

Credit score and financing new car

Does a wizard need their hands free in order to cause their familiar from the Find Familiar spell to reappear?

What is /bin/red

How to tell someone I'd like to become friends without letting them think I'm romantically interested in them?

Why do we need common sense in AI?

Misrepresented my work history

How can I effectively communicate to recruiters that a phone call is not possible?

What's it called when the bad guy gets eaten?

LED glows slightly during soldering

Couldn't make condition-case work

Is there any reason why MCU changed the Snap to Blip

What is a "Lear Processor" and how did it work?

Can the spell Floating Disk cut objects?

Why does every calorie tracking app give a different target calorie count for the same goals?

Yet another hash table in C

Matrix with arrows and comments

How do I make this Excel macro save in .xls (Excel 97-2003) format, instead of .xlsx?

How to split spreadsheet into multiple spreadsheets with set number of rows?How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?How to avoid using Select in Excel VBAHow to change save type from excel workbook to excel 97-2003 in excel2010 using vba?VBA Excel macro message box auto closeSave Excel Workbook in different locationHow to reset sheet naming in ExcelExcel Macro: Lose line breaks pasting multiple (non-adjacent) rows into a different workbookVBA Macro lost after saving and closing as xslmcreating macro in excel

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

This macro is pretty close to exactly what I need:
How to split spreadsheet into multiple spreadsheets with set number of rows?

It splits a long Excel file into smaller files based on number of rows.

HOWEVER, I need to modify it to save the files as .xls (Excel 97-2003) format.

What do I need to change?

Here's what I'm working with right now:

Sub Test()
 Dim wb As Workbook
 Dim ThisSheet As Worksheet
 Dim NumOfColumns As Integer
 Dim RangeToCopy As Range
 Dim RangeOfHeader As Range 'data (range) of header row
 Dim WorkbookCounter As Integer
 Dim RowsInFile 'how many rows (incl. header) in new files?

 Application.ScreenUpdating = False

 'Initialize data
 Set ThisSheet = ThisWorkbook.ActiveSheet
 NumOfColumns = ThisSheet.UsedRange.Columns.Count
 WorkbookCounter = 1
 RowsInFile = 100 'as your example, just 10 rows per file

 'Copy the data of the first row (header)
 Set RangeOfHeader = ThisSheet.Range(ThisSheet.Cells(1, 1), ThisSheet.Cells(1, NumOfColumns))

 For p = 2 To ThisSheet.UsedRange.Rows.Count Step RowsInFile - 1
 Set wb = Workbooks.Add

 'Paste the header row in new file
 RangeOfHeader.Copy wb.Sheets(1).Range("A1")

 'Paste the chunk of rows for this file
 Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 2, NumOfColumns))
 RangeToCopy.Copy wb.Sheets(1).Range("A2")

 'Save the new workbook, and close it
 wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter
 wb.Close

 'Increment file counter
 WorkbookCounter = WorkbookCounter + 1
 Next p

 Application.ScreenUpdating = True
 Set wb = Nothing
End Sub

excel vba

share|improve this question

edited Mar 26 at 0:36

BigBen

8,56933 gold badges88 silver badges2121 bronze badges

asked Mar 26 at 0:35

JWgoldenbJWgoldenb

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The Workbook.SaveAs and XlFileFormat enumeration documentation should give you what you need.
  
  – BigBen
  Mar 26 at 0:38
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I see where I can get variable for FileFormat in both vaue and number format. I'm having trouble getting them inserted into the macro without breaking it.
  
  – JWgoldenb
  Mar 26 at 2:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Actually, I think that worked. I just changed this: wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter to this: wb.SaveAs Filename:=ThisWorkbook.Path & "splitoutput" & WorkbookCounter, _ FileFormat:=xlExcel8 and that did the trick.
  
  – JWgoldenb
  Mar 26 at 2:17
  
  

  
  
  
  

add a comment | 

0

This macro is pretty close to exactly what I need:
How to split spreadsheet into multiple spreadsheets with set number of rows?

It splits a long Excel file into smaller files based on number of rows.

HOWEVER, I need to modify it to save the files as .xls (Excel 97-2003) format.

What do I need to change?

Here's what I'm working with right now:

Sub Test()
 Dim wb As Workbook
 Dim ThisSheet As Worksheet
 Dim NumOfColumns As Integer
 Dim RangeToCopy As Range
 Dim RangeOfHeader As Range 'data (range) of header row
 Dim WorkbookCounter As Integer
 Dim RowsInFile 'how many rows (incl. header) in new files?

 Application.ScreenUpdating = False

 'Initialize data
 Set ThisSheet = ThisWorkbook.ActiveSheet
 NumOfColumns = ThisSheet.UsedRange.Columns.Count
 WorkbookCounter = 1
 RowsInFile = 100 'as your example, just 10 rows per file

 'Copy the data of the first row (header)
 Set RangeOfHeader = ThisSheet.Range(ThisSheet.Cells(1, 1), ThisSheet.Cells(1, NumOfColumns))

 For p = 2 To ThisSheet.UsedRange.Rows.Count Step RowsInFile - 1
 Set wb = Workbooks.Add

 'Paste the header row in new file
 RangeOfHeader.Copy wb.Sheets(1).Range("A1")

 'Paste the chunk of rows for this file
 Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 2, NumOfColumns))
 RangeToCopy.Copy wb.Sheets(1).Range("A2")

 'Save the new workbook, and close it
 wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter
 wb.Close

 'Increment file counter
 WorkbookCounter = WorkbookCounter + 1
 Next p

 Application.ScreenUpdating = True
 Set wb = Nothing
End Sub

excel vba

share|improve this question

edited Mar 26 at 0:36

BigBen

8,56933 gold badges88 silver badges2121 bronze badges

asked Mar 26 at 0:35

JWgoldenbJWgoldenb

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The Workbook.SaveAs and XlFileFormat enumeration documentation should give you what you need.
  
  – BigBen
  Mar 26 at 0:38
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I see where I can get variable for FileFormat in both vaue and number format. I'm having trouble getting them inserted into the macro without breaking it.
  
  – JWgoldenb
  Mar 26 at 2:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Actually, I think that worked. I just changed this: wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter to this: wb.SaveAs Filename:=ThisWorkbook.Path & "splitoutput" & WorkbookCounter, _ FileFormat:=xlExcel8 and that did the trick.
  
  – JWgoldenb
  Mar 26 at 2:17
  
  

  
  
  
  

add a comment | 

This macro is pretty close to exactly what I need:
How to split spreadsheet into multiple spreadsheets with set number of rows?

It splits a long Excel file into smaller files based on number of rows.

HOWEVER, I need to modify it to save the files as .xls (Excel 97-2003) format.

What do I need to change?

Here's what I'm working with right now:

Sub Test()
 Dim wb As Workbook
 Dim ThisSheet As Worksheet
 Dim NumOfColumns As Integer
 Dim RangeToCopy As Range
 Dim RangeOfHeader As Range 'data (range) of header row
 Dim WorkbookCounter As Integer
 Dim RowsInFile 'how many rows (incl. header) in new files?

 Application.ScreenUpdating = False

 'Initialize data
 Set ThisSheet = ThisWorkbook.ActiveSheet
 NumOfColumns = ThisSheet.UsedRange.Columns.Count
 WorkbookCounter = 1
 RowsInFile = 100 'as your example, just 10 rows per file

 'Copy the data of the first row (header)
 Set RangeOfHeader = ThisSheet.Range(ThisSheet.Cells(1, 1), ThisSheet.Cells(1, NumOfColumns))

 For p = 2 To ThisSheet.UsedRange.Rows.Count Step RowsInFile - 1
 Set wb = Workbooks.Add

 'Paste the header row in new file
 RangeOfHeader.Copy wb.Sheets(1).Range("A1")

 'Paste the chunk of rows for this file
 Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 2, NumOfColumns))
 RangeToCopy.Copy wb.Sheets(1).Range("A2")

 'Save the new workbook, and close it
 wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter
 wb.Close

 'Increment file counter
 WorkbookCounter = WorkbookCounter + 1
 Next p

 Application.ScreenUpdating = True
 Set wb = Nothing
End Sub

excel vba

share|improve this question

edited Mar 26 at 0:36

BigBen

8,56933 gold badges88 silver badges2121 bronze badges

asked Mar 26 at 0:35

JWgoldenbJWgoldenb

1

Sub Test()
 Dim wb As Workbook
 Dim ThisSheet As Worksheet
 Dim NumOfColumns As Integer
 Dim RangeToCopy As Range
 Dim RangeOfHeader As Range 'data (range) of header row
 Dim WorkbookCounter As Integer
 Dim RowsInFile 'how many rows (incl. header) in new files?

 Application.ScreenUpdating = False

 'Initialize data
 Set ThisSheet = ThisWorkbook.ActiveSheet
 NumOfColumns = ThisSheet.UsedRange.Columns.Count
 WorkbookCounter = 1
 RowsInFile = 100 'as your example, just 10 rows per file

 'Copy the data of the first row (header)
 Set RangeOfHeader = ThisSheet.Range(ThisSheet.Cells(1, 1), ThisSheet.Cells(1, NumOfColumns))

 For p = 2 To ThisSheet.UsedRange.Rows.Count Step RowsInFile - 1
 Set wb = Workbooks.Add

 'Paste the header row in new file
 RangeOfHeader.Copy wb.Sheets(1).Range("A1")

 'Paste the chunk of rows for this file
 Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 2, NumOfColumns))
 RangeToCopy.Copy wb.Sheets(1).Range("A2")

 'Save the new workbook, and close it
 wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter
 wb.Close

 'Increment file counter
 WorkbookCounter = WorkbookCounter + 1
 Next p

 Application.ScreenUpdating = True
 Set wb = Nothing
End Sub

share|improve this question

edited Mar 26 at 0:36

BigBen

8,56933 gold badges88 silver badges2121 bronze badges

asked Mar 26 at 0:35

JWgoldenbJWgoldenb

1

share|improve this question

edited Mar 26 at 0:36

BigBen

8,56933 gold badges88 silver badges2121 bronze badges

asked Mar 26 at 0:35

JWgoldenbJWgoldenb

1

edited Mar 26 at 0:36

BigBen

8,56933 gold badges88 silver badges2121 bronze badges

edited Mar 26 at 0:36

BigBen

8,56933 gold badges88 silver badges2121 bronze badges

asked Mar 26 at 0:35

JWgoldenbJWgoldenb

1

asked Mar 26 at 0:35

JWgoldenbJWgoldenb

1

1 Answer
1

active

oldest

votes

0

The Workbook.SaveAs and xlFileFormat enumeration documentation are helpful here.

Change

wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter

to

wb.SaveAs FileName:=ThisWorkbook.Path & "splitoutput" & WorkbookCounter, FileFormat:=xlWorkbookNormal

share|improve this answer

answered Mar 26 at 2:11

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55348277%2fhow-do-i-make-this-excel-macro-save-in-xls-excel-97-2003-format-instead-of%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

The Workbook.SaveAs and xlFileFormat enumeration documentation are helpful here.

Change

wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter

to

wb.SaveAs FileName:=ThisWorkbook.Path & "splitoutput" & WorkbookCounter, FileFormat:=xlWorkbookNormal

share|improve this answer

answered Mar 26 at 2:11

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

add a comment | 

0

The Workbook.SaveAs and xlFileFormat enumeration documentation are helpful here.

Change

wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter

to

wb.SaveAs FileName:=ThisWorkbook.Path & "splitoutput" & WorkbookCounter, FileFormat:=xlWorkbookNormal

share|improve this answer

answered Mar 26 at 2:11

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

add a comment | 

The Workbook.SaveAs and xlFileFormat enumeration documentation are helpful here.

Change

wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter

to

wb.SaveAs FileName:=ThisWorkbook.Path & "splitoutput" & WorkbookCounter, FileFormat:=xlWorkbookNormal

share|improve this answer

answered Mar 26 at 2:11

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

wb.SaveAs ThisWorkbook.Path & "splitoutput" & WorkbookCounter

wb.SaveAs FileName:=ThisWorkbook.Path & "splitoutput" & WorkbookCounter, FileFormat:=xlWorkbookNormal

share|improve this answer

answered Mar 26 at 2:11

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

answered Mar 26 at 2:11

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

answered Mar 26 at 2:11

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges