Convert to Long Integer in Loop
Results 1 to 3 of 3

Thread: Convert to Long Integer in Loop
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2008
    Location
    Austria
    Posts
    1,082
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Convert to Long Integer in Loop

    Hi guys,

    as I am importing an Excel Worksheet into my access database I need some assistance to convert a column in Excel to a longInteger Field in Access.

    Code:
    Sub FillAllCellsWithLongInteger()
        Dim Zeile As Integer
        Dim ZeileMax As Long
        
    Application.ScreenUpdating = False
        
        With ActiveSheet
            ZeileMax = .Cells(Rows.Count, 1).End(xlUp).Row
        
            For Zeile = 2 To ZeileMax
                .Cells(Zeile, 17).Value = CLng(59)
            Next
        End With
        
    Application.ScreenUpdating = True
    End Sub
    This is what I tried.

    In Excel the column(17) is blank and I fill it with the number 59. This is a dummy so to say .. As I need this column to be a field with longInteger datatype in Access I did try to convert it to Long in my code.
    When I import that worksheet in access I get doule Datatyp tough. So I was wondering if someone knows how to be able to get around this.

    Many thanks

    Albert
    Silentwolf



    I use MS Office 2010 and Windows 7

  2. #2
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,281
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert to Long Integer in Loop

    It will be more interesting to see the code that does the import, if it is done by code that is.
    Do you run the import code from access or from excel.
    If you do it manually you can easily change the data format /Field size manually.
    If you import in an existing table you can set the field size manually in advance.

    Turns out DAO does not allow changing an existing field size you can use SQL:
    Code:
    CurrentDB.Execute "ALTER TABLE tblMytable ALTER COLUMN fldMyField LONG"
    change the red ones to match you case.
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  3. #3
    Board Regular
    Join Date
    May 2008
    Location
    Austria
    Posts
    1,082
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert to Long Integer in Loop

    Hi thanks Bosan42,

    I do the import with access and yes via code.. it's just a TransferSpreadsheet Code

    Code:
    Sub ImportExcelSpreadsheet(FileName As String, TableName As String)
    On Error GoTo BadFormat
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, TableName, FileName, True
        Exit Sub
    BadFormat:
        MsgBox "Das war keine Excel Datei!"
    End Sub
    But I will try it with your SQL Statement .

    Many Thanks
    Silentwolf



    I use MS Office 2010 and Windows 7

Some videos you may like

User Tag List

Tags for this Thread

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
  •