Access Import from Excel

[daniroy]

Hello everybody, your help will be very very welcome on a very classic
question...

I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of data. They are setup as follow,
with a few more columns on the rights as stock prices, dividend and so
on as:

Date Code Country Adjust Reason Name Shares
25/02/2005 FR0000045072 FR CREDIT AGRICOLE
25/02/2005 FR0000054900 FR TF1

I have setup an excel macro that save the date once I have downloaded
them, with a sheet name including the daily date as:
DivivendData28_Feb_05. Tomorrow it will thus be saved as
DivivendData29_Feb_05

Here difficulties arise.

I wish to store these data in a single access database. I did begin in
a naïve way as I am not an expert at excel. I did create a blank
database and did manually import one the spreadsheet. Went fine. But as
I import the second one I do receive error messages stating that:
"Method 'Columns' of object 'IllmexGrid'failed" and then
"File was not imported"

Anyway, I would like to automatize from Access or Excel, if it is
possible.

Regards
Daniel

[Rog]

Check out the TransferSpreadsheet method in the help file.

Use a variable for the filename argument, e.g.
strFileName = "DividendData" & Format(Date, "dd") & "_" * Format(Date,
"mmm") & "_" & Format(Date, "yy")

....although I would recommend using the yyyymmdd format. Oh, and I
think you will find tomorrow is not February 29 :-)


da*****@gmail.com wrote:

Hello everybody, your help will be very very welcome on a very classic question...

I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of data. They are setup as follow, with a few more columns on the rights as stock prices, dividend and so on as:

Date Code Country Adjust Reason Name Shares
25/02/2005 FR0000045072 FR CREDIT AGRICOLE
25/02/2005 FR0000054900 FR TF1

I have setup an excel macro that save the date once I have downloaded
them, with a sheet name including the daily date as:
DivivendData28_Feb_05. Tomorrow it will thus be saved as
DivivendData29_Feb_05

Here difficulties arise.

I wish to store these data in a single access database. I did begin in a naïve way as I am not an expert at excel. I did create a blank
database and did manually import one the spreadsheet. Went fine. But as I import the second one I do receive error messages stating that:
"Method 'Columns' of object 'IllmexGrid'failed" and then
"File was not imported"

Anyway, I would like to automatize from Access or Excel, if it is
possible.

Regards
Daniel

[daniroy]

Thanks, I am having a look I come back to you.

Good spot I just realized what year are we ...

[daniroy]

I do not want to abuse from your kindness but I am really dumb, I have
a problem on the file name setup.
Here is how the excel macro save the daily file on a folder:

ChDir "C:\Documents and Settings\Daniel\My Documents\Dividend and
Index Pricing\CAC 40\Dividend Data"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Daniel\My Documents\Dividend and
Index Pricing\CAC 40\Dividend Data" & _
Format(Date, "dd_mmm_yy") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Thus, what should I write in the FileName field in Access ? I know I
push a little bit far away the boundary of stupidity ...

Regards
DR

[Rog]

dani,
Never underestimate yourself :-)

dim strFilename as string
strFilename = Format(Date, "dd_mmm_yy") & ".xls"
docmd.transferspreadsheet acImport, , Table1, strFilename

You can leave the second argument blank (hence 2 commas after
acImport).
Replace Table1 by your table name.
Add another argument , True after strFilename if the spreasheets have a
first row containing the field names.

Hope this helps.

[daniroy]

Thanks mate, but do not overestimate me neither as I will ask you
something really dumb.
In Access, I click on the left panel on macro.
I am trying to enter the function in the field name. Obviously it does
not work at ALL. I am missing a few points and steps isn'it ? How do
you even access the Visual Basic panel on Access? I am really Access
illiterate...

Regards
DR

[Rog]

Dani,

OK, we're not on the same page here. I'm not talking about a macro,
but about VB code. You cannot make a "flexible" macro, all the
arguments are fixed. So what you need to do is write code to
accomplish this. Although there are other methods, the easiest thing
to do is create a form and, in design mode, place a command button on
it. Cancel out of the command button wizard if that opens. Select the
button, go into its properties (the properties window should be open
but if it isn't, right click and select properties), select the Event
tab, Select the On Click property, click on the three dots next to it,
and select Code builder. Now, after the Private sub Commandx_Click
line, enter the code I gave you. Save the form, open it and click the
button to perform the import.

[daniroy]

Thank you once again
I know that I am a real pain in the ...

I am thus editing the code like this

Private Sub Command0_Click()

Dim strFilename As String
strFilename = Format(Date, "dd_mmm_yy") & ".xls"
DoCmd.TransferSpreadsheet acImport, , CAC40_Weightings, strFilename,
True

End Sub

But when I run it by clicking on the form button, the debugger opens
with a pop-up window stating that:
"Compile Error
Expected function or variable"
highlighting "CAC40_Weightings" in the code, that is to say the table I
want to update.

If you want to call it a day and tell me to ... off I can understand as
it is a complete waste of time for you ! ,-)

Meanwhile, regards
Daniel

[Alex]

How about this:

Unless you have like a gazillion tabs in your spreadsheet, just copy
and paste!

