Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBS to import into excel

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you show me how to do that?

    Thanks.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Phoenix, Arizona
    Posts
    529
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •