By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,955 Members | 1,768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,955 IT Pros & Developers. It's quick & easy.

Importing Excel with Quotes in Name?

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.Application, wkbk As Excel.Workbook
Dim sht As Excel.Worksheet, i As Integer, j As Integer

MyPath = "C:\yourExcelFilesDir\"
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("Excel.Application")
Set wkbk = xlObj.Workbooks.Open(MyPath & MyName)
Set sht = wkbk.Sheets("Sheet1")
Set rng = sht.UsedRange
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
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!
Nov 13 '05 #2

P: n/a
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.Application, wkbk As Excel.Workbook
Dim sht As Excel.Worksheet, i As Integer, j As Integer
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("tblExcelData")
MyPath = "C:\yourExcelFilesDir\"
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("Excel.Application")
Set wkbk = xlObj.Workbooks.Open(MyPath & MyName)
Set sht = wkbk.Sheets("Sheet1")
Set rng = sht.UsedRange
For i = 1 To rng.Rows.Count
rs.Addnew
For j = 1 To rng.Columns.Count
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!
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.