|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
February 8th, 2005, 11:55 AM
|
|||
|
|||
Input filename using txtbox with Excel Query
I have the following code for importing data from a CVS file into Excel. This works file for a file of a given name (book7) as in example 1. I want to make the macro flexible so that users can enter the file name, which is in the same directory. I included a textbox (TXTFILE) to input the file name and made modifications to the code (Example 2) expecting it obtain the file name from the textbox. Instead I get the following error when attempting to execute the macro. It stops at the line “With Selection.QueryTable”
RUN TIME ERROR ‘1004’ APPLICATION-DEFINED OR OBJECT-DEFINED ERROR. Can any one point me in the right direction of resolving this error . Thanks Tigre Example 1 (this works) ' rawdata Macro ' Macro Date 2/3/2005 (Tigre) 'This macro imports raw data from CVS file created by the analyzer ' With Selection.QueryTable .Connection = Array(Array( _ "ODBC;DSN=Excel Files;DBQ=C:\Documents and Settings\Tigre\Desktop\Book7.xls;DefaultDir=C:\Doc uments and Settings\Tigre" _ ), Array("Tigre\Desktop;DriverId=790;MaxBufferSize=2048;Page Timeout=5;")) .CommandText = Array( _ "SELECT `Sheet1$`.Index, `Sheet1$`.Time, `Sheet1$`.`Ch 1 Raw Data`, `Sheet1$`.`Ch 1 Load`, `Sheet1$`.`Ch 1 Energy`, `Sheet1$`.`Ch 1 Velocity`, `Sheet1$`.`Ch 1 Deflection`, `Sheet1$`.F8, `Sheet1$`.Time1" _ , _ ", `Sheet1$`.`Ch 1 Load1`" & Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Settings\Tigre\Desktop\Book7`.`Sheet1$` `Sheet1$`" & Chr(13) & "" & Chr(10) & "WHERE (`Sheet1$`.Time>=0) AND (`Sheet1$`.`Ch 1 Load`>=0)" _ ) .Refresh BackgroundQuery:=False End With End Sub EXAMPLE 2 ( this returns error ‘1004’) Sub rawdatabyfile() ' ' rawdata Macro ‘Macro Date 2/3/2005 (Tigre) ‘ ‘This macro imports raw data from CVS file created by the analyzer Dim txtfile With Selection.QueryTable .Connection = Array(Array( _ "ODBC;DSN=Excel Files;DBQ=C:\Documents and Settings\Tigre\Desktop\txtfile;DefaultDir=C:\Docum ents and Settings\Tigre" _ ), Array("tigre\Desktop;DriverId=790;MaxBufferSize=2048;Page Timeout=5;")) .CommandText = Array( _ "SELECT `Sheet1$`.Index, `Sheet1$`.Time, `Sheet1$`.`Ch 1 Raw Data`, `Sheet1$`.`Ch 1 Load`, `Sheet1$`.`Ch 1 Energy`, `Sheet1$`.`Ch 1 Velocity`, `Sheet1$`.`Ch 1 Deflection`, `Sheet1$`.F8, `Sheet1$`.Time1" _ , _ ", `Sheet1$`.`Ch 1 Load1`" & Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Settings\Tigre\Desktop\txtfile`.`Sheet1$` `Sheet1$`" & Chr(13) & "" & Chr(10) & "WHERE (`Sheet1$`.Time>=0) AND (`Sheet1$`.`Ch 1 Load`>=0)" _ ) .Refresh BackgroundQuery:=False End With End Sub
|
|
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > Input filename using txtbox with Excel Query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
