Importation Statique et Dynamique des plages à partir d'Excel dans MS-Access qui ne commencent pas à la cellule A1

Comment pourrais-je lier une plage de données à partir d'une feuille de calcul Excel pour que les données qui vont apparaît comme une durée de table dans Access?

En le liant à une feuille de calcul Excel qui contient des données de départ à la cellule A1 est assez facile, mais où les données dans le tableur Excel démarre ailleurs dans la feuille, je ne suis pas sûr de la façon d'obtenir l'Accès à de pin-point, surtout si la non-A1 gamme dynamique.

L'accès ne semble pas ramasser nommé/plages dynamiques lors de l'importation/link assistant.

Monde réel scénario:

J'ai un fichier tableur Excel, nous allons l'appeler "ExcelFile1.xls", qui m'est fournie à l'extérieur (donc je ne peux pas vraiment changer sa mise en forme).

1 de la literie/onglets, appelons-la "Dynamique", a une plage de données que je veux disponible comme une table dans Access, mais ses en-têtes de colonne de commencer à la ligne 14, traverser la colonne de EL. Ce que je voudrais que l'Accès à faire est de prendre cette plage de données comme un tableau. En outre, "ExcelFile1.xls" seront également mises à jour régulièrement, c'est à dire une nouvelle version de la "ExcelFile.xls" fichier sera disponible, mais avec plus de données ci-dessous la ligne 14 du en-têtes de colonne, donc idéalement, j'aimerais que l'Accès à ramasser les nouvelles données dans cette gamme, chaque fois que je remplace la précédente version de "ExcelFile1.xls".

J'ai aussi un autre fichier tableur Excel, nous allons l'appeler "ExcelFile2.xls", à condition de m'en externe.

