Opening output in Excel

[eldnahmurof]

Hi,
I have successfully used VBA code within Excel to link to our database and retrieve data (I used record macro to get connection stuff). It uses QueryTable and outputs to a sheet within the workbook.
Now I have been asked to write this as vbscript instead. I have noticed that my Account Number field is not preserving leading zeroes (e.g. "01102..." becomes "1102...") and at first thought it was my connection string (maybe some difference between VBA and VBS) but then realized the data looks ok if I open it in Notepad.

Hence my question now is this. Rather than output to csv and then have the user manually open the file, where the data format is wrong, can I open the output directly in Excel and keep the "text" formatting I require? Should I write record by record to the spreadsheet, or rather continue to output to my text csv file and then open that file in Excel? The output needs to be csv-like, i.e. one field per cell.

I'm Googling for examples but as this is the first time I've used VBscript any help is most welcome!

Thanks,
Bob

 

[SkipVought]

Hi,

When Excel opens a .csv file, it can't see inside your head. Excel sees a NUMBER (which in this case is all numeric characters) and ASSUMES that it's a....

NUMBER!

If you want something other than a number, then DON'T MAKE THE VALUE A NUMBER. Append a leading APOSTROPHY. That way, Excel will KNOW that you want TEXT and not a NUMBER.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[eldnahmurof]

Hi,
Just what I need on a Friday afternoon, sarcasm... lol!
Seriously, though, thanks for answering and making sure I didn't overlook the obvious, which I did.

Strange that in VBA it worked and I didn't do any formating on it ...Excel must have read my mind, that time. ;-)

Anyway, I'm not losing the front of the data now but in the looks department it's not all there, with that single quote in front; especially noticable if opened from the .csv file.

When I run the VBA code it is just a clean value, no quote in sight. Maybe there is a difference between VBA and VBS for ODBC after all?

Cheers,
Bob

 

[eldnahmurof]

BTW, here's my code...

Dim objConn
dim sConn
dim rsTemp
dim sHeaders
dim sRecords
dim filOutput
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Detail"

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Set filOutput = fso.CreateTextFile("Detail.csv", True)
Set objConn = CreateObject("ADODB.Connection")
    sConn = "DSN=Taipei"
objConn.Open sConn

     sSql = "SELECT gl.AccountNo, gl.AccountNoFiller, gl.AccountDescription, ... <snip>"

set rstemp=objConn.Execute(sSql)
if rstemp.eof then
     filOutput.Write "No records matched" & chr(13)
     filOutput.Write "Detail paramter file query" & chr(13) & "So cannot make table..."
     objConn.Close
     filOutput.Close
     set filOutput=nothing
     set fso=nothing
     set objConn=nothing
     Wscript.End
end if

irow = 1
icol = 1

for each whatever in rstemp.fields
     sHeaders = sHeaders & chr(34) & whatever.name & chr(34) & "," 
     objExcel.cells(irow, icol)=whatever.name
     icol = icol + 1
next
sHeaders = left(sHeaders, len(sHeaders) -1)
filOutput.WriteLine sHeaders 

irow = 2
icol = 1

DO UNTIL rstemp.eof
for each rec in rstemp.fields
     thisfield=rec.value
     if isnull(thisfield) then
         thisfield=shownull
     end if
     if trim(thisfield)="" then
         thisfield=showblank
     end if
     if icol = 1 then
         objSheet.Cells(irow, icol) = chr(39) & thisfield 
         sRecords = sRecords & chr(39) & thisfield & ","
     else
         objSheet.Cells(irow, icol) = thisfield 
         sRecords = sRecords & thisfield & ","
     end if
     icol = icol + 1
     next
     sRecords = left(sRecords, len(sRecords) -1)
     filOutput.WriteLine sRecords
     irow = irow + 1
     if ( irow mod 50 = 0 ) then
	 wscript.echo irow & " records written"
     end if
     icol = 1
     rstemp.movenext
     sRecords = ""
LOOP
rstemp.close
set rstemp=nothing
objConn.Close
set objConn=nothing
filOutput.Close
set filOutput=nothing
set fso=nothing

Set objWorkbook = objExcel.ActiveWorkbook  
objWorkbook.SaveAs("C:\Detail.xls")  
objExcel.Visible = True
  
wscript.Echo "The File is Saved in C:\Detail.Xls"

I've cut down the SQL statement to save space.

 

[SkipVought]

Check out the CopyFromRecordset method of the ADO recordset object. You can place the data in the sheet with ONE STATEMENT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[eldnahmurof]

Thanks! I'll look into that now....

 

[PHV]

Skip, for me, CopyFromRecordset is a method of the Excel.Range object ...

 

[eldnahmurof]

Skip,
Definitely a step in the right direction! I replaced my DO UNTIL LOOP with

objSheet.Cells(irow, icol).CopyFromRecordset rstemp

Looks a lot better and almost as fast as the vba. However, the vba gives me sized columns and bold heading, which vbs is not giving me. Guess I'll have to play around with some formating.

Here's my vba code, just for comparison's sake:

Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable
    sConn = "ODBC;DSN=Taipei;ServerName=xxxxxx.1583;ServerDSN=QUERYDB;"
    sConn = sConn & "ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;"
    sConn = sConn & "ClientVersion=10.12.013.000;CodePageConvert=1252;"
    sConn = sConn & "PvClientEncoding=CP1252;PvServerEncoding=CP1252;"
    sConn = sConn & "QryPlan=1;QryPlanOutput=C:\PVSW\QPoutput.qpf;AutoDoubleQuote=0;"

sSql = "SELECT gl.AccountNo, gl.AccountNoFiller, gl.AccountDescription, <snip>"
     
    ThisWorkbook.Worksheets("Results").Activate

   Set oQt = ActiveSheet.QueryTables.Add( _
        Connection:=sConn, _
        Destination:=Range("a1"), _
        Sql:=sSql)

    oQt.Refresh

Note that the SQL statement sSql is the same in both.

 

[SkipVought]

PHV is correct.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]