Results 1 to 14 of 14
  1. #1
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts

    Help with writing data using VBA

    I have a VBA script which reads data from a text file and writes it in a sheet. I have a problem with one variable though. The text file has the text as 1/60, and the variable its being put in is defined as a string. But when I write it to the sheet, it treats it as a date, calling it Jan-60. Changing the cell's format to text results in a number, 21916. The only way I've found to display it correctly is to predefine the column as text. But this is tedious (as I would have to faf around deciding how many rows to define (I'm constantly moving the data).

    Is there a simple way of ensuring the data is stored as text please.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Access,

    Access-mdb.JPG

    That's a double quote - single quote - double quote just in case you're eyes are as bad as mine!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    access-mdb (2015-11-09)

  4. #3
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts
    Magic RG, I thought it might be simple (tho' obscure!).

    The value 1/60 is camera shutter speed (that spreadsheet again). The sheet tells me it's an error with a text date with a two digit year. Can I tell it that it's just text and accept is as text. Ho hum....

    I suppose that telling it to accept the error will do this forever?

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Access,

    Post #3, Say WHAT? You lot me on that one.

    Prepending the apostrophe merely tells Excel that the value is text and to ignore the cell formatting.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts
    RG, a picture is worth a thousand words, hopefully this will make clear

    excel_error.png

    Note all the speeds less than 1/100 have the error, which is telling me that there's only two digits for the year and do I want to change it (no!). I can click the ignore error for them all - but I just wondered if there was a more elegant way.

    But then I wonder why MS considers this sort of thing an error - there's one exposure time which is 20 seconds and it flags that as a number stored as text.

    It's not an issue so I can live with it.
    Last edited by access-mdb; 2015-11-10 at 05:28.

  7. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    What happens if you enter the data with an apostrophe in front? '1/100

    cheers, Paul

  8. #7
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts
    Paul, now I've corrected the image upload, you can see that RG's suggestion added that apostrophe. It corrected the original problem of Excel converting the string to a date. This is just the error it flags, which is a tad annoying (No, it's not an error) but as I said, clicking ignore error solves the issue of the spurious error message.

    I've noticed that Excel produces all sorts of these 'error' messages, when they are precisely what I want, so not an error.

  9. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    I see now.
    The value of auto-correct is sometimes not.

    cheers, Paul

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Access,

    Ah, the Excel version of the Idiot Light ( you know like the dumb lights on your car's dash board).

    These were added to point out Possible Problems.....
    headbang.gif
    Click Pic to Animate!

    You can turn them off in settings at: File->Options->Formulas->Error Checking Rules:
    ExcelFormulaCheckingRules.JPG

    Note: If you just want it in this one workbook you can inculed VBA in an Auto_Open to the selected items off and then in the Workbook_BeforeClose event turn them back on.

    HTH :Cheers:
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can also ignore them at the range level:
    Code:
        With Range("A1")
            .Value = "'1/60"
            .Errors(xlTextDate).Ignore = True
        End With
    for example.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. The Following User Says Thank You to rory For This Useful Post:

    access-mdb (2015-11-11)

  13. #11
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts
    Thanks Rory, that looks like the simplest solution. RG, that might be an answer as well, but looks a bit more complicated than Rory's. I will mark this thread as solved in due course (just in case anyone else wants to chime in).

  14. #12
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts
    OK, not so simple. Where does the code go Rory? And what should the range argument be - the data is in column M (rather than A as in the screenshot).

    I've tried:
    Code:
    With Range("M1")
                        .Value = "'1/60"
                        .Errors(xlTextDate).Ignore = True
                   End With
    But with no change.

    Should it be with where the value is written?

    Code:
    .Cells(lLastRow, 13) = zExposureTime
    There are other writes as well.

    The value could also be 1/30 (perhaps other values, I've yet to check).
    Last edited by access-mdb; 2015-11-11 at 15:20. Reason: Wrong line corrected for the data write

  15. #13
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts
    Right a little bit of googling and some thought came up with the answer. My code now reads:

    Code:
                        With .Cells(lLastRow, 13)
                            .Errors(xlTextDate).Ignore = True
                            .Errors(xlNumberAsText).Ignore = True
                        End With
    This answers my question about other speeds e.g. 1/30, and also when I have a long exposure - 20 seconds has the error "Number stored as text". So all is now sweetness and light and I again thank you Rory and RG for your excellent suggestions.

    Did I ever say the Lounge rocks‽‽‽‽

  16. #14
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts
    As I said that works, but then moving the data to another sheet brings them back again. But, thought I, Excel is great at number crunching but is pants when it comes to being a database. Using Excel has been very useful to me in developing the scripts, but my final destination for the data is my photographic database, so I decided to use my Access VBA skills to convert the Excel script to Access.

    Just one problemette, I was getting all missing dates being reported as 00:00:00. After a bit of fiddling and Googling I discovered the Missing keyword (that's the keyword called Missing!). I'd never come across that before, but changing my datetime variable to a variant and setting it to the value Missing solved the problem. Never too late to learn something.

Posting Permissions

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