Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Vba, Activexcontrols And Data Types

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am collecting data through an ActiveXcontrol. The command I am using is 'ai = DataqSdk1.GetData'. This data is coming in as type 'variant'. I want to be able to manipulate this data (from counts to a pressure value), but I first need to convert its data type to double. I tried the following with no luck ' ai_a = CDbl(ai)'. Please help!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
- This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the highes
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
- This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
Display Filter Arrows in a Table or Data Set in Excel - AutoFilter
- This free Excel macro allows you to apply filter settings to a data set, list, or range of data in Excel. This will not

Similar Topics







I've tried finding a definitive answer to this but can't.

If I am reading from a worksheet and placing the data in a multidimensional array, are all the values held in the array the SAME Data Type or are they retained as the Data Type they were in the worksheet?

For example, if I read A5 (Date), B5 (Text) and C5 (Double) into an array I declared as

Dim arrayMyNiftyData As Variant

... then will those three values retain their (Date, Text, Double) data types or will they be a new, different type of Data Type common for all three?

Is that clear as mud?


I have a form that takes input from the user. Its a textbox so the value is a string. But the input from the user is actually a real number. Have not decided on the actual data type as yet but i was thinking either single or double data type.

the issue is how to convert this text box string to one of these types.............any ideas?

i know to convert string to
integer i use "Val"
Long i use "Clng"

not sure about single or double data types.


Hey

I need some serious help with the countif formula...Plz help

Heres a sample data. The table one is a raw data sheet with continuous data with like 1000 types as shown in column A and there are many process to be done to convert it....Its on luck basis,we can achieve success may be in first prcocess and at the most we go upto 10 process per type and as u can see dates represent date on which another process is done per type if there is no success. Now, A,B and C are various processes by which it can be transformed....this process is choosen at random, like its our wish to use whatever process type.

Date

Process 1

Date

Process 2

Date

Process 3

Date

Process 4

Date

Process 5

Type 1

1-Aug-10

A

4-Aug-10

A

5-Aug-10

B

Type 2

2-Aug-10

A

2-Aug-10

B

4-Aug-10

B

5-Aug-10

C

Type 3

2-Aug-10

B

2-Aug-10

B

10-Aug-10

C

11-Aug-10

A

11-Aug-10

C

Type 4

3-Aug-10

A

4-Aug-10

B

5-Aug-10

A

Type 5

4-Aug-10

B

5-Aug-10

B

1-Sep-10

C



Now coming to what kind of analysis i want out of it is : -

I want to calculate the particular number of process (A, B or C) done in a particular month so that expenses can be calculates,,,,

Here is the analysis sheet

Aug-10 Sep-10 Oct-10 Nov-10 Dec-10 Jan-11 A B C

I hope i have made myself very clear.
Thanx


Hi


For the times when variant contains an integer, how do i convert it to
integer type?

is it some thing like?
......................
dim varies As Variant
dim x As integer

if i know varies contains an integer value
x = integer(varies)

Also can a function which is said to return a variant, return an integer
type and the cell calling the function display an integer type or will just
appear as a variant type. I know a variant has no specific type but does
this mean it can accept various types and which are then tread as the
original type or is variant a type in itself, rather than a variable which
can be a variety of types?

Many thanks
James





I have the following simple, custom data type:
Code:

Type cmd
start as string*4
end as string*4
end type

dim command() as cmd


And now I want to be able to simply load it from a sheet, e.g.,
Code:

set rng = sheets("blah-blah").range ("a1:b3")
command = rng.value


But it does not work. If I throw away my custom data type and just DIM command as variant, it works fine.

So, how can I load the data SIMPLY, but be able to refer to it using the dot syntax of custom data types?

**** Penny
PS. did I do the code tags correctly this time?


I have the following simple, custom data type:
Code:

Type cmd
start as string*4
end as string*4
end type

dim command() as cmd


And now I want to be able to simply load it from a sheet, e.g.,
Code:

set rng = sheets("blah-blah").range ("a1:b3")
command = rng.value


But it does not work. If I throw away my custom data type and just DIM command as variant, it works fine.

