Excel Formatting

[SorenIX]

Here is a small problem I have :

I imported an excel sheet with the header to Access. It created a table with the header names in the fields. But the header names were too long so they got truncated in the fields of the table.

Now I'm exporting the table with VBA code and it creates a perfect file except for the header.

I would like to take the header from the original excel file and overwrite the exported file header with it.

I would also like to remove the columns CL to EJ in the exported file.


Thanks for helping me

 

[daievans]

Well - have you thought about expanding the size of the Table fields to allow the headers to fit?

 

[SorenIX]

I honestly didn't.

I remember reading they weren't expandable, so I left it at that.

Please tell me how?

 

[daievans]

I honestly didn't.

I remember reading they weren't expandable, so I left it at that.

Please tell me how?

Design view for the table - select the field you want to expand and change the size - 255 characters is the maximum size - but that should give you enough room - unless that is your current size ...

 

[SorenIX]

You misunderstood! What I want to have more lenght in is not the field, it's the name of the field!

 

[Mihail]

Drag the border of the field (or double click it).
This works exactly like in Excel.

 

[SorenIX]

@Mihail

You misunderstood as well. I reached the max numbers of characters in a table column name. What do.

 

[Mihail]

I think is nothing to do.
The guys from Microsoft don't think that someone could use more than 255 characters for a field name.

 

[SOS]

Just create a table which can "translate" for you. Like this:

tblColumnExpNames
ExpNameID - Autonumber (PK)
TableFieldName - Text (255)
ExportName - Memo

Then you would need code to Substitute the field names once you've exported. This is untested "Air Code" so some changes may be needed to make it work. But hopefully you will get the idea.

Function ExportMyTable()
    Dim objXL As Object
    Dim xlWB As Object
    Dim strFile As String
    Dim rst As DAO.Recordset
    Dim i As Integer
 
    strFile = "C:\Temp\MyExport.xls"
 
    DoCmd.TransferSpreadsheet acExport, , "TableOrQueryNameHere", strFile, True
 
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open(strFile)

    Set rst = CurrentDb.OpenRecordset("Select * From tblColumnExpNames ORDER BY TableFieldName")
 
    For i = 1 To xlWB.ActiveSheet.UsedRange.Columns.Count
        Do Until rst.EOF
            If xlWB.ActiveSheet.Cells(1, i) = rst!TableFieldName Then
                xlWB.ActiveSheet.Cells(1, i).value = rst!ExportName
                Exit Do
            End If
        End If
    Loop

Next
 
xlWB.Close True
objXL.Quit

Set objXL = Nothing

End Function

 

[daievans]

Have you set up a File Import spec?
Do you tell it that your first row contains headers?
You can limit the columns you want to import in the Import Spec by checking the boxes for each field you don't wnat to import ... so CL -> EJ won't get into your Access table ...

 

[SOS]

I would also like to remove the columns CL to EJ in the exported file.

Just use a query to export instead of the table and don't have those columns in the query.

 

[SorenIX]

@SOS

I didn't quite get your "Translate" table...

I'm not too familiar with every terms yet... I'm learning as I program. Wouldn't it be easier to just make two Excel file; one containing the export and the other containing the header only, and then importing the export in the header starting on cell A2 and then saving that merged file into the desired name?

Perhaps it's easier to understand than code.

And good one for the query. Thanks for that.

 

[SOS]

@SOS

I didn't quite get your "Translate" table...

I'm not too familiar with every terms yet... I'm learning as I program. Wouldn't it be easier to just make two Excel file; one containing the export and the other containing the header only, and then importing the export in the header starting on cell A2 and then saving that merged file into the desired name?

Perhaps it's easier to understand than code.

And good one for the query. Thanks for that.

You could do it that way but I like to keep things self-contained so you aren't reliant upon external factors (like if someone moves the spreadsheet).

The Translate table is just a table that holds the full values of the field names and the truncated values and is able to be used to search for the truncated value and bring back the associated full text value.

 

[SorenIX]

Yeah I think I get it now. But I wouldn't know how to get the header in the Excel export to take on the full names in the translate table.

I'll take a look at your code once more. Maybe you can clarify certain things?

 

[SorenIX]

