Export Excel then feed data to cells

[gakiss2]

Overall function is OK. the excel file is created and the data placed into the cells as desired (mostly) then the modified file is saved as a new file.

The issue is one of the fields is not outputting correctly. Attached I have screen shot of the database showing the field as "Machine No. N3-790Defect: The air bag has a broken seal." When the record comes up in a form it looks the same, I attached a screenshot called Form. When I out put it to Excel it gets cut off and only says "Machine No. N3-790". I attached the screenshot of the excel file. Another screen shot is the code and also here:

Public Function DoExcel()

Dim oXL As Object
Dim NewFile As String
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set wb = oXL.Workbooks.Open("C:\Users\gkissick\Desktop\PRR.xlsx", True, False)
wb.Sheets(1).Range("F2").Value = "YES"
wb.Sheets(1).Range("c4").Value = Forms!frmMRRLog!SupplierName
wb.Sheets(1).Range("h4").Value = Date
wb.Sheets(1).Range("c6").Value = Forms!frmMRRLog!item
wb.Sheets(1).Range("b17").Value = Forms!frmMRRLog!ProblemDescription
wb.Sheets(1).Range("e6").Value = Forms!frmMRRLog!mrr_num
NewFile = "C:\Users\gkissick\Desktop\PRR " & Forms!frmMRRLog!mrr_num
wb.SaveAs FileName:=NewFile, FileFormat:=56
End Function

I can also tell you that the data is imported from an Excel File and in that Excel file the entire text shows in the cell BUT there is some sort of internal line break because it shows up in the cell as:

Machine No. N3-790
Defect: The air bag has a broken seal.

Which is, of course, right where the record gets cut when put into the Excel.

I just don't know what to do to get the entire field to get put into the Excel cell. Some sort of hidden text codes in the text? Some way to strip them out?? More background is that the data originally comes from a commercial database type program 'Siteline' or 'Infor' and I'm sure other aliases as well. I don't have any influence over that, just thought it might help understand why the text is getting cut.

Help is greatly appreciated.

 

[gakiss2]

Forgot attachments

The attachments are in this message

 

[gakiss2]

For what it is worth.. If I just copy from the original excel and paste into blank excel (I guess the same as what I am doing in the vba), the whole field comes through.

Pull it straight from the orig excel to the newly created excel??? … Seems very complicated to me.

A button runs code to import from this excel into a table in the access that I am working on... which ultimately causes a different Excel to be created. I suppose maybe if I hadn't already spent so much time on the database I could possible write it all as Excel using vba in Excel??

That seems somehow sacrilegious and just … wrong.

 

[Mark_]

I can also tell you that the data is imported from an Excel File and in that Excel file the entire text shows in the cell BUT there is some sort of internal line break because it shows up in the cell as:

Machine No. N3-790
Defect: The air bag has a broken seal.

You can't see the carriage return/line feed that are after "N3-790" and before "Defect". When you format your output, you would want [YourFieldWith"N3-790"] & "chr(13) & chr(10) & [YourFieldWith"Defect"].

 

[gakiss2]

Thank You for your reply.

After further exploration I found that I can see the rest of the data if I widen the row on the Exel - so yes it was carriage return. Now I need to figure out how to format the Excel so the row will expand as needed. Clearly out of range for this forum.

 

[Gasman]

Thank You for your reply.

After further exploration I found that I can see the rest of the data if I widen the row on the Exel - so yes it was carriage return. Now I need to figure out how to format the Excel so the row will expand as needed. Clearly out of range for this forum.

Record a macro when you set the width/height and use that code with yours.?

 

[gakiss2]

OK, Thank You

 

[Mark_]

Have you tried setting the RowHeight in your code?

 

[gakiss2]

I tried setting row height to automatic in the Excel File but that doen't cause it to expand. Not sure if its because of the carriage return or just not working, I need to test. Another thought is to strip out the chr(10)'s with excel 'substitute'.

 

[Mark_]

I was thinking use RowHeight in your code when you create the cell.

The other piece to set is WrapText so the text displays on more than one line.

 

[Uncle Gizmo]

There's an example of some code for exporting data to MS Access on this on the BTAB Developments website, which I believe was Bob Larson's. Bob was one of the top posters here until he retired a year or two back. I seem to recall it adjusts the column widths of the Excel sheet.

The code is here:-
Code Snippets → Export A Table Or Query To Excel

And I have Incorporated the code in to a product which you can see on my website here:-
Show/Hide & Export to Excel

The product is available for free if you subscribe to my newsletter.
Subscribe to Nifty News

 

[Uncle Gizmo]

I seem to recall it adjusts the column widths of the Excel sheet.

I reckon HERE:-

  ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

 

[gakiss2]

Thanks to ALL. I ended up putting the data in an out of the way cell then in the I Put a formula in the real destination which stripped out the Char(10)'s . Now at least the text flows in one line. When I get a record that overflows the line I'll see what happens. I played with the excel and just couldn't get the cell format row autoheight function to work. I'd go on but this is an Excel issue, not an Access issues so I assume this is not interesting here.