Ce qui a une feuille/onglet, nous allons l'appeler "Statique", que de même a une plage de données que je veux comme une table dans Access, et encore une fois, il y aura de nouvelles versions de "ExcelFile2.xls" qui va remplacer les précédentes versions que j'ai l'idéal serait d'Accès à ramasser. Cette gamme est A14:O19 et sera toujours de cette gamme (c'est à dire statique de la gamme).

Donc en résumé: j'aimerais lier l'Accès à 2 plages de données à partir de 2 respectifs des fichiers Excel pour produire 2 tables distinctes disponibles dans Access. 1 de la source plages de données sera dynamique et l'autre sera statique. J'aimerais Accéder à ramasser sur les nouvelles données si possible simplement en écrasant la source des fichiers Excel avec les nouvelles versions du même nom de fichier et le chemin d'accès.

OriginalL'auteur Matt Hall | 2013-04-10

4

Bon, à partir de la réflexion sur cette question ici, et sur quelques autres que j'ai posée ailleurs, je pense avoir une solution plutôt simple pour tous ceux qui veulent extraire des données à partir d'une feuille de calcul Excel comme une Plage Dynamique ou Statique de la Plage où la dans Excel, les données ne démarre pas dans la cellule A1.

Ces deux exemples, l'utilisation d'un bouton d'attente le code. Manifestement, vous n'avez pas à le faire de cette façon, mais si vous le faites, vous aurez besoin de créer un formulaire et un bouton et puis exécutez le générateur de code hors du bouton et de remplacer ce code avec la solution ci-dessous vous avez besoin.

Gamme Dynamique:

S'il vous plaît être conscient que cet exemple d'une gamme dynamique suppose que votre plage de cellules dans Excel démarre toujours à la même plus haut position la plus à gauche et que le nombre de colonnes est toujours le même, c'est à dire la seule chose qui est dynamique est la ligne du bas numéro de votre plage de cellules.

Les références que vous aurez besoin de changer en fonction de votre propre mis en place:
- C:\Users\Matt\Desktop\ExcelFile1.xls Remplacer par le chemin complet de votre fichier Excel
- Dynamic Remplacer par le nom de la feuille contenue dans votre fichier Excel
- A14:A2000 Remplacer par la plage que vous souhaitez tester pour voir comment beaucoup de non-cellules vides. Cette gamme devrait: commencer à partir de la ligne de l'endroit où vos en-têtes de colonne sont situés; couvrir une colonne dans les données que vous êtes à la recherche d'importation qui n'auront jamais de cellule vide entrées; couvrent un éventail assez large de toujours dépasser le nombre de lignes contenant des données réelles dans votre feuille de calcul Excel.
- ExcelDynamicRangeData Remplacer par ce que vous voulez l'appeler la table dans Access qui contient les données extraites à partir de votre gamme Excel.
- Dynamic!A14:EL Remplacer par le nom de votre feuille, le plus haut/le plus à gauche de la cellule de référence et la colonne la plus à droite de la lettre(s) de votre gamme Excel. Ne comprennent pas le fond le plus/la plus à droite du numéro de ligne que c'est ce qui doit être dynamique, d'où l'attribution de ce à numberofrows et la concaténation à la fin de cette gamme plus tard.
- numberofrows = 13 ... Remplacer la 13 avec toutefois de nombreuses lignes il y a au-dessus de l'endroit où votre en-têtes de colonne de commencer. E. g. si vos en-têtes de colonne de commencer sur la ligne 4, ce nombre doit être de 3.
- Command0 Remplacer par le nom de votre bouton utilisé pour initier le présent code.
```
Sub ImportDataFromRange()

' Assign the Excel Object
Dim excelapp As Object
Set excelapp = CreateObject("excel.application")

' Assign the workbook
Dim wb As Object
Set wb = excelapp.Workbooks.Open("C:\Users\Matt\Desktop\ExcelFile1.xls")

' Assign the result of your CountA function used in the next line
Dim numberofrows As Integer

' Get the bottom-most row number needed to complete our dynamic range address
numberofrows = 13 + excelapp.Application.CountA(wb.worksheets("Dynamic").Range("A14:A2000"))

' Delete any previous access table, otherwise the next line will add an additional table each time it is run
DoCmd.DeleteObject acTable, "ExcelDynamicRangeData"

' Import data from Excel using a range that now knows where bottom row number is
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ExcelDynamicRangeData", "C:\Users\Matt\Desktop\ExcelFile1.xls", True, "Dynamic!A14:EL" & numberofrows

' Close and clean
wb.Close
Set wb = Nothing
excelapp.Quit
Set excelapp = Nothing

End Sub    

Private Sub Command0_Click()

ImportDataFromRange

End Sub
```
Statique De La Gamme:

Beaucoup plus simple car il n'y a pas besoin d'ouvrir le classeur Excel pour appliquer un CountA fonction sur les données.

Les références que vous aurez besoin de changer en fonction de votre propre mis en place:
- C:\Users\Matt\Desktop\ExcelFile2.xls Remplacer par le chemin complet de votre fichier Excel
- ExcelStaticRangeData Remplacer par ce que vous voulez l'appeler la table dans Access qui contient les données extraites à partir de votre gamme Excel.
- Static!A14:EL20 Remplacer par le nom de votre feuille, et la gamme complète de l'adresse des cellules dans Excel que vous souhaitez importer. Comme cette méthode est de décrire comment obtenir un statique de la plage de données à partir d'Excel dans l'Accès à vos données dans Excel, vous êtes désireux d'importation ne devrait jamais sortir de cette gamme.
- Command0 Remplacer par le nom de votre bouton utilisé pour initier le présent code.
```
Sub ImportDataFromRange()

' Delete any previous access table, otherwise the next line will add an additional table
DoCmd.DeleteObject acTable, "ExcelStaticRangeData"

' Import data from Excel using a static range
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ExcelStaticRangeData", "C:\Users\Matt\Desktop\ExcelFile2.xls", True, "Static!A14:EL20"

End Sub    

Private Sub Command0_Click()

ImportDataFromRange

End Sub
```
NB:
- La acSpreadsheetTypeExcel9 peu dans les deux méthodes se réfère à la version du fichier Excel à importer; dans mon exemple, je suis de l'importation de format Excel 2000; vous pouvez importer une autre version d'Excel afin reportez-vous à cette pour voir quelle version vous avez besoin de faire référence dans le code; .xlsx fichiers ne sont pas répertoriés, mais ce serait acSpreadsheetTypeExcel12Xml.
- Mon exemple, regroupe les données pour Accéder à un lien actif vers la source de données. Vous pouvez trouver de mieux à fait d'importer et de stocker les données dans l'Accès à la place si vous avez beaucoup de données, un lien peut causer certains problèmes de performances. Swap-out acLink pour acImport si c'est le cas.
- Si vous ne l'avez pas déjà créé manuellement une table vide dans l'Accès (avec le même nom de la table référencée dans votre code), alors vous devrez soit faire ou commentaire-out DoCmd.DeleteObject acTable, "yourAccessTable" la première fois que le code est exécuté, puis rétablir cette partie par la suite.
Il est plus probable qui peut être fait à ce sujet - c'est à dire l'adaptation de la fonction Nbval à prendre en compte de la dynamique des numéros de colonnes si votre source de données à un nombre variable de colonnes.

Un autre grand merci à @david-zemens, @gord-thompson ainsi que d'autres StackoverFlow utilisateurs pour m'aider à obtenir ce que ça va être très utile pour moi et j'espère que d'autres.

OriginalL'auteur Matt Hall
2

Je ne suis pas un Accès gars, mais laissez-moi vous donner quelques conseils qui devraient être en mesure de vous aider.

1 de la literie/onglets, appelons-la "Dynamique", a une plage de données que je veux
disponible comme une table dans Access, mais ses en-têtes de colonne de commencer à la ligne
14, traverser la colonne de EL. Ce que je voudrais que l'Accès à faire est de prendre
cette plage de données comme un tableau. En outre, "ExcelFile1.xls"
mis à jour régulièrement, c'est à dire une nouvelle version de la "ExcelFile.xls"
le fichier sera disponible, mais avec plus de données ci-dessous la ligne 14 de la colonne
en-têtes, donc idéalement, j'aimerais que l'Accès à ramasser les nouvelles données
cette gamme à chaque fois que je remplace la précédente version de
"ExcelFile1.xls".

OK, sur cette feuille de calcul que vous voulez mettre en place une dynamique de la Plage Nommée. En gros, vous créez une formule qui détermine/redimensionne la plage chaque fois que de nouvelles données sont ajoutées.

Pour un aperçu de la façon de créer de la dynamique des plages nommées, commencez ici:

http://support.microsoft.com/kb/830287

Avec VBA, puis, le pire scénario est que vous pouvez accéder à cette plage nommée, lire son contenu dans une variable de tableau ou tout simplement effectuer une itération sur les lignes/colonnes, puis d'écrire le contenu à votre table Access. Mais, n'étant pas un Accès programmeur, il peut être légèrement plus efficace façons de le faire.

Ce qui a une feuille/onglet, nous allons l'appeler "Statique", qui de même a une
la plage de données que je veux comme une table dans Access, et encore une fois, il n'y aura plus récente
les versions de "ExcelFile2.xls" qui va remplacer les versions précédentes
que je serait l'idéal, comme l'Accès à la ramasse. Cette gamme est A14:O19
et sera toujours de cette gamme (c'est à dire statique de la gamme).

Vous pouvez également créer une autre Plage Nommée qui définit comme =$A$14:$O$19 qui sera statique de la plage nommée. Ensuite, vous pouvez le traiter comme ci-dessus.

MODIFIER Voici un exemple de récupération de données Excel et ensuite effectuer une itération sur les lignes & les colonnes, vous avez juste besoin d'ajouter du code pour ajouter les champs/dossiers/etc de la table dans Access.
```
Sub ImportDataFromRange()
'Access variables
Dim dbFile As Database
Dim tbl As TableDef, fld As Field

'Excel variables
Dim xlApp As Excel.Application
Dim xlFile As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim r#, c#
Dim clVal As String 'string to hold cell's value, may need to modify this type.

Set dbFile = CurrentDb

'Use this to create a new table definition
'    Set tbl = dbFile.CreateTableDef("Test")
'Use this if your table already exists:
    Set tbl = dbFile.TableDefs("Test")

'Get the info from Excel:
Set xlApp = New Excel.Application

Set xlFile = xlApp.Workbooks.Open("C:\Users\david_zemens\desktop\Book1.xlsx")
Set xlSheet = xlFile.Sheets("Sheet1")
Set xlRange = xlSheet.Range("A1:B10")

    For r = 1 To xlRange.Rows.Count
        For c = 1 To xlRange.Columns.Count

            'Add code to append new fields/records/etc to your table

        Next c
    Next r

xlApp.Quit
Set xlApp = Nothing


End Sub
```
Espère que c'est assez pour vous obtenir a commencé!

Merci David. Je suis familiarisé avec la création d'une gamme dynamique dans Excel déjà, mais comme vous, qui sont moins familiarisés avec l'aide de Access VBA pour l'amener à l'Accès. Peut-être quelqu'un d'autre lecture de ce qui pourrait me faire sur la syntaxe VBA, si c'est la meilleure façon d'aborder cette question.
J'ai ajouté un exemple qui n'est tout mais construire la table (car je ne sais pas comment le faire). Mais il devrait commencer au moins à la lecture dans les données Excel, et à parcourir les lignes/colonnes. Ensuite, vous pouvez simplement ajouter un peu de code à écrire sur la table.
Merci David. Je suppose que le Set xlRange = xlSheet.Range("A1:B10") partie accepte une plage dynamique j'ai précisé dans Excel, c'est à dire la substitution de la A1:B10 bits pour le nom que j'ai donner de la plage dynamique?
Je serais enclin à utiliser le code de cette réponse dans un Accès de Fonction VBA pour "traquer" la plage de cellules dans Excel et de retour que la gamme comme une chaîne de caractères. Alors je voudrais utiliser la chaîne comme le dernier argument de DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "ExcelLinkedData", "C:\__tmp\Book1.xlsx", True, "Sheet1!B4:D6". Qui va créer une "table liée" dans Access qui contient les données Excel.
Je vais marquer le vôtre, la réponse @David-Zemmens, comme je suis sûr que les lecteurs qui sont un peu plus VBA lettré que moi, il sera en mesure de comprendre comment obtenir votre code et @Gord-Thompson suggestion de travailler ensemble. J'ai demandé à une autre question d'ailleurs qui pourrait m'aider à obtenir ce dont j'ai besoin dans un plus simple (pour moi). Depuis la plage j'ai besoin de passer à l'argument @Gord-Thompson a proposé est toujours la même position de départ et le même nombre de colonnes, tout ce que je vraiment besoin est un Accès à déterminer combien de lignes il y a, donc, je peux passer à quelque chose comme "Sheet!B4:D" & "rowVariable"

OriginalL'auteur David Zemens

Vous devez vous connecter pour publier un commentaire.