Importing Excel with Quotes in Name?

[RustyR]

Hi,

I have 5 excel spreadsheets that are exported to me weekly.
At first, I could not get them to import. Then I did a File -> Save as and
noticed that the Filenames of the spreadsheets contained Quotes around
them - hence the problem.

Is there any way to remove those quotes prior to import (other than "Save
As")???

Thanks in advance!

Rusty

[Rich P]

Don't know if this is close to your situation, but here is something
that works for me - note: you need to make a reference to the Microsoft
Excel Object Library from a Standard Module Menu - Tool/References

Sub GetExcelData()
Dim MyPath As Variant, MyName As Variant, rng As Excel.Range
Dim xlObj As Excel.Applicati on, wkbk As Excel.Workbook
Dim sht As Excel.Worksheet , i As Integer, j As Integer

MyPath = "C:\yourExcelFi lesDir\"
MyName = Dir(MyPath & "*.xls") ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
If MyName = "Book1 And test's.xls" Then
Debug.Print MyName ' Display entry only if it
Set xlObj = CreateObject("E xcel.Applicatio n")
Set wkbk = xlObj.Workbooks .Open(MyPath & MyName)
Set sht = wkbk.Sheets("Sh eet1")
Set rng = sht.UsedRange
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Cou nt
Debug.Print rng(i, j);
Next
Debug.Print
Next
wkbk.Close
xlObj.Quit
Set wkbk = Nothing
Set xlObj = Nothing
End If
MyName = Dir ' Get next entry.
Loop
End Sub

You can replace the Debugs with DAO code to write the data to a table in
Excel.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

I meant a table in Access. Here is some sample DAO code to go with the
routine: Say you have 10 columns of data in Excel, your table would
have 10 fields.

Sub GetExcelData()
Dim MyPath As Variant, MyName As Variant, rng As Excel.Range
Dim xlObj As Excel.Applicati on, wkbk As Excel.Workbook
Dim sht As Excel.Worksheet , i As Integer, j As Integer
Dim rs As Recordset

Set rs = CurrentDb.OpenR ecordset("tblEx celData")
MyPath = "C:\yourExcelFi lesDir\"
MyName = Dir(MyPath & "*.xls") ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
If MyName = "Book1 And test's.xls" Then
Set xlObj = CreateObject("E xcel.Applicatio n")
Set wkbk = xlObj.Workbooks .Open(MyPath & MyName)
Set sht = wkbk.Sheets("Sh eet1")
Set rng = sht.UsedRange
For i = 1 To rng.Rows.Count
rs.Addnew
For j = 1 To rng.Columns.Cou nt
rs(j - 1) = rng(i, j)
Next
rs.Update
Next
wkbk.Close
xlObj.Quit
Set wkbk = Nothing
Set xlObj = Nothing
End If
MyName = Dir ' Get next entry.
Loop
End Sub

The routine now assumes you have a table called tblExcelData which would
have the same fields (and datatypes per field) as the Excel Columns.
Also, the Excel range object, rng starts counting at 1. The DAO
recordset object starts counting at 0, thus rs(j - 1) = rng(i, j) to get
the columns synchronized correctly between Excel and Access. Oh yeah,
notice that the title of the excel workbook in my example contains a
quote

"Book1 And test's.xls"

which is how you get inside the "If" statement in the loop.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!