So, how can I load the data SIMPLY, but be able to refer to it using the dot syntax of custom data types?

**** Penny
PS. did I do the code tags correctly this time?


Hi,

In one of the column of excel, the data contains data with numeric and some data with characters. How can I convert the data type of the column to Text.

I tried to use the DigDB AddIn ( Convert Excel Data Types - from Text to Number/Date (General), vice versa - Excel Tips & Add-ins ) and now the AddIn in my PC has expired.

I need to perform the similar task (to convert the data type to Text).

Thanks.


I get an error when using type:

Type complex
re as double
im as double
end type

I put the type code in a module, and access it from thestartup module in vba with :

dim akd(150) as complex

.... that seems to go ok, but when I use the array the error comes, for instance:

akd(1)=sa

then the error comes:

Compiler Error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions

anyone?


I am copying data between two workbooks through a macro.I am matching value in first column of first workshhet and finding that value in the workbook from where data needs to be copied. It works fine untill value i am matching, contains 1 decimal point. For values it contain 2 decimal points, it doesnt copy anything. Type of the variable for values containing 1 decimal point is 'Variant/double' whereas for 2 decimal points, it comes up with 'variant/string'. i cant see where to change this type so that type is same for whole column.

Can anybody help or any ideas what i should be doing?

Thanks!!


Hi,
I have a string of time stamped dates. Some are stored as TYPE=1 and some as TYPE=2. I need to convert these to a format of 31-Mar-11. I have successfully done it with all of the TYPE 2 values, but for some reason the TYPE 1 values are reverting to a DDMMYY version and hence, whenever the day is below 12, Mar 12 2011 will revert to 3-Dec-11. It's driving me insane.

