Importing from excel

[roamer762]

Hello,

I have a spreadsheet in excel and i am inporting it into access usring the tarnsfer spreadsheet command. However one of the colums to be imported is being treated as double when actually I have sone of its records in text format .So when import is finished I am ending up with an import error.Since I do not have any control of how the access decides to import a particular colums...i tried to open this spreadsheet save it as text and then using the transfer text method.


Is there a way of how to implement this automatically.
I would like to open automatically the excel and the required spreadsheet and either save it as text and then importing it by the tranfertext or selecting all records in the excel sheet , copy them and pasting them in my access table.



Regards

 

[GingerR]

i've imported tons of stuff from excel, and i've found the best thing to do is to import into a 'temp' table, however access wants to. then write an append query to put the imported data into your 'real' table how you need it to be. you can automate all that in a macro when you're done.

do you know about macros?

you can write a macro that
1) imports the excel sheet (you would have to name it the same and put in the same place/folder all the time, and have the same fields in the same order, etc)
2) then runs an APPEND QUERY that adds the newly-imported records into your 'real' table.

if you have to first delete 'old' records from either 'temp' or 'real' tables, you can do that in the macro too.

 

[Crystalguru]

GingerR,

From the above post, you mention you can write a macro that will import an excel spreadsheet. That is exactly what I trying to do, but the file location will not always be the same, is there a solution to that?

thanks
crystalguru

 

[GingerR]

depending on how you have things set up, you could make a form with a text box where you enter the path, or just put that info into a table and reference that form or table field in a vba module (use transfer text code in a module instead of in a macro). i always just set up my world so the file is in the same place and called the same thing, even if that means i manually make a copy and rename it first.

 

[janetlyn]

Ginger, I have a form with 30 unbound text boxes. I would like to go to the same Excel file each month, and have one cell populate one text box. The cell=text box will always be the same only data would change each month. Here is the code I am using in an Open Form Sub to try and get the data. I only have it set to grab one cell because I was just trying to make it work before I hooked up all 30:


Call Shell("excel.exe M:\Accounting\Financials\Reconcilations\access.xls", 4)
Range("A2&quot.Seelct
ActiveCell.CurrentRegion.Select
Selection.Copy
Selection.Paste Me.E1

The debugger does not like Range. I had it as Column and Row before and it did not like those either. Any clues you can help me with please? JL