Import Excel to Access

[Denver]

hi,

i have a list box that displays all imported weekly report....

SELECT [Name] FROM MSysObjects WHERE [Type]=1 AND [Name] LIKE
"*exception06W*" ORDER BY [Name];

i have this Command0 that imports my weekly report to Access and i have this
code...

On Error GoTo Err_Command0_Click

Dim i As Integer

For i = 0 To List1.ListCount - 1

If List1.Selected(i) Then
DoCmd.TransferSpreadsheet acImport, List1.Column(0, 1), _
"D:\" & List1.Column(0, i) & ".xls", True, ""
MsgBox "Table is already Imported"
End If

Next i
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

the problem is when i click on this Command0 nothing will happen...
what do else do i need to write from this code? or what do i miss to write?

i want that when i click Command0 it prompts drive D:\ window so that
users have the option to choose what to import..

hope there is someone is kind to help me with this code...


thanks

 

[Jeff Boyce]

You've described "how".

"How" depends on "what". What are you trying to accomplish (with the
approach you mentioned), and what does the underlying data look like?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Denver]

what i want to accomplish is when i click Command0 it imports my weekly
report from Excel to Access 2003..but the problem is the code that i have in
Command0 doesnt work... can you help me how this codes works will..

Dim i As IntegerExit Sub


thanks a lot Jeff Boyce.... i appreciated it

denver

You've described "how".

"How" depends on "what". What are you trying to accomplish (with the
approach you mentioned), and what does the underlying data look like?

Regards

Jeff Boyce
Microsoft Office/Access MVP

hi,

i have a list box that displays all imported weekly report....

SELECT [Name] FROM MSysObjects WHERE [Type]=1 AND [Name] LIKE
"*exception06W*" ORDER BY [Name];

i have this Command0 that imports my weekly report to Access and i have
this
code...

On Error GoTo Err_Command0_Click

Dim i As Integer

For i = 0 To List1.ListCount - 1

If List1.Selected(i) Then
DoCmd.TransferSpreadsheet acImport, List1.Column(0, 1), _
"D:\" & List1.Column(0, i) & ".xls", True, ""
MsgBox "Table is already Imported"
End If

Next i
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

the problem is when i click on this Command0 nothing will happen...
what do else do i need to write from this code? or what do i miss to
write?

i want that when i click Command0 it prompts drive D:\ window so that
users have the option to choose what to import..

hope there is someone is kind to help me with this code...


thanks

 

[Jeff Boyce]

Again, you are describing "how" ("command0", code, ...).

Let's see if I can paraphrase your situation...

You have data that is compiled (weekly) in Excel. You want to be able to
use that data from within Access.

If that's a reasonable description of "what", then you might want to
consider linking to Excel rather than importing (that way, you don't have
two copies of the same data floating about, with increased risk of data
integrity issues).

And it would probably help the folks here to answer your specific questions
if you provided specific information about what you want to DO with the data
from within Access. How are you intending to use it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

what i want to accomplish is when i click Command0 it imports my weekly
report from Excel to Access 2003..but the problem is the code that i have
in
Command0 doesnt work... can you help me how this codes works will..

Dim i As IntegerExit Sub


thanks a lot Jeff Boyce.... i appreciated it

denver

You've described "how".

"How" depends on "what". What are you trying to accomplish (with the
approach you mentioned), and what does the underlying data look like?

Regards

Jeff Boyce
Microsoft Office/Access MVP

hi,

i have a list box that displays all imported weekly report....

SELECT [Name] FROM MSysObjects WHERE [Type]=1 AND [Name] LIKE
"*exception06W*" ORDER BY [Name];

i have this Command0 that imports my weekly report to Access and i have
this
code...

On Error GoTo Err_Command0_Click

Dim i As Integer

For i = 0 To List1.ListCount - 1

If List1.Selected(i) Then
DoCmd.TransferSpreadsheet acImport, List1.Column(0, 1), _
"D:\" & List1.Column(0, i) & ".xls", True, ""
MsgBox "Table is already Imported"
End If

Next i
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

the problem is when i click on this Command0 nothing will happen...
what do else do i need to write from this code? or what do i miss to
write?

i want that when i click Command0 it prompts drive D:\ window so that
users have the option to choose what to import..

hope there is someone is kind to help me with this code...


thanks

 

[Denver]

Jeff Boyce,

thanks for your patience i appreciate it.

You have data that is compiled (weekly) in Excel. You want to be able to

use that data from within Access.

***** Yes i have a compiled data from Excel and i want this Command0 to
Import this file to access. and i don't know how to write my codes in
importing this data to access (Excel to Access).
----------
If that's a reasonable description of "what", then you might want to

consider linking to Excel rather than importing (that way, you don't have
two copies of the same data floating about, with increased risk of data
integrity issues).

***** i like your suggestion linking from Excel to Access but for now i just
want to work on importing this excel data to access

**************************************************

Again, you are describing "how" ("command0", code, ...).

Let's see if I can paraphrase your situation...

You have data that is compiled (weekly) in Excel. You want to be able to
use that data from within Access.