On top of it, all of the raw dates that come in are formatted as MMDDYY, but some (and this isn't consistent), are coming in as "03312011 <timestamp>" and some as "3312011 <timestamp>". I've figured out this part but thinking it might play into my next set of formulas to convert the various types. please help! Below is an example of the formulas I'm using:

Raw data eg:
40547.5375
03/31/2011 22:51
3/31/2011 22:33

then i manipulate it with this formula:
=IF(MID(A12,2,1)="/",CONCATENATE("0",A12),A12)

OUTPUT:
4-Jan-11
03/31/2011 22:51
03/31/2011 22:33


and then i maniupate that out with this formula:
=IF(TYPE(A12)=2,DATEVALUE(MID(B12,4,2) &"/"& LEFT(B12,2)&"/"&MID(B12,7,4)),B12)

OUTPUT:
4-Jan-11
31-Mar-11
31-Mar-11
** as you can see, the top date should be April 1, 2011, and not Jan 4, 2011.

This is driving me crazy and I'm sure there has to be a better way than separating out the time stamp with text to columns (manual), then (manual again) using text to columns to convert to MMDDYY.

Please help!

Sincerely,
frustrated in syntax


Hello,
First of all - I'm aware that maybe the qiestion should be in different part of the forum, but because it is also connected to excel and VBA programming, I post it here.
I'm using ADO to retrieve data from csv files, manipulate the return values and display in excel (forms and worksheets). The problem I have is with the data type of the fields in the recordest. Data in some of the columns are mixed - integers (0) and double. When most of the data are not integers, everything works fine - data provider guess data type correctly. However in the case when most of the data are 0, it guess that field type is integer and cut the decimal part of all data. Searching the Internet, I know that in general it is possible to specify the data type of a recodest field, however I'm not able to achieve this in Excel VBA enviroment. After reading some Microsoft KB articles, started to doubt is it even possible.
So my question is, is it possible and eventually can you give me a working code example in VBA?


Hi Guys, can somebody pleasehelp me?!?! i am importing data from an external package (I SCALA if anybody recognises it) and am having trouble with the data it brings across. Basically, the data coming across is in TYPE 2 format, and am reconciling against data that is in TYPE1 format. Does anybody know how i can change a whole range of cells into a different type?!?! as my look ups are not working saldy!

Any help will be greatly received

Many Thanks

Dan Passfield


This is driving me crazy.

I am using named ranges in Excel to query from Visual Basic. Here is the SELECT staement that is giving me trouble:

.CommandText = "SELECT * FROM MODELS WHERE MODEL = '" & cboModel.Text & "'"

I get 'Data type mismatch in criteria expression.'

The MODELS in Excel are all numeric. When I check their types I get numeric. I have tried changing the cell format to text but TYPE still says they are numeric. I can add a ' to the front of the number and the type changes to text. But I still get the data type mismatch error. I assume the ' is the problem there.

CboModel.Text is a string. I have tried converting to integer, single, double, etc. with no solution.

So here's the question. If you type an integer into a cell in Excel what data type is it? How is it interpreted in a SELECT statement?


Maybe this is an Access question, but here goes:

I'm uploading data from Excel to Access via macro. One of the fields is a number field and the data type in Access is Double. When the macro tries to upload a zero value it fails, but, I can manually enter a zero value into the table.

This one has me stumped. I've tried playing with formatting and data types, but no luck.

Any ideas are appreciated.


hi,

I have 123 columns in a table and i am to insert all columns value in to the sql table from the excel sheet.

Now I am able to insert till 104 columns.
If i am go to 105 column. i got error = "Run-time Error '9': Subscript out of range"

Now What should do?? I need to insert all 123 columns in to Sql table

Please help me.. It's really very urgent.....

I am using below code :-

Code:

Sub UploadFromExcelToSQL()
 
Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim sSQL As String
Dim lRow As Long, lCol As Long
Dim str As String
Dim r, c As Integer

sConnString = "Provider=sqloledb;Server=WKS10953;Database=QFA;User Id=sa;Password=kk"
 
Set adoCN = CreateObject("ADODB.Connection")
 
adoCN.Open sConnString
 
 Sheets("DATA").Select
 r = 2
 str = Sheets("DATA").Cells(r, 1).Value
 
While str  ""
                
    sSQL = "INSERT INTO TBL_MASTER (RCLNT, RYEAR, OBJNR00, OBJNR01, OBJNR02,OBJNR03, OBJNR04, OBJNR05, OBJNR06, OBJNR07, OBJNR08,DRCRK, RPMAX, ACTIV, RMVCT, RTCUR, RUNIT, AWTYP, RLDNR, RRCTY, RVERS, LOGSYS, RACCT, COST_ELEM, RBUKRS, RCNTR, PRCTR, RFAREA, RBUSA, KOKRS, SEGMENT, SCNTR, PPRCTR, SFAREA, SBUSA, RASSC, PSEGMENT, TSLVT, TSL01, TSL02, TSL03, TSL04, TSL05, TSL06, TSL07, TSL08, TSL09, TSL10, TSL11, TSL12, TSL13, TSL14, TSL15, TSL16, HSLVT, HSL01, HSL02, HSL03, HSL04, HSL05, HSL06, HSL07, HSL08, HSL09, HSL10, HSL11, HSL12, HSL13, HSL14, HSL15, HSL16, KSLVT, KSL01, KSL02, KSL03, KSL04, KSL05, KSL06, KSL07, KSL08, KSL09, KSL10, KSL11, KSL12, KSL13, KSL14, KSL15, KSL16, OSLVT, OSL01, OSL02, OSL03, OSL04, OSL05, OSL06, OSL07, OSL08, OSL09, OSL10, OSL11, OSL12, OSL13, OSL14, OSL15, OSL16)" _
    & " VALUES (" & "'" & CDbl(Sheets("DATA").Cells(r, 1)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 2)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 3)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 4)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 5)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 6)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 7)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 8)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 9)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 10)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 11)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 12)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 13)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 14)) & "'," & "'" _
