VBS to import into excel

[Quinn]

Hi,
I am writing a VBS to import the text into Excel. The problem is whenever my data is in the form of '01-7008', it imports into Excel as 'Jan-08'.

Please help.
Thanks
Quinn

 

[Qroozn]

it is reading the format of your cell.
try chnaging the format of the escel it is importing to... and then importing text only.

 

[Quinn]

Can you show me how to do that?

Thanks.

 

[Qroozn]

format the page you are importing to..

1. highlight the entire page. then goto format cells.... text.

2. i dont know what your code for your import is. but excel vba has code along the lines of paste xlvalueonly =true


(I am not the best person at excel(infact I'm one of the worst)). just try and expand from what ahs already been shown and from search results on previous posts.

 

[Rocky E]

Hi,
What Qroozn is saying is correct. If you wish to import text (that is what you are saying) you must first format the column or range for text. There are several ways to do that. For example highlight a cell or column or range then FORMAT->CELLS->TEXT. Now your import will work, but this must be done before the import.


_________________
Hope this helps!

Rocky

"Be not the first by whom the New are try'd,
Nor yet the last to lay the Old aside."
Alexander Pope (1688-1744).
This message was edited by Rocky E on 2002-05-02 18:14

 

[Quinn]

Hi

Thanks for all the help
I tried that already. It didn't work.

In my vbs, I created the object, open the spread sheet, and import data from the database. So I can't set the format of the cell prior to import.

There must be a way to set the cell format in vbs, but I am sorta new to vbs, I don't know all the methods and properties yet.

Thanks.

 

[kkknie]

I'm not quite sure what you mean by importing to excel from vbs (VBScript?), but here's some possibly useless info.

If the script creates a new excel spreadsheet, then you will may be able to refer to is like this:

Application.Workbooks("Book1").Sheets("Sheet1").Columns("F:F").NumberFormat = "@"

This command will format Book1, Sheet1, ColumnF to text from any other excel workbook. The trick will be figuring out what to use in place of (or before) application...

If you figure it out, I'd love to know since I am thinking of some applications of my own right now.

K

 

[Quinn]

Hi kkknie,

Yes, I was trying to use VBScript to pull info from the database and spit it out onto an excel spreadsheet.

Your Info wasn't useless. Infact, IT WORKED.
That's what I need to know! the 'NumberFormat' property!

I am not sure what you mean by "figuring out what to use in place of (or before) application..."
But here is the script...


Option Explicit

Dim xlexport
Dim rowIndex
Dim Qty_Lookup
Dim Total_qty
Dim fiscal_yr

rowIndex = 1

Sub Initialize
Set xlexport = CreateObject("Excel.Application")
xlexport.Workbooks.Add
xlexport.Visible = vbTrue
fiscal_yr = datecyr(CATALOG_BIDS.BUDGETYEAR)
End Sub

Sub PageHeading
xlexport.ActiveSheet.Cells(rowIndex,"A").Value = "Product ID #"
xlexport.ActiveSheet.Cells(rowIndex,"B").Value = "Qty Ordered"
xlexport.ActiveSheet.Cells(rowIndex,"C").Value = "Unit"
xlexport.ActiveSheet.Cells(rowIndex,"D").Value = "Description"

rowIndex = rowIndex + 1

End Sub

Sub GroupHeader_CATALOG_ORDERS_PRODUCTID
Set Qty_Lookup = CreateSQLCursor
Qty_Lookup.sql = "Select sum(orderqty) qty from catalog_orders where budgetyear = '" & fiscal_yr & "' and productid = '" & CATALOG_ORDERS.PRODUCTID & "'"

Qty_Lookup.MoveNext
Total_qty = Qty_Lookup.qty
xlexport.ActiveSheet.Cells(rowIndex, "A").Value = CATALOG_ORDERS.PRODUCTID
xlexport.ActiveSheet.Cells(rowIndex, "B").Value = Total_qty
xlexport.ActiveSheet.Cells(rowIndex, "C").Value = PE_PROD_MSTR.PE_UNIT
xlexport.ActiveSheet.Cells(rowIndex, "D").NumberFormat = "@"
xlexport.ActiveSheet.Cells(rowIndex, "D").Value = PEDESC.DESC
rowIndex = rowIndex + 1
End Sub



THANKS A BUNCH!

 

[kkknie]

Much thanks for you posting your code.

My problem was that I had never activated an excel application from VBS and wasn't sure if you used Application("Excel"). or Excel.Application or some such nonsense.

Using the CreateObject function clears everything up. It's always nice to learn something when trying to answer other people's questions.

K

 

[RogerC]

On a similar subject... I have a column of dates in the format of 1/01/02. Excel won't let me change the format of this column. No matter what format I try to apply, it still reads the cells in the column as a date serial number (35649). I need it to see the data as text so that I can trim the month off each date. any thoughts?