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'fai led" 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 TransferSpreads heet method in the help file.

Use a variable for the filename argument, e.g.
strFileName = "DividendDa ta" & 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.c om 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'fai led" 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:\Documen ts and Settings\Daniel \My Documents\Divid end and
Index Pricing\CAC 40\Dividend Data"
ActiveWorkbook. SaveAs Filename:= _
"C:\Documen ts and Settings\Daniel \My Documents\Divid end and
Index Pricing\CAC 40\Dividend Data" & _
Format(Date, "dd_mmm_yy" ) & ".xls" _
, FileFormat:=xlN ormal, Password:="", WriteResPasswor d:="", _
ReadOnlyRecomme nded:=False, CreateBackup:=F alse

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.transfers preadsheet 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.TransferS preadsheet acImport, , CAC40_Weighting s, 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_Weightin gs" 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.c om 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'fai led" 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_Weighting s 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_Weighting s that could contain anything).

Does that help you now?

Can you get it to work?

Rob.