& CStr(Sheets("DATA").Cells(r, 15)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 16)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 17)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 18)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 19)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 20)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 21)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 22)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 23)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 24)) & "'," & "'" _
& CStr(Sheets("DATA").Cells(r, 25)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 26)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 27)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 28)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 29)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 30)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 31)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 32)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 33)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 34)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 35)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 36)) & "'," & "'" _
& CStr(Sheets("DATA").Cells(r, 37)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 38)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 39)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 40)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 41)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 42)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 43)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 44)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 45)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 46)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 47)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 48)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 49)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 50)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 51)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 52)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 53)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 54)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 55)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 56)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 57)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 58)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 59)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 60)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 61)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 62)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 63)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 64)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 65)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 66)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 67)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 68)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 69)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 70)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 71)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 72)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 73)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 74)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 75)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 76)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 77)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 78)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 79)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 80)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 81)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 82)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 83)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 84)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 85)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 86)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 87)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 88)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 89)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 90)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 91)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 92)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 93)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 94)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 95)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 96)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 97)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 98)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 99)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 100)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 101)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 102)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 103)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 104)) & "'," & "'" & CDbl(Sheets("DATA ").Cells(r, 105)) & "')"
                
 adoCN.Execute sSQL
    r = r + 1
    str = Sheets("DATA").Cells(r, 1).Value
 
 Wend
                 
adoCN.Close
 
Set adoCN = Nothing
 
 
End Sub





i have an array of strings which i have declared as a variant mainly because i get an error when i try to declare it otherwise. i would like to eliminate all variant data types, and declare this array properly. any thoughts on why variant is the only allowable type? thanks.


Hi, I have a macro that counts the number of cells that have conditional formatting on them. The macro was created in Excel 2003 but when I try to run the macro in Excel 2007 it does not work. Any help greatly appreciated. Here is the code:

