Import from Excel (variable ranges) (1 Viewer)

[Arvidsson]

Hello,

I am trying to import data from an Excel file to MS Access (2013).

The Excel sheet consists of 700 columns. A group of 7 columns (always same header) are to be implement in Access one among the other.

That means:
At first columns A-G, than columns H-N, than columns O-U
I am trying to solve this with a loop (as you can see in the code)

But, if I start the modul I get this error:
"Runtime error 1004
The method Worksheets for _Global object failed"
But the modul works, of I start it a second time. The error message appears only at the first start.

Apart from that, the code works. Sometimes, the modul imported empty rows into the Access table.

Does anyone have an idea for this two problems?

Thank you very much in advance!

Sub ExcelImport()
   
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application

xlApp.Workbooks.Open FileName:="C:\Users\...\Desktop\Beispiel.xlsx"
      
Dim Feld As String
Dim FeldBeginn As String
Dim FeldEnde As String
Dim FeldBeginn2 As String
Dim FeldEnde2 As String
Dim n As Integer
Dim m As Integer
Dim Page As Worksheet

n = 1
m = n + 6

For i = 1 To 100

Feld = Worksheets("Tabelle1").range(Worksheets("Tabelle1").Cells(1, n), Worksheets("Tabelle1").Cells(25000, m)).Address

Select Case m
Case Is < 28
FeldBeginn = Left(Feld, 4)
FeldEnde = Right(Feld, 7)
FeldBeginn2 = Mid(FeldBeginn, 2, 1) & Right(FeldBeginn, 1)
FeldEnde2 = Left(FeldEnde, 1) & Right(FeldEnde, 5)
Feld = FeldBeginn2 & ":" & FeldEnde2
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "Kurse", "C:\Users\...\Desktop\Beispiel.xlsx", True, Feld
n = n + 7
m = n + 6

Case Is = 28
FeldBeginn = Left(Feld, 4)
FeldEnde = Right(Feld, 8)
FeldBeginn2 = Mid(FeldBeginn, 2, 1) & Right(FeldBeginn, 1)
FeldEnde2 = Left(FeldEnde, 2) & Right(FeldEnde, 5)
Feld = FeldBeginn2 & ":" & FeldEnde2
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "Kurse", "C:\Users\...\Desktop\Beispiel.xlsx", True, Feld
n = n + 7
m = n + 6

Case Is > 28
FeldBeginn = Left(Feld, 5)
FeldEnde = Right(Feld, 8)
FeldBeginn2 = Mid(FeldBeginn, 2, 2) & Right(FeldBeginn, 1)
FeldEnde2 = Left(FeldEnde, 2) & Right(FeldEnde, 5)
Feld = FeldBeginn2 & ":" & FeldEnde2
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "Kurse", "C:\Users\...\Desktop\Beispiel.xlsx", True, Feld
n = n + 7
m = n + 6
End Select

Next i
    
xlApp.Quit
Set xlApp = Nothing

End Sub

 

[KenHigg]

Just a quick question - How does the '/.../' thing work in the file location reference, seems it'd be an issue...