I'm currently doing the table, and I realise there are a lot of columns (89). And one problem I might have in the future is that, the files from which the users are updating the database may have one column removed or added, which I think wouldn't keep the integrity of my table. Is there a way to go around this?

 

[SOS]

You don't need to worry about anything that is deleted. It will just bypass that in the code. But if anything is added to the spreadsheet initially which then changes your input table, you would need to add that column information in the translation table.

 

[SorenIX]

I just tried your code and it freezes Access, as if the loop was infinite. I am missing something?

Function ExportMyTable()
    Dim objXL As Object
    Dim xlWB As Object
    Dim rst As DAO.Recordset
    Dim i As Integer
 
    DoCmd.TransferSpreadsheet acExport, , "ExcelTab", outputFileName, True
 
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open(CurrentProject.Path & "\" & outputFileName)
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM ExportColumnNamesTab ORDER BY [TableTruncatedNames];")
 
    For i = 1 To xlWB.ActiveSheet.UsedRange.Columns.Count
        Do Until rst.EOF
            If xlWB.ActiveSheet.Cells(1, i) = rst!TableTruncatedNames Then
                xlWB.ActiveSheet.Cells(1, i).Value = rst!FullNames
                Exit Do
            End If
        Loop
Next
 
xlWB.Close True
objXL.Quit
Set objXL = Nothing
End Function

 

[SOS]

Sorry, Yes I missed two parts. See the red:

Function ExportMyTable()
    Dim objXL As Object
    Dim xlWB As Object
    Dim strFile As String
    Dim rst As DAO.Recordset
    Dim i As Integer
 
    strFile = "C:\Temp\MyExport.xls"
 
    DoCmd.TransferSpreadsheet acExport, , "TableOrQueryNameHere", strFile, True
 
    Set objXL = CreateObject("Excel.Application")
 
    Set xlWB = objXL.Workbooks.Open(strFile)

    Set rst = CurrentDb.OpenRecordset("Select * From tblColumnExpNames ORDER BY TableFieldName")
 
    For i = 1 To xlWB.ActiveSheet.UsedRange.Columns.Count
        Do Until rst.EOF
            If xlWB.ActiveSheet.Cells(1, i) = rst!TableFieldName Then
                xlWB.ActiveSheet.Cells(1, i).value = rst!ExportName
                Exit Do
            End If
        End If
[B][COLOR=red]        rst.MoveNext
[/COLOR][/B]    Loop
[B][COLOR=red]    rst.MoveFirst[/COLOR][/B]
Next
 
xlWB.Close True
 
objXL.Quit
Set objXL = Nothing

End Function

 

[SorenIX]

It's still freezing for a long time. I don't know if the process is just very long........ I don't think so.

Notice that you have an End If without a If case in your version of the code. See the red part for the correction (unless yours is really what you want to do)

Function ExportMyTable()
    Dim objXL As Object
    Dim xlWB As Object
    Dim rst As DAO.Recordset
    Dim i As Integer
 
    DoCmd.TransferSpreadsheet acExport, , "ExcelTab", outputFileName, True
 
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open(CurrentProject.Path & "\" & outputFileName)
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM ExportColumnNamesTab ORDER BY [TableTruncatedNames];")
 
    For i = 1 To xlWB.ActiveSheet.UsedRange.Columns.Count
        Do Until rst.EOF
            If xlWB.ActiveSheet.Cells(1, i) = rst!TableTruncatedNames Then
                xlWB.ActiveSheet.Cells(1, i).Value = rst!FullNames
                Exit Do
[COLOR=red]            End If[/COLOR]
[COLOR=red]            rst.MoveNext[/COLOR]
[COLOR=red]        Loop[/COLOR]
[COLOR=red]        rst.MoveFirst[/COLOR]
[COLOR=red]    Next[/COLOR]
 
xlWB.Close True
objXL.Quit
Set objXL = Nothing
End Function

 

[SOS]

Yes, the extra End If is not supposed to be there.

I did forget to put this in which I usually do until it has been debugged and working correctly.

objXL.Visible = True

That is necessary to avoid having rogue Excel.exe instances out there. If you use Ctrl+Alt+Delete and go to Task Manager, you likely have multiple Excel.exe instances happening. You should kill them all and then try.

I would also set a breakpoint at the start of this and then use F8 to step through it so you can check each step of the way and the values to make sure it is working and where it is not working.