importing excel and odbc dsns

[Thai]

hello,

I have a few questions, please help if you can...

#1. Is there a way to create, edit and delete the system dns's through vb code?

#2. I am using a DAO database to connect to an Excel file then import it into an ms access database. It works fine, but when there is a space in front of the column name in the excel file, it crashes. Is there a way to edit the field names of a recordset that is connected to an excel file through DAO or ADO?

I have tried:

For I = 0 To rs.Fields.Count -1
rs.Fields(I).Name = Trim(rs.Field(I).Name)
next


I tried that, but I get an error.

Thanks,
Thai

[JHausmann]

For #1, try:

http://forums.vb-world.net/showthread.php?threadid=12044

For #2, you might try creating an access table by using select .. into, using the excel table as the source

[Thai]

thanks for the link to the API, I am trying to use it but am having problems.


If SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, _
"Microsoft Excel Driver (*.xls)", _
"DSN=TEST" & Chr(0) & _
"DESCRIPTION=TESTING DSN" & Chr(0) & _
"SOURCETYPE=WORKBOOK" & Chr(0) & _
"SOURCEDB=C:\timesheet.xls" & Chr(0) & _
"BACKGROUNDFETCH=YES" & Chr(0) & _
"NULL=YES" & Chr(0) & _
"DELETED=YES" & Chr(0) & _
"EXCLUSIVE=NO" & Chr(0) & Chr(0)) Then
MsgBox "DSN Created"
Else
MsgBox "Create Failed"
End If


It adds the DSN, but there is no workbook connected to it even though I tried to specify it. Do you know what needs to change in that code to make it add the workbook also? thanks..

About my second question, I am doing exactly what you say, using a SELECT INTO statement, and it works fine except when there is a leading space in the column header of the excel file, then it bombs out. My solution was to open the excel file as an excel app and trim the spacing of the first row. Here is my code for that so far:


Dim myExcelApp as New Excel.Application
Dim myExcelWrk as New Excel.Worksheet

myExcelApp.Workbooks.Open "C:\timesheet.xls"
Set myExcelWrk = myExcelApp.Worksheets(0)


How do I loop through each column in the first row only and trim the spaces in it?

Thanks,
Thai

[JHausmann]

#1
I advocated using a VB reference and then calls based on that reference, in the thread I showed you. I can't help with this particular problem.

#2

a simple loop ought to do it:

for i = 1 to end_of_excel_data
myExcelWrk.Cells(i,1).Value = trim(myExcelWrk.Cells(i,1).Value))
next i

[Thai]

end_of_excel_data .. is that a vb thing or someting I need to define? and if so, how do I define that? Btw, I figured out the DSN, so this is the only other problem I have currently :)

Thanks,
Thai

[JHausmann]

It's a variable (or constant) that you create that contains the number representing the letter of the last column that you want the loop to work on. If you only have columns A through J, the number would be 10.

[Thai]

how can i find how many columns there are so I can set that variable?

thanks
thai

[JHausmann]

I'd just open the spreadsheet and count. If your columns are dynamically changing, you could do:

dim end_of_headers_found as boolean
dim i as integer

end_of_headers_found = false
i = 1

do until end_of_headers_found
myExcelWrk.Cells(i,1).Value = trim(myExcelWrk.Cells(i,1).Value))

if len(trim(myExcelWrk.Cells(i,1).Value)) < 1 then
end_of_headers_found=true
end if

i=i+1

loop

[JHausmann]

I just noticed I incremented rows not columns so my commands should read:


myExcelWrk.Cells(1,i).Value = trim(myExcelWrk.Cells(1,i).Value))


for example.

The last post's code should be:


dim end_of_headers_found as boolean
dim i as integer

end_of_headers_found = false
i = 1

do until end_of_headers_found
myExcelWrk.Cells(1,i).Value = trim(myExcelWrk.Cells(1,i).Value))

if len(trim(myExcelWrk.Cells(1,i).Value)) < 1 then
end_of_headers_found=true
end if

i=i+1

loop

[JHausmann]

Count the cells or set the number to an obviously high value that you know won't be hit.