Convert to Text and Value

[Mr80s]

Hello Board!

Ok, I'm trying to muddle my way through a large bit of code I'm cobbling together. It does several things but it's not very efficient or clean.. Here's what I'd like to do:

-Prompt to open an xlsx file. (this works)
-Determine the range of data and copy it to a new workbook. The data contains many rows and columns some columns have numbers of varying lengths with leading zeros.

[TABLE="width: 892"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Student ID[/TD]
[TD]Role[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]Sally[/TD]
[TD]123456[/TD]
[TD]Student[/TD]
[TD]somewhere@something.com[/TD]
[/TR]
[TR]
[TD]Vader[/TD]
[TD]Darth[/TD]
[TD]00443[/TD]
[TD]Teacher[/TD]
[TD]somplace@somewhere.com[/TD]
[/TR]
[TR]
[TD]Lee[/TD]
[TD]Bruce[/TD]
[TD]00011111[/TD]
[TD]Kung Foo[/TD]
[TD]martial@arts.com[/TD]
[/TR]
[TR]
[TD]Gates[/TD]
[TD]Bill[/TD]
[TD]994422[/TD]
[TD]Programmer[/TD]
[TD]bill@microsoft.com[/TD]
[/TR]
[TR]
[TD]Happy[/TD]
[TD]Joe[/TD]
[TD]0123[/TD]
[TD]student[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I then parse this into a csv file with headings of:
sN,givenName,OtherID,Template,mail

I have a couple of problems.
1) I can copy the data and parse and change the headings but when I try to save as a .csv the original file must of formulas in it (the file can be from many sources and hundreds of rows long). So when I save as .csv it must be seeing formulas and converting to NULL as the end of the csv contains many rows of commas:
,,,,
,,,,
,,,,

So if I convert to value

With sourceBook.Sheets("Access Request Spreadsheet").UsedRange ThisWorkbook.Sheets("temp").Range("A1").Resize( _
        .Rows.Count, .Columns.Count) = .value

It removes the commas at the end of the file, BUT it also strips the leading zeros of my ID numbers which I need (and the numbers could be different lengths so I can't pre-format )

If I convert the range to text via

[COLOR=#303336][FONT=inherit]ThisWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"temp"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Resize[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Rows[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Count[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Columns[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Count[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]NumberFormat [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"@"[/FONT][/COLOR]

It doesn't strip the leading zeros which is good, BUT now it doesn't convert to values and so I'm getting the rows at the end of the csv with commas.

So I've tried to first convert to text, then to value. It doesn't strip the zeros, but it doesn't remove formulas and thus my csv is full of commas at the end.

Here's what I've tried:

  Set sourceBook = Workbooks.Open(fileName)With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
    End With
ThisWorkbook.Worksheets("temp").Range("A1").Resize( _
        .Rows.Count, .Columns.Count).NumberFormat = "@"
 With sourceBook.Worksheets("Access Request Spreadsheet").UsedRange
 ThisWorkbook.Sheets("temp").Range("A1").Resize( _
        .Rows.Count, .Columns.Count) = .value
 End With
 sourceBook.Close

I'd like to know how to:
-Open the workbook,
-find the range of data (could be hundreds of rows)
-strip out only the columns (via column headings) I want (I have this part working)
-write the rows with only the columns I want into a new file, rename the header row and save as .csv
-Keeping leading zeros and no rows of commas at the end of the file.

Anyone got any ideas?

Many thanks in advance!!

 

[Mr80s]

Well, I think I solved by issues.

Before saving to CSV, I clear all formatting within the usedrange. No more commas at the end of my CSV

sh2.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats

I really should clean up this code though as it could be way more efficient.