If that's a reasonable description of "what", then you might want to
consider linking to Excel rather than importing (that way, you don't have
two copies of the same data floating about, with increased risk of data
integrity issues).

And it would probably help the folks here to answer your specific questions
if you provided specific information about what you want to DO with the data
from within Access. How are you intending to use it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

what i want to accomplish is when i click Command0 it imports my weekly
report from Excel to Access 2003..but the problem is the code that i have
in
Command0 doesnt work... can you help me how this codes works will..

Dim i As Integer

For i = 0 To List1.ListCount - 1

If List1.Selected(i) Then
DoCmd.TransferSpreadsheet acImport, List1.Column(0, 1), _
"D:\" & List1.Column(0, i) & ".xls", True, ""
MsgBox "Table is already Imported"
End If

Next i
Exit_Command0_Click:

Exit Sub


thanks a lot Jeff Boyce.... i appreciated it

denver

You've described "how".

"How" depends on "what". What are you trying to accomplish (with the
approach you mentioned), and what does the underlying data look like?

Regards

Jeff Boyce
Microsoft Office/Access MVP

hi,

i have a list box that displays all imported weekly report....

SELECT [Name] FROM MSysObjects WHERE [Type]=1 AND [Name] LIKE
"*exception06W*" ORDER BY [Name];

i have this Command0 that imports my weekly report to Access and i have
this
code...

On Error GoTo Err_Command0_Click

Dim i As Integer

For i = 0 To List1.ListCount - 1

If List1.Selected(i) Then
DoCmd.TransferSpreadsheet acImport, List1.Column(0, 1), _
"D:\" & List1.Column(0, i) & ".xls", True, ""
MsgBox "Table is already Imported"
End If

Next i
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

the problem is when i click on this Command0 nothing will happen...
what do else do i need to write from this code? or what do i miss to
write?

i want that when i click Command0 it prompts drive D:\ window so that
users have the option to choose what to import..

hope there is someone is kind to help me with this code...


thanks

 

[Jeff Boyce]

Denver

If you have limited experience creating procedures in VBA, you'll need to
decide if now it the time to learn, or if getting the job done is more
important to you. If you just need the job done, consider looking at how
Access Macros work... the learning curve is not as steep as VBA (but the
flexibility is not as great).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff Boyce,

thanks for your patience i appreciate it.

You have data that is compiled (weekly) in Excel. You want to be able to

use that data from within Access.

***** Yes i have a compiled data from Excel and i want this Command0 to
Import this file to access. and i don't know how to write my codes in
importing this data to access (Excel to Access).
----------
If that's a reasonable description of "what", then you might want to

consider linking to Excel rather than importing (that way, you don't have
two copies of the same data floating about, with increased risk of data
integrity issues).

***** i like your suggestion linking from Excel to Access but for now i
just
want to work on importing this excel data to access

**************************************************

Again, you are describing "how" ("command0", code, ...).

Let's see if I can paraphrase your situation...

You have data that is compiled (weekly) in Excel. You want to be able to
use that data from within Access.

If that's a reasonable description of "what", then you might want to
consider linking to Excel rather than importing (that way, you don't have
two copies of the same data floating about, with increased risk of data
integrity issues).

And it would probably help the folks here to answer your specific
questions
if you provided specific information about what you want to DO with the
data
from within Access. How are you intending to use it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

what i want to accomplish is when i click Command0 it imports my weekly
report from Excel to Access 2003..but the problem is the code that i
have
in
Command0 doesnt work... can you help me how this codes works will..

Dim i As Integer

For i = 0 To List1.ListCount - 1

If List1.Selected(i) Then
DoCmd.TransferSpreadsheet acImport, List1.Column(0, 1), _
"D:\" & List1.Column(0, i) & ".xls", True, ""
MsgBox "Table is already Imported"
End If

Next i
Exit_Command0_Click:
Exit Sub


thanks a lot Jeff Boyce.... i appreciated it

denver
:

You've described "how".

"How" depends on "what". What are you trying to accomplish (with the
approach you mentioned), and what does the underlying data look like?

Regards

Jeff Boyce
Microsoft Office/Access MVP

hi,

i have a list box that displays all imported weekly report....

SELECT [Name] FROM MSysObjects WHERE [Type]=1 AND [Name] LIKE
"*exception06W*" ORDER BY [Name];

i have this Command0 that imports my weekly report to Access and i
have
this
code...

On Error GoTo Err_Command0_Click

Dim i As Integer

For i = 0 To List1.ListCount - 1

If List1.Selected(i) Then
DoCmd.TransferSpreadsheet acImport, List1.Column(0, 1), _
"D:\" & List1.Column(0, i) & ".xls", True, ""
MsgBox "Table is already Imported"
End If

Next i
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

the problem is when i click on this Command0 nothing will happen...
what do else do i need to write from this code? or what do i miss to
write?

i want that when i click Command0 it prompts drive D:\ window so
that
users have the option to choose what to import..

hope there is someone is kind to help me with this code...


thanks