Simple loop question 2

[brd24gor]

Howdy.

Hopefully this will prove to be a simple question with a simple answer. I am currently using VBA to format an Excel spreadsheet from an Access module before transferring it to a table in the database. I am writing a loop to format dates from YYYYMMDD to MM/DD/YYYY. While I have the code for re-arranging the dates written, I am having trouble finding a good way to loop through the cells in the column. I need to loop from C:2 to the end of the column. Here is my loop code:

Option Compare Database


Sub TestMacro()
    Dim xlsApp As Object
    Dim xlsFileName As String
    Dim xlsFilePath As String
    Dim year As String
    Dim day As String
    Dim month As String
    
    xlsFilePath = ***filepath name***
    xlsFileName = ***filename***
    Set xlsApp = GetObject(xlsFilePath & xlsFileName)
    xlsApp.Application.Visible = True
    xlsApp.Parent.Windows(1).Visible = True
    
    xlsApp.ActiveSheet.Cells(1, 1) = "UNIT"
    xlsApp.ActiveSheet.Cells(1, 2) = "SOURCE"
    xlsApp.ActiveSheet.Cells(1, 3) = "DATE"
    xlsApp.ActiveSheet.Cells(1, 4) = "BBCS"
    xlsApp.ActiveSheet.Cells(1, 5) = "PROD"
    xlsApp.ActiveSheet.Cells(1, 6) = "STATUS"
    xlsApp.ActiveSheet.Cells(1, 7) = "DISP"
    xlsApp.ActiveSheet.Cells(1, 8) = "LTD"
    xlsApp.ActiveSheet.Cells(1, 9) = "SPEC"

'**************
    For Each c In ***
        If c <> "DATE" Then
            day = Right(c, 2)
            month = Mid(c, 5, 2)
            year = Left(c, 4)
            If c <> Empty And c <> "DATE" Then
                 c = month & "/" & day & "/" & year
            End If
        End If
    Next c
'***************

    xlsApp.Application.Save
    xlsApp.Application.Quit
    
    DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel97, _
        tableName:="DailyTest", fileName:=xlsFilePath & xlsFileName, hasFieldNames:=True
End Sub

The problem area of the loop is outlined in ***. The way it is currently written, I would like c to equal the cell that's being formatted.

Thanks in advance for the help!

 

[SkipVought]

hi,

Sub TestMacro()
    Dim xlsApp As Object
    Dim xlsFileName As String
    Dim xlsFilePath As String
    Dim year
    Dim day
    Dim month
    
    xlsFilePath = ***filepath name***
    xlsFileName = ***filename***
    Set xlsApp = GetObject(xlsFilePath & xlsFileName)
    xlsApp.Application.Visible = True
    xlsApp.Parent.Windows(1).Visible = True
    
    With xlsApp.ActiveSheet
        .Cells(1, 1) = "UNIT"
        .Cells(1, 2) = "SOURCE"
        .Cells(1, 3) = "DATE"
        .Cells(1, 4) = "BBCS"
        .Cells(1, 5) = "PROD"
        .Cells(1, 6) = "STATUS"
        .Cells(1, 7) = "DISP"
        .Cells(1, 8) = "LTD"
        .Cells(1, 9) = "SPEC"

'**************
        For Each c In .Range(.Cells(2, "C"), .Cells(2, "C").End(xlDown))
            If Not IsDate(c.Value) And c.Value <> "" Then
                day = Right(c, 2)
                month = Mid(c, 5, 2)
                year = Left(c, 4)
                c.Value = DateSerial(year, month, day)
            End If
        Next c
'***************
    End With
    xlsApp.Save
    xlsApp.Application.Quit
    
    Set xlsApp = Nothing
    
    DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel97, _
        tableName:="DailyTest", Filename:=xlsFilePath & xlsFileName, hasFieldNames:=True
End Sub

the year, month, day values must be CONVERTED to a REAL DATE and NOT a string that LOOKS like a date.

BTW, xlsApp is really a WORKBOOK object in your code. Hence the SAVE of the workbook and the QUIT of the application

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[TonyJollans]

Hi brd24gor,

For this to make sense you must have text and not actual dates. Is that the case and is that what you really want? It's far easier to work with dates and format them as you want where you want (both in Excel and Access).

That said, there are all sorts of ways to work with the range you want. Here's one:
[blue][tt] For Each c In Range("C2", Range("C65536").End(xlUp))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[brd24gor]

The field in the table is in fact a date field. I have tested my code on a single row and it appeared to transfer to the database table fine as a string. However, I would much rather have cleaner code than to just work with what *appears* to be working.

Thanks for the heads-up about xlsApp being a Workbook object. I had thought that it was an Application. I'm sure that will prove most useful as I progress.

Thanks for the help guys, I'll keep you posted.

 

[brd24gor]

When I run Skip's code, I get an error on the For.. statement of my loop:

Run-time error 1004
Application-defined or object-defined error

Any clues as to what would cause this? It is an odd line for that to happen on as I usually see it when forgetting a 'Set' before setting a variable.

Thanks again,
Brad

 

[PHV]

Replace this:
xlDown
By this:
&HFFFFEFE7

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

I have run the code and it runs thru the for each...next loop.

Have you copied 'n' pasted my code into your module?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[PHV]

Skip, I guess the code is running in Access VBA with Late Binding ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[brd24gor]

PHV's suggestion did the trick. Craziness. No code errors, now.

However, now I am having something rather odd happen when the data gets brought in from the spreadsheet to the table. It is only bringing in the records which have data in all the fields. I double-checked my table design and only the primary key (an AutoNumber field not present in the spreadsheet) and the "Unit" field are required and unique.

*sigh*

That's what I get for saying this should be simple...

Brad

 

[SkipVought]

Is UNIT sometimes all NUMERIC characters and sometimes ALPHANUMERIC characters?

If so, perfix the all NUMERIC values with an [apostrophy]

For Each c In .Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown))
  if isnumeric(c.value) then
     c.value = "'" & c.value
  end if
next

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[brd24gor]

That could be part of the problem. My data is pulled off of an iSeries and when it goes into the spreadsheet, it appears as if the unit numbers are being read by Excel as text. My table is set for numeric characters (these unit numbers will always be numeric).

Is there an easy way to make sure the whole row is formatted numerically? Possibly:

.Range("A:A").Selection.NumberFormat = "0"?

You have been a huge help. Thank you very much!

--Brad

 

[SkipVought]

If EVERY value in that column is NUMERIC, then you should NOT have had a problem.

What I suggested applies ONLY if there were also ALPHA-NUMERIC values.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[brd24gor]

I think I have figured out what is causing the problem importing the spreadsheet to my table. My table has 9 data fields and 1 autonumber field for the primary key. If I import my data into a table with the autonumber ID taken out, all of my data comes in great. If I try it with that field in there, I only get a few of my rows in.

Any ideas?