0

I have written a small code to export a query from Access to a sheet in Excel.

In the Excel sheet i have one problem; there is one row (underneath a header) that need to be converted into a

  • number value

Basically my VBA code should do what i now have to do by hand.

  • Number value: Select the cells containing the numbers. Press (popup) and select "convert to number"

I already have this

wks.range("Z2:Z" & rs.RecordCount + 1).numberformat = "General"

but it doesn't work

For reference below is my full code

SUB UnknownProcName

  Dim appXL As Object
  Dim wb As Object
  Dim wks As Object
  Dim xlf As String
  Dim rs As DAO.Recordset

  'Full path to Excel file'


  Set rs = CurrentDb.OpenRecordset("openPOLIST")
  Set wks = wb.Sheets("INP.OpenPOlist")

  wks.range("A1").select
  For Each fld In rs.Fields
      appXL.ActiveCell = fld.Name
      appXL.ActiveCell.Offset(0, 1).select
  Next
  wks.range("A2").CopyFromRecordset rs
  wks.range("A1" & rs.RecordCount + 1).Columns.AutoFit

  wks.range("Z2:Z" & rs.RecordCount + 1).numberformat = "General"

  rs.Close
  wb.Save
  wb.Close
  appXL.Quit
  Set wb = Nothing
  Set rs = Nothing

  DoCmd.SetWarnings True
  MsgBox ("Well... it does give a warning...")
End Sub

After i run the export the number it looks like this After export and has indeed the text value:

enter image description here

When I press this button and choose convert to number it has the desired value

enter image description here

and looks like this after manual change

https://i.stack.imgur.com/Gtk7J.png

braX
  • 11,506
  • 5
  • 20
  • 33
Drac
  • 27
  • 6
  • I believe you'll find the answer to converting text to numbers in this Stack Overflow discussion: https://stackoverflow.com/questions/36771458/vba-convert-text-to-number – Cindy Meister Apr 18 '18 at 15:24

1 Answers1

0

Thank you Cindy for helping me out and finding the answer. The code in the link you provided didnt work for me straight away but after playing around with it a bit i came up with the following

wks.range("Z2:Z" & rs.RecordCount + 1).NumberFormat = "0" wks.range("Z2:Z" & rs.RecordCount + 1).Value = wks.range("Z2:Z" & rs.RecordCount + 1).Value

This finally solved my problem. Thank you so much for the help !

Drac
  • 27
  • 6