Importing Tables with Comments

[ConnorGiles]

Hey again guys

Need some help in finding some way to import my tables from excel into access 2007 with comments still inside their boxes.

one of the columns in my table is called "Order No" and each field contains a Comment as a note to go with the order.

Is there a way to keep this setting when importing the tables into access 2007?

Thanks

Connor

:banghead:

 

[madefemere]

Hello,

You can create a field for "comments" in the Access table and pull the value with the code :

 Dim com As String 
com = ActiveCell.Comment.Text

Good continuation

 

[ConnorGiles]

Where would i go about putting the code to extract the values into the access table - as ive tried importing the tables as they are but it just gets rid of the comments completely.

 

[gemma-the-husky]

the easiest way would probably be to insert a column in excel, and put the comments in a specific column, rather than having them as cell comments.

you want to make the transfer process as easy as possible with standard methods, I should think.

 

[ConnorGiles]

Don't think a copy and paste is an option - got over 90,000 fields with a comment in random columns so would like a column which intercepts all comments from each individual cell. and puts it in the corresponding comments cell in the row.

 

[gemma-the-husky]

I am not sure (don't think) you can do that with normal docmd.transfertext or docmd.transferspreadsheet

writing code to open and iterate the spreadsheet is much harder.

I still think you would be better adding a comments column, and writing excel vba to populate the comments column.

 

[ConnorGiles]

sorry to hassle you gemma but would you be willing to tell me how that is achieved ?

extremely new to vba

 

[Frothingslosh]

It's complicated. I have something similar over in the Code Repository - I'll see if I can't adapt something along the lines you're talking about, but it will take a little time.

 

[ConnorGiles]

Thanks FrothingSloth - Take as much time as you need.

Thanks for all your help everyone

 

[Frothingslosh]

Okay, here's what I think you said you're after. Please keep in mind that while it's BASED on working code, it has not actually been run. Read through it and make sure you can follow everything I did. If you have any problems, let us know.

Public Sub ImportXLS(ByVal FilePath As String, _
                     ByVal SheetName As String, _
                     ByVal DestTableName)
 
[COLOR=seagreen]'Note - This function assumes that the import table's field names and the source file's column headers are the same.[/COLOR]
[COLOR=seagreen]'       If they are not, it is strongly advised that you use a permanent staging table and clear it before[/COLOR]
[COLOR=seagreen]'           using this procedure.[/COLOR]
[COLOR=seagreen]'Note - This function assumes that the comment field for the orders is to be called "Order Comments".[/COLOR]
 
Dim Workspace As DAO.Workspace
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rs As DAO.Recordset
Dim LastColumn As Long
Dim LastRow As Long
Dim x As Long
Dim y As Long
Dim OrderCol As Long
Dim Headers() As String
Dim TransActive As Boolean
 
On Error GoTo ImportXLS_Err
 
[COLOR=seagreen]  'Open the workbook and worksheet.[/COLOR]
    Set wb = Excel.Application.Workbooks.Open(FilePath)
    Set ws = wb.Sheets(SheetName)
 
    [COLOR=seagreen]'Create the workspace object.[/COLOR]
    Set Workspace = DBEngine.Workspaces(0)
 
[COLOR=seagreen]  'Set LastColumn and LastRow[/COLOR]
    LastColumn = ws.UsedRange.Columns.Count
    LastRow = ws.UsedRange.Rows.Count
 
[COLOR=seagreen]  'Set the size of the array Headers.[/COLOR]
    ReDim Headers(1 To LastColumn + 1)
 
[COLOR=seagreen]  'Load the column names into Headers.[/COLOR]
    For x = 1 To LastColumn
        Headers(x) = ws.Cells(1, x).Value
 
[COLOR=seagreen]      'If the header for this row is "Order No", assign that column number to OrderCol.[/COLOR]
        If Headers(x) = "Order No" Then OrderCol = x
 
    Next x
 
[COLOR=seagreen]  'Add the field "Order Comments" to headers in the last spot (LastColumn + 1).[/COLOR]
    Headers(LastColumn + 1) = "Order Comments"
 
[COLOR=seagreen]  'Open the destination table for import.[/COLOR]
    Set rs = CurrentDb.OpenRecordset(DestTableName, dbOpenDynaset, dbAppendOnly)
 
[COLOR=seagreen]  'Create a progress bar.[/COLOR]
    SysCmd acSysCmdInitMeter, "Importing data...", LastRow
 
[COLOR=seagreen]  'Activate the hourglass cursor.[/COLOR]
    DoCmd.Hourglass True
 
    With rs
[COLOR=seagreen]      'Cycle through the rows in the spreadsheet.[/COLOR]
        For x = 1 To LastRow
 
[COLOR=seagreen]          'Begin the transaction.[/COLOR]
            Workspace.BeginTrans
            TransActive = True
 
[COLOR=seagreen]          'Create the new record.[/COLOR]
            .AddNew
 
[COLOR=seagreen]          'Cycle through the columns in the spreadsheet.[/COLOR]
            For y = 1 To LastColumn
 
[COLOR=seagreen]              'Copy the value from cell x,y into the corresponding field.[/COLOR]
                .Fields(Headers(y)).Value = Nz(ws.Cells(x, y))
 
            Next y
 
            [COLOR=seagreen]'Copy the note text from Order No.[/COLOR]
            .Fields("Order Comments").Value = Nz(ws.Cells(x, OrderCol).Comment.text)
 
            [COLOR=seagreen]'Save the record.[/COLOR]
            .Update
 
            [COLOR=seagreen]'Commit the transaction.[/COLOR]
            Workspace.CommitTrans
            TransActive = False
 
            [COLOR=seagreen]'Update the progress meter.[/COLOR]
            SysCmd acSysCmdUpdateMeter, x
 
        Next x
    End With
 
ImportXLS_Exit:
    On Error Resume Next
    [COLOR=seagreen]'Remove the meter.[/COLOR]
    SysCmd acSysCmdRemoveMeter
    [COLOR=seagreen]'Remove the hourglass cursor.[/COLOR]
    DoCmd.Hourglass False
    [COLOR=seagreen]'Check to see if the workspace reference is still set.[/COLOR]
    If Not Workspace Is Nothing Then
        [COLOR=seagreen]'Verify the transaction status.[/COLOR]
        If TransActive = True Then
            [COLOR=seagreen]'Roll back the transaction.[/COLOR]
            Workspace.Rollback
            TransActive = False
        End If
       [COLOR=seagreen]'Clear the workspace reference.[/COLOR]
        Set Workspace = Nothing
    End If
    [COLOR=seagreen]'Clear the worksheet reference if still set.[/COLOR]
    If Not ws Is Nothing Then Set ws = Nothing
    [COLOR=seagreen]'Check the workbook status.[/COLOR]
    If Not wb Is Nothing Then
        [COLOR=seagreen]'Close the workbook.[/COLOR]
        wb.Close
        [COLOR=seagreen]'Clear the workbook reference.[/COLOR]
        Set wb = Nothing
    End If
    [COLOR=seagreen]'Clear the recordset reference if still set.[/COLOR]
    If Not rs Is Nothing Then Set rs = Nothing
    [COLOR=seagreen]'Exit the sub.[/COLOR]
    Exit Sub
 
ImportXLS_Err:
    DoCmd.Hourglass False
    MsgBox "An error was encountered in procedure ImportXLS!" & vbCrLf & vbCrLf & _
           "Error Number: " & vbTab & Err.Number & vbCrLf & _
           "Error Desc:   " & vbTab & Err.Description, vbCritical
    Resume ImportXLS_Exit
 
End Sub

 

[Frothingslosh]

Also, please note that importing a spreadsheet this way is very, very, VERY slow.

 

[spikepl]

One mechanism not to forget are macros. In Word I'd notoriously record a macro to do some repetitive task, and simply tie it to a key. With many repetitions I'd even open the code and wrap a loop around it.

Macros in Word/Excel are simply records of what code the thing called while recording the macro, so that too would be helpful for you while you are wokring on gaining your VBA-spurs.