Since you have done one successfull import already, you should have a
table in your database. Go into excel, select the data you want to
import (that you havn't before) and select it all. Do an Edit->Copy.

Go back into access and open the table. Scroll down to the bottom of
the table and select the last (blank) line. Then go to Edit -> Paste
Append.

Repeat as necessary.

If the table is setup correctly, it should correctly interpret all of
the fields.

Alex.

da*****@gmail.com wrote:

Hello everybody, your help will be very very welcome on a very classic question...

I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of data. They are setup as follow, with a few more columns on the rights as stock prices, dividend and so on as:

Date Code Country Adjust Reason Name Shares
25/02/2005 FR0000045072 FR CREDIT AGRICOLE
25/02/2005 FR0000054900 FR TF1

I have setup an excel macro that save the date once I have downloaded
them, with a sheet name including the daily date as:
DivivendData28_Feb_05. Tomorrow it will thus be saved as
DivivendData29_Feb_05

Here difficulties arise.

I wish to store these data in a single access database. I did begin in a naïve way as I am not an expert at excel. I did create a blank
database and did manually import one the spreadsheet. Went fine. But as I import the second one I do receive error messages stating that:
"Method 'Columns' of object 'IllmexGrid'failed" and then
"File was not imported"

Anyway, I would like to automatize from Access or Excel, if it is
possible.

Regards
Daniel

[dkintheuk]

Simple fix for you Dani...

CAC40_Weightings needs to have double quotes around it to work
properly...

It is not a variable so you have to put quotes around it to make VB
realise what you mean (i.e. EXACTLY this NOT a variable that is called
CAC40_Weightings that could contain anything).

Does that help you now?

Can you get it to work?

Rob.

[daniroy]

Wow brillant, good spot!
Now it is working, the form enable me to retrieve the data and creates
the "CAC40_Weighting" table to store in data.

Unfortunately, when I want to manualy import (File=>External
Data=>Import) from Excel to data of previous day - as the code is
working for today data - (notably the Dividend Data25_Feb_05.xls
spreadsheet) the following happens:

first: Method 'Columns' of object 'IImexGrid' failed

then, when I highlight that first row includes header:
"The first row contains some data that cant be used for valid Access
field names. In these cases the wizard will automaticely assign valid
field names"

I finally ask the wizard to add the new data to the CAC40_Weightings
tables and receive an arror occured trying to import file and that it
has not been imported.

My mental stake is at play ...
Regards everybody
Daniel

[Ken Snell]

"Rog" <de********@state.gov> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Dani,

OK, we're not on the same page here. I'm not talking about a macro,
but about VB code. You cannot make a "flexible" macro, all the
arguments are fixed.

Actually, you can use "flexibility" in a macro. For example, to do the
filename that you're proposing, set the FileName argument to this expression
(include the = sign):

= Format(Date(), "dd_mmm_yy") & ".xls"
--

Ken Snell
<MS ACCESS MVP>

[Rog]

Ken,
Thanks for that tip, I learn every day!

[Ken Snell]

You're welcome. I don't think the Help files are very explicit about it, but
I've found that you can use expressions in just about every argument for the
various macro actions. Think of those expressions the same way you would in
VBA code. Except, of course, you cannot use a variable there, as macros
cannot read variables; but you can use functions, and functions can return
the value of variables.

--

Ken Snell
<MS ACCESS MVP>

"Rog" <de********@state.gov> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Ken,
Thanks for that tip, I learn every day!

[daniroy]

thanks to everybody, but I have not finished with questions if you
still have time. But I will come back on new points on tomorrow ...

regards
Daniel

[Rich P]

try this code. I will link your Excel sheet to Access programatically.
Just replace the workbook name "Book1.xls" with the name of your actual
workbook (where ever it says Book1 - replace that with the name of your
actual workbook) and replace "Sheet1$" with the actual name of your
spreadsheet - make sure to include the $ (dollar sign is required). If
you have spaces in your sheet name, you need to eliminate the spaces -
use underscore _. Add this code to a standard module. Then press the
F5 function key to run the code. Then you can just use queries against
the linked table. Oh, and the subroutine assumes that your Access mdb
and your Excel file reside in the same directory.

-------------------------------------------------------
Sub ConnectToExcel()
Dim DB As Database, tdf As TableDef, strPath As String
Dim strTable As String, strConnect As String
Dim strSourceTable As String

Set DB = CurrentDb
strPath = Left(DB.Name, Len(DB.Name) - Len(Dir(DB.Name)))
strPath = strPath & "Book1.xls"
strTable = "Book1"
strConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strPath
strSourceTable = "Sheet1$"

For Each tdf In DB.TableDefs
If tdf.Name = "Book1" Then DB.TableDefs.Delete tdf.Name
Next
Set tdf = DB.CreateTableDef(strTable)
tdf.Connect = strConnect
tdf.SourceTableName = strSourceTable
DB.TableDefs.Append tdf
Application.RefreshDatabaseWindow

End Sub
--------------------------------------------------------

The nice thing about this routine is that if you have several worksheets
you can write a loop and loop through a list of spreadsheets (use the
Array function to create an array of spreadsheet names and loop through
the array - goto help lookup Array)

Rich

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