vba - ADO is truncating Excel data -


i have function gets adodb recordset contents of worksheet using ado, follows:

function worksheetrecordset(workbookpath string, sheetname string) adodb.recordset  dim objconnection new adodb.connection dim objrecordset new adodb.recordset  on error goto errhandler  const adopenstatic = 3 const adlockoptimistic = 3 const adcmdtext = &h1  objconnection.commandtimeout = 99999999  objconnection.open "provider=microsoft.ace.oledb.12.0;" & _     "data source=" & workbookpath & ";" & _         "extended properties=""excel 12.0 xml;hdr=yes;imex=1"";"  objrecordset.open "select * [" & sheetname & "$]", _     objconnection, adopenstatic, adlockoptimistic, adcmdtext  if objrecordset.eof     set worksheetrecordset = nothing     exit function end if  objrecordset.movelast objrecordset.movefirst  set worksheetrecordset = objrecordset exit function  errhandler: set worksheetrecordset = nothing  end function

i'm having problem importing number data numbers formatted 1 decimal place have 2 decimal places. happens if datatype mixed in column. example, these values:

0.03 0.05 0.08 0.13

when set them 1 decimal place in table:

+-------+-----------+ | value | | +-------+-----------+ | 0.0   |         | | 0.1   |         | | 0.1   | sda       | | 0.1   | sdf       | +-------+-----------+

then recordset gets correct 2 decimal place values. when put them in table:

+---------+-----------+ |  value  | | +---------+-----------+ | asdfasd | asdfas    | | 0.0     |         | | 0.1     |         | | 0.1     | sda       | | 0.1     | sdf       | +---------+-----------+

then recordset gets 1 decimal place values, e.g. picks "0.0" instead of "0.03". think because string in first row causing ado treat values in columns strings displayed.

is there way can still pick text string, correct number of decimal places in number values?

edit: noticed odd. when run while workbook open, recordset gets correct decimal places. if run while workbook closed, gets displayed decimals.

try below objrecordset features , query (tested in ms query excel):

with objrecordset     .cursorlocation = aduseclient     .locktype = adlockoptimistic     .cursortype = adopenstatic     .activeconnection = objconnection     .open "select format(`" & sheetname & "$`.value,'0.00') [value], [" & sheetname & "$]" end

so, here jet sql format function forcing ado's sql parser output string formatted 0.00

also, have set cursortlocation property aduseclient won't need use movelast , movefirst

let know how on

philip


Comments

Post a Comment

Popular posts from this blog

user interface - Python attempting to create a simple gui, getting "AttributeError: 'MainMenu' object has no attribute 'intro_screen'" -

complete newbie here python started learning language few days ago (with no programming experience beforehand). i'm bashing skull against desk here, trying create 1 menu button lead menu, supposed replace/hide/destroy previous menu (either works, long process can reversed). what i've come far: import wx class mainframe(wx.frame): def __init__(self): wx.frame.__init__(self, none) self.centre() self.main_menu = mainmenu(self) self.intro_screen = introscreen(self) self.intro_screen.hide() class mainmenu(wx.frame): def __init__(self, parent): wx.frame.__init__(self, parent=parent) self.main_menu = mainmenu panel = wx.panel(self) sizer = wx.boxsizer(wx.vertical) nextscreen = wx.button(panel, label='next screen', size=(150,30)) nextscreen.bind(wx.evt_button, self.nextscreen) sizer.add(nextscreen, 0, wx.center|wx.all, 5) self.show() self.c...
Read more

jquery - Common JavaScript snippet to share files on Google Drive, Dropbox, Box.net or SkyDrive -

this first assignment of file sharing. need share files our website above mentioned locations. currently, have been exploring each service’s api file sharing. question 1 : there javascript code snippet available (like addthis.com ) share files? question 2 : should use server side coding file sharing or javascript advisable? thanks viewers. found useful plug-in named "ink file picker". saves given http file resource directly users online storage engine. url: https://developers.inkfilepicker.com/docs/web/ live example: http://ctrlq.org/save/
Read more

Android Gson.fromJson error -

i've completed tutorial how connect json url , parse content using gson library. code works fine url specified in tutorial, tryed modify work this url . tryed display 1 field second ulr, base field, can't work. here code, please help: public class jsonparsingactivity extends activity { string url = "http://api.openweathermap.org/data/2.5/weather?q=london,uk"; @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.main); inputstream source = retrievestream(url); gson gson = new gson();// main gson class reader reader = new inputstreamreader(source); searchresponse response = gson.fromjson(reader, searchresponse.class); //gson.fromjson(string, class) toast.maketext(this, response.base, toast.length_short).show(); } private inputstream retrievestream(string url) { defaulthttpclient client = new defaulthttpclient(); httpget getrequest = new httpget(url);...
Read more