Import Web Query Looses Number Format
Aug 29, 2006
I am wanting to use Excel to perform a web query into an online database our company uses. One of the columns I am wanting to import contains numbers that are 20 digits long & they begin with 4 zero's. The problem is that when Excel imports the query, if I fprmat the cells & preserve the formatting, it cuts off the first 4 zero's & replaces the last number with a zero. Basically, it looks as if it is only importing 15 numbers. Is there a way around this ; is there a way to force Excel to import all 20 numbers? If I don't format the cells, it imports in the following format - 4.20017E+15
View 4 Replies
ADVERTISEMENT
Oct 16, 2006
When i select 10% in the combo box, it changes to decimal which is 0.1
How do i change it to a whole number? eg. 0.9 to 90%?
View 6 Replies
View Related
May 19, 2009
I'm Importing Columns from one sheet to another in the same workbook. imports good But:
1- Some columns with first records/rows blank do not import at all.
2- I have a column with some letter/numbers combinations and single numbers at times. Those single numbers are not imported. And half my data in that column are single numbers.
View 9 Replies
View Related
Nov 15, 2006
I have the following code for importing a query from Access to Excel.
Sub LoadAdditionalData()
Application.Run Range("AUTOSAVE.XLA!mcs02 .OnTime")
With ActiveSheet.QueryTables.Add(Connection:= Array(Array( _
"ODBC;DBQ=S:NBHD_RENEWALDatabaseNR DB_current.mdb;DefaultDir=S:NBHD_RENEWALDatabase;Driver={Driver do Microsoft Access (*.mdb)};" _
), Array( _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _ ...............
I currently have filters set to 990 (finance code) and 3 (quarter) but I would like to be able to enter these parameters myself everytime the macro is run.
View 4 Replies
View Related
Aug 17, 2013
I have a website (National Lacrosse League - 2013 Regular Season - Standings), that I want to pull information off and automatically into Excel.
I have been using web queries on other websites, and they have worked beautifully, however the tables that I want aren't recognised by the web query wizard (no yellow/black arrows). I can import the whole page through a query, however that's not really practical.
Is there a way I can import just specific tables on this site?? I'd like both the 'east' and 'west' tables.
View 8 Replies
View Related
Apr 6, 2008
I have been using the attached to pull multiple csv files into seperate sheets. This takes all files from a user specified directory and dumps them into a workbook. I only need three specific files. How do I request specific files instead of a wild card.
View 3 Replies
View Related
May 9, 2009
I am auto-importing statistics from the web via web query. This information imported has a number in the form of a decimal stated as either x.1, or x.2. x.1 represents x-1/3 (one-third) and x.2 represents x-2/3 (two-thirds). I need to use these numbers in a calculation after importing them, but using x.1 or x.2 obviously does not give me accurate results as the numbers should actually be x.3333333 and x.6666666 respectively. What is the easiest way to convert the imported numbers to their actual decimals?...
View 2 Replies
View Related
May 22, 2014
When I enter a number, say 2456 in this textbox why does it display "$2.45" instead of "$2,456". I've tried fiddling with the ###'s and .'s but it just gets worse.
[Code] ....
View 4 Replies
View Related
Aug 28, 2009
I use web queries to download stock options from MSN Money.
If I manually refresh the queries (right click -refresh) the downloaded
prices are in number format.
If I use:
View 3 Replies
View Related
Oct 3, 2006
I tried to upload my spreadsheet but the zipped excel spreadsheet was 78KB over the forum max. Its hard for me to explan this without you looking at the spreadsheet. Basicly I'm exporting data from Access to Excel into a spreadsheet. When the data exports into excel I want the data to be formated in such a way and this is where some code will need to be written. I've made up 2 spreadsheets within excel. One sheet is how the data comes into excel from access and the other sheet is how I'd like the data to look without me having to doctor the sheet every time I export.
View 9 Replies
View Related
May 31, 2012
The following code stops at the red line with "Method 'SaveAs' of object '_Workbook' failed.
FF="xlCSV". It works fine if I replace
Code:
FileFormat:=FF
with
Code:
FileFormat:=xlCSV
Code:
Sub SaveIt(FileNm, FF)
Dim FSO As Object, a
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(FileNm) Then FSO.DeleteFile FileNm
If FSO.FileExists(FileNm) Then
[code]....
View 2 Replies
View Related
Jun 14, 2006
if it is possible to concatenate multiple cells that have different font formats? For example; cell A1 is font formatted SYMBOL and contains the letter "D", cell B1 is font formatted ARIEL and contains the word "delta". What I am trying to do is concatenate the two cells while maintaining their font formats.
View 3 Replies
View Related
Apr 14, 2009
I was wondering if its possible to build a module that would do the following: a) import a txt file to excel formatting it as text.
b) then in column D would remove the preceding space.
c) then convert data in D according to a separately kept Conversion table (or conversion table could be integrated into the code) and print conversion results into column J.
d) the last step is to print/copy columns A and J so that it looks like the final table in Sheet2.
Here are files attached.
sample data.txt
sample data.xls
conversion table.xls
To summarize I need to go from a txt file like the one attached and arrive at the table in Sheet2 of xls file attached.
View 8 Replies
View Related
Aug 7, 2012
I have a piece of code that imports data from a database on the web. It works but the import uses the wrong encoding. Excel also messes up a column with id-numbers, starting with three zeros that are removed by Excel.
How can a change my code so that it imports the data as utf8 and the columns as text?
Code:
Sub csvAll_diva_update()
Dim url As String
url = "URL;http://kth.diva-portal.org/dice/csvAll?query=-publicationTypeCode:studentThesis&start=0&rows=20&sort=author_sort%20asc"
Sheets("DiVA_All").Activate
ActiveSheet.Cells.Clear
[Code] .......
View 1 Replies
View Related
Jul 23, 2008
on data import from CSV file. The file is formated in a sligthly strange way:
"London","182.56","12/12/2007"
"Paris","133.41","27/12/2007"
I get into problem when importing using default CSV settings as numbers and date are understood like text. I need to replace "." with "," to get to number format. Used following Range("J5:K9999").Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
However I get values multiplied by 100 for some reason.
Converting date format gets eeven more complicated.
So I decided I can not use standard CSV import and need to define separate data source for each raw file This is not what I wanted, since I do import many files (10 at this point in time, but it will get growing). However, I would be ready to do that if there is no other option. So I go to "Create New Data Source" and under "Connect" I get to another input box called "ODBC Text Setup" where I select "Options" to define customer format. However, after selecting source file, Excel is not able to interprate fields. Tried clicking "Guess" button, no luck there. Thought it is because fo those "" before and after field value. So, just for a sake of experiment I opened file in text editor and removed those ". Still, can not read the file. The error message says "Text file specification field separator matches decimal separator or text delimiter. Guess failed"
I am out of ideas how to get this file properly imported. Hope there is a way to do that either by defining field formats while importing (perhaps that can be done via SQL, but I am not confident with it) or writing some code to change format after importing from text to Number and Date...
View 9 Replies
View Related
Jan 12, 2010
I have a large text file that is generated daily and want to import into MS Access as the end result.
First I need to reformat into the row format in excel rather than the format it is in. The issue is not all the segments are the same number of lines or they may have mutli message lines. The names with colon : after them I want to be field names in the table which I wish the text file to import into. Please, I am looking for assistance with this.
I have attached sample data of the text dump, and the name of the file is as you see but different date on the end. At the very end of the text file you will see the desired output which I will then import into Access
Also every new message begins with the dotted line and the date and time at the end.
View 14 Replies
View Related
Jul 7, 2014
So i'm receiving a web query on a sheet that is taken from a table off the web. When this data gets extracted into the sheet, it populates as "$xxxM" in order to show that it is a certain million dollars. The problem is, I need to total all these numbers to display at the bottom of my report. So essentially, I am trying to configure web query to display as "number" as opposed to "$numberM" so it is easier to total.
View 1 Replies
View Related
Mar 31, 2014
One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.
View 5 Replies
View Related
Aug 1, 2013
I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
View 3 Replies
View Related
Oct 5, 2009
They have a list of data numbers which are yy/#####/@@@@@ (2digit year/casenumber/alphanumeric code). Where some people have typed in the code wrong (6 or more digits in case number) we want to know is there a way of doing this.
Ideally I want something that says "If 9th character is a slash then yes, if not no" or "if 9th character is a number then yes, if not, no".
Example of data is (where we want third option highlighted as erroneous (6 not 5 numbers in middle)
09/56487/KFJT
09/42105/PLOUR3
09/002145/PLIFD
09/02145/ASDF
View 4 Replies
View Related
Mar 1, 2007
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
View 14 Replies
View Related
Jan 29, 2009
I am importing data from an access database that is continually refreshed. Some of the data that is returned to my Excel sheet is in binary, where a "0" represents an unchecked box on the database program and a "1" represents the same box, but with a check mark. I want to change the data so that when it is updated on my Excel sheet it is converted to text as either "Yes" or "No" instead of "1" or "0", respectively. I'm sure this is pretty elementary stuff but I have tried VBE and cell formulas and can't come up with an answer.
View 3 Replies
View Related
Nov 13, 2012
I've got 80 CSV files that I need to import into an Excel sheet. The filenames are sequential, eg 1APN .... 80APN). I need each chunk of data added after the previous. How can I do this using VB?
View 2 Replies
View Related
Jun 29, 2009
I have a formula which I have copied from one of your good selves and tried to adapt it. The formula is below. I a am getting #NA as my answer.
=SUMPRODUCT(ISNUMBER(FIND({"h","H"},B11:G11))*{0.5,1})
I have a person name in Colomn A and then 1 week B-G on a row and want to count number of days holiday.
View 4 Replies
View Related
Nov 28, 2013
How do I convert 11.20.00 in A1 to 11:20:00 in B1?
I've tried =TEXT(A1,"hh:mm:ss") to no avail.
View 3 Replies
View Related
Oct 11, 2012
I want to convert number format to text format, any formula ?
View 6 Replies
View Related
Sep 14, 2007
I'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:
474556788 to 047-455678-8
Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)
View 10 Replies
View Related
Jan 6, 2014
I am wanting to create a validation rule so when two cells are added the together the answer must be less than or equal to 14:00 - otherwise a message box will appear
So for example if H7+I7 gives an answer of 13:00 in J7, that's OK, but if the answer is 15:00, the error box will appear. I know that data validation doesnt work on a cell that already has a formula so I'm hoping to use K7 for the Validation and then hide the column
View 4 Replies
View Related
Oct 23, 2011
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
[URL]
View 4 Replies
View Related
Jan 23, 2009
I have recorded some script in excel to import a *.dat file, delete the first 19 rows of data, then export the data as a *.csv file.
My problem is I have circa 30 *.dat files to import, all of which have large file names and I am fed up with copying and pasting them in.
So my question is, in the below code, can I somehow use a reference at the start such as:
abc1.dat = "1"
abc2.dat = "2"
Then where the files were used, reference the "1".dat and its exported version "1".csv for the abc1.dat case.
View 10 Replies
View Related