Function ActiveCondition(Rng As Range) As Integer
Dim Ndx As Long
Dim FC As FormatCondition
Dim Temp As Variant
Dim Temp2 As Variant
If Rng.FormatConditions.Count = 0 Then
ActiveCondition = 0
Else
For Ndx = 1 To Rng.FormatConditions.Count
Set FC = Rng.FormatConditions(Ndx)
Select Case FC.Type
Case xlCellValue
Select Case FC.Operator
Case xlBetween
Temp = GetStrippedValue(FC.Formula1)
Temp2 = GetStrippedValue(FC.Formula2)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) >= CDbl(FC.Formula1) And _
CDbl(Rng.Value) = Temp And _
Rng.Value CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Rng.Value > Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If
Case xlEqual
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Temp = Rng.Value Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case xlGreaterEqual
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) >= CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Rng.Value >= Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case xlLess
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) < CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Rng.Value < Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If
Case xlLessEqual
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) 0 Then
If Condition = -1 Or Condition = FCNum Then
Count = Count + 1
End If
End If
Next Rng
CountOfCF = Count
End Function
'''''''''''''''''''''''''''''''''''''''
Function SumByCFColorIndex(Rng As Range, CI As Integer) As Double
Dim R As Range
Dim Total As Double
For Each R In Rng.Cells
If ColorIndexOfCF(R, False) = CI Then
Total = Total + R.Value
End If
Next R
SumByCFColorIndex = Total
End Function


Ok i know this will be impossible, but i'll try it anyway......
Is there any way to set up excel so when......
say you have three columns you type data into the first two and if you type data into the third one it saves the data you typed in and then drops you down to a seperate row, and automatically types something in and lets you finish. EXAMPLE!!!

say i'm setting up a report type spreadsheet in the third column is a yes/no section if no you leave it blank if yes you type yes and hit enter, then it would drop you down oh say two rows, type in "comments:" for you and then let you type in the comments. seems a little impossible i know, but i've seen wierder things happen.


hi,

I have 123 columns in a table and i am to insert all columns value in to the sql table from the excel sheet.

Now I am able to insert till 104 columns.
If i am go to 105 column. i got error = "Run-time Error '9': Subscript out of range"

Now What should do?? I need to insert all 123 columns in to Sql table

Please help me.. It's really very urgent.....

I am using below code :-
Code:

Option Explicit

Sub UploadFromExcelToSQL()
 
Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim sSQL As String
Dim lRow As Long, lCol As Long
Dim str As String
Dim r, c As Integer

sConnString = "Provider=sqloledb;Server=WKS10953;Database=QFA;User Id=sa;Password=kk"
 
Set adoCN = CreateObject("ADODB.Connection")
 
adoCN.Open sConnString
 
 Sheets("DATA").Select
 r = 2
 str = Sheets("DATA").Cells(r, 1).Value
 
While str  ""
                
    sSQL = "INSERT INTO TBL_MASTER (RCLNT, RYEAR, OBJNR00, OBJNR01, OBJNR02,OBJNR03, OBJNR04, OBJNR05, OBJNR06, OBJNR07, OBJNR08,DRCRK, RPMAX, ACTIV, RMVCT, RTCUR, RUNIT, AWTYP, RLDNR, RRCTY, RVERS, LOGSYS, RACCT, COST_ELEM, RBUKRS, RCNTR, PRCTR, RFAREA, RBUSA, KOKRS, SEGMENT, SCNTR, PPRCTR, SFAREA, SBUSA, RASSC, PSEGMENT, TSLVT, TSL01, TSL02, TSL03, TSL04, TSL05, TSL06, TSL07, TSL08, TSL09, TSL10, TSL11, TSL12, TSL13, TSL14, TSL15, TSL16, HSLVT, HSL01, HSL02, HSL03, HSL04, HSL05, HSL06, HSL07, HSL08, HSL09, HSL10, HSL11, HSL12, HSL13, HSL14, HSL15, HSL16, KSLVT, KSL01, KSL02, KSL03, KSL04, KSL05, KSL06, KSL07, KSL08, KSL09, KSL10, KSL11, KSL12, KSL13, KSL14, KSL15, KSL16, OSLVT, OSL01, OSL02, OSL03, OSL04, OSL05, OSL06, OSL07, OSL08, OSL09, OSL10, OSL11, OSL12, OSL13, OSL14, OSL15, OSL16)" _
    & " VALUES (" & "'" & CDbl(Sheets("DATA").Cells(r, 1)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 2)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 3)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 4)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 5)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 6)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 7)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 8)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 9)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 10)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 11)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 12)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 13)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 14)) & "'," & "'" _
& CStr(Sheets("DATA").Cells(r, 15)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 16)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 17)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 18)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 19)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 20)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 21)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 22)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 23)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 24)) & "'," & "'" _
& CStr(Sheets("DATA").Cells(r, 25)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 26)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 27)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 28)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 29)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 30)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 31)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 32)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 33)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 34)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 35)) & "'," & "'" & CStr(Sheets("DATA").Cells(r, 36)) & "'," & "'" _
& CStr(Sheets("DATA").Cells(r, 37)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 38)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 39)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 40)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 41)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 42)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 43)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 44)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 45)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 46)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 47)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 48)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 49)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 50)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 51)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 52)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 53)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 54)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 55)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 56)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 57)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 58)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 59)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 60)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 61)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 62)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 63)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 64)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 65)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 66)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 67)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 68)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 69)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 70)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 71)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 72)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 73)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 74)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 75)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 76)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 77)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 78)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 79)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 80)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 81)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 82)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 83)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 84)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 85)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 86)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 87)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 88)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 89)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 90)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 91)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 92)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 93)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 94)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 95)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 96)) & "'," & "'" _
& CDbl(Sheets("DATA").Cells(r, 97)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 98)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 99)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 100)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 101)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 102)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 103)) & "'," & "'" & CDbl(Sheets("DATA").Cells(r, 104)) & "'," & "'" & CDbl(Sheets("DATA ").Cells(r, 105)) & "')"
          
adoCN.Execute sSQL
    r = r + 1
    str = Sheets("DATA").Cells(r, 1).Value
 
 Wend
                 
adoCN.Close
 
Set adoCN = Nothing
 
 
End Sub





Does anyone know where I can find a complete list of VBA functions which includes
definitions of the return code data types? I am particularly interested in knowing
what data type is returned by the Application.Search function.

At the moment I've got the return value var declared as a variant (no choice since I
don't know the data type). Now I'm sifting through my code getting rid of these
dreaded variants wherever possible (mainly for performance reasons) and my search
call is receiving special attention since it is in a large loop (executed 1000s of times).

I checked various MS sites and had no joy so far.

I would very grateful for any leads on this.

Many thanks all,

Richard.


I have a propietary database query application which keeps exporting my queries into text. When I import these files into access as a table it presents problems. I need to convert these data types to numbers so that I can perform sum calculations on them.

Is there a way to do this in the query section of access? If not, Is there a way to specifically tell access that I want specific columns from a specific table to change from a text data type to a number data type upon import?


All,

I would like to know if there is an information about the classes and data types used for Automations in Excel. For example the class CWorkbook has a memeber SaveAs(...) which takes 10 arguments all of them of type VARIANT. I fo not know what they refer to and Micosoft does not provide any description for this.

Do you know by chance what is a VARIANT type in VC++ used for Excel?

Thanks a lot for help,

Pshem


Hello,

In one excel book I have 2 sheets.

1. One sheet (request type wise) contains request type and the phase which it belongs to. eg request type A belongs to phase 1 and request type B belongs to phase 2. (like wise there are 212 request types divided into 8 phases)

2. The second sheet contains raw data (request type) where the request types are randomly arranged.

What I am trying to do is:

Compare the raw data of sheet 2 with standard data in sheet 1 and paste the phase to which it belongs to with a help of macro/code, since the standard data (212 request types with 8 phases) is huge.

Regards
Arif


Hello all,

I am using VBA to try to find the earliest date greater than a given date, from a list of dates. I am using the Worksheet.Application.Match function, passing the third argument as "-1", which according to Excel's VBA help:
Quote:

"If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on."

I use the following code below to fill an array with a set of dates, and then try to return the earliest date greater than my given date. The match function does not return a value, but instead returns a run-time error. If I convert the array to type "Double" and then match the given date (converted to double as well), the match returns the correct answer. Is this is a bug, or am I misunderstanding how the function works? The help page lists the first two arguments of the match function as "Variant", so I don't believe MSFT is indicating that date type values are not valid for this function.

Code:

Sub Test()

Dim DateArray(1 To 2) As Date
Dim NumericDateArray(1 To 2) As Double
Dim CheckDate As Date
Dim NumericCheckDate As Double
Dim DateMatchVal As Double
Dim NumericMatchVal As Double

'Fill array with date values
DateArray(1) = "1/1/2008"
DateArray(2) = "1/1/2007"
'Fill numeric array with type-double values
NumericDateArray(1) = CDbl(DateArray(1))
NumericDateArray(2) = CDbl(DateArray(2))

'Assign date to match to the array
CheckDate = "6/30/2007"
NumericCheckDate = CDbl(CheckDate)

'Using numerals (type double) returns the expected answer (1), which corresponds to the smallest
'value in the match's 2nd argument greater than or equal to the first argument
NumericCheckDate = Application.WorksheetFunction.Match(NumericCheckDate, NumericDateArray(), -1)

'Using dates (type date) returns a run-time error
On Error Resume Next
    DateMatchVal = Application.WorksheetFunction.Match(CheckDate, DateArray(), -1)
    Debug.Assert Err.Number = 0
On Error GoTo 0


End Sub





Just typed all this out but realised I can't add attachments!!!!!! Where can I create an image of my excel file online, anyone? please read my mail in the meantime anyway, thanks.

I have a sheet of statistics, 'Sheet 1' on the attached sample.

They all have the same metadata, which is detailed in row 1.

The data is then split into sub-types, which are shown in row 2, 19, 22, 24, 26 etc on Sheet 1. The syb-types are in blue font and the whole row is merged into 1 cell.

Underneath is the data for that sub type. Some have multiple row, as in the first one, which has 16, while some (the rest on this sample) have just 1 row of data.

I would like to convert this to as is shown in 'Sheet2', where there is a new column A (sub type name) and the relevant sub type is copied into that column.

Anyone got any ideas how to do this (non-manually as there are a few thousand records)

(There are further questions regarding other formatting further on in my sheet but I'll see if this is a starter with what we have first)