msgbox before opening a excel sheet

ok,

sorry i got mixed up with the words. (Englisch is not my mother tongue)


Tabs are Sheets.

Sheets are workbooks.


i want that the data gets transfered allways to the Special workbook in sheet1 (the Special workbook you can find in the Appendix)

The informations from e.g. SuWID1 go to the Standard Excel workbook (sheet1)

The Information from e.g. SuWID2 go the Standard Excel workbook, but then in sheet 2. (In sheet 2 is then also already the Standard Excel sheet1)

The Information from e.g. SuWID3 go to the Standard Excel workbook, but then in sheet 3. (In sheet 3 is then also already the Standard Excel sheet1)

Hope you understood it now.

greetz benjamin
 

Attachments

How about?
Code: 
Set xlSheet = xlBook.Sheets("SuWID - " & rstID.Fields("SuWID"))
Just make sure you name/refer the Sheets correctly.. The above is just a sample, you need to change it as your sheets name would actually be..
 
hi Paul eugin

i get a runtime error.


The error number is 9.

error message is index out of the range.



I Named the sheet 1,2,3 like the examples for the SuWID.


i will attach my database.



Code: 
Private Sub Befehl0_Click()
    Dim xlApp As Object         'Excel.Application
    Dim xlBook As Object        'Excel.Workbook
    Dim xlSheet As Object       'Excel.Worksheet
    Dim rstID As DAO.Recordset, tmpStr As String
    Dim rstGr As DAO.Recordset, strSQL As String
 
    strSQL = "SELECT SuWID FROM Abfrage GROUP BY SuWID;"
    Set rstID = CurrentDb.OpenRecordset(strSQL)
    If rstID.RecordCount > 0 Then
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        Set xlBook = xlApp.Workbooks.Open("C:\Users\GRIMBEN\Desktop\Mappe2.xlsx")
        Do While Not rstID.EOF
 
 
[COLOR=red]           [B]Set xlSheet = xlBook.Sheets("SuWID" & rstID.Fields("SuWID")):confused::confused: (Here is the debug)[/B][/COLOR]
 
 
 
 
 
            xlSheet.Name = "SuWID" & rstID.Fields("SuWID")
 
 
            Set rstGr = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SuWID, Jahr FROM Abfrage WHERE SuWID = " & rstID.Fields("SuWID"))
            xlSheet.Range("A2").CopyFromRecordset rstGr
            rstGr.Close
            rstID.MoveNext
        Loop
    Else
        MsgBox "No information to export", vbInformation, "No data exported"
    End If
    rstID.Close
    Set rstID = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub
 

Attachments

Okay not sure if you read my post..
pr2-eugin said:
Just make sure you name/refer the Sheets correctly.. The above is just a sample, you need to change it as your sheets name would actually be..
Click to expand...
Is your sheet names named as SuWID1, SuWID2, SuWID3, SuWID4 etc? Last time I remeber you mentioned.
benjamin.grimm said:
The informations from e.g. SuWID1 go to the Standard Excel sheet (tabelle1)

The Information from e.g. SuWID2 go the Standard Excel sheet, but then in tab 2. (In tab 2 is then also already the Standard Excel sheet)

The Information from e.g. SuWID3 go to the Standard Excel sheet, but then in tab 3. (In tab3 is then also already the Standard Excel sheet)
Click to expand...
Change the line of code to..
Code: 
        Do While Not rstID.EOF
            [COLOR=Blue][B]Set xlSheet = xlBook.Sheets("Tabelle" & rstID.Fields("SuWID"))[/B][/COLOR]
            [COLOR=Green]'The next line will rename the Sheet from Tabelle to SuWID. If you do not want 
            'this to happen comment it out.[/COLOR]
            xlSheet.Name = "SuWID" & rstID.Fields("SuWID")
            Set rstGr = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SuWID, Jahr FROM Abfrage WHERE SuWID = " & rstID.Fields("SuWID"))
            xlSheet.Range("A2").CopyFromRecordset rstGr
            rstGr.Close
            rstID.MoveNext
        Loop
 
Great!

Great, great, great.


Thank you so much.



But as you know me one further question i still have.

I put in this Special workbook the following code:

Code: 
Sub workbook_open()
 
Worksheets("Tablle1").Range("A2:E14").ClearContents
 
 End Sub

Is it also possible to put this code in the vba code from the Access.

And there is still another Problem. I want that the range A2:E14 gets deleted in every sheet.

Thanks in advance

greetz benjamin
 
Before starting to loop, use..
Code: 
        Set xlBook = xlApp.Workbooks.Open("C:\Users\GRIMBEN\Desktop\Mappe2.xlsx")
        [COLOR=Blue][B]Set xlSheet = xlBook.Sheets("Tablle1")
        xlSheet.Range("A2:E14").ClearContents[/B][/COLOR]
        Do While Not rstID.EOF
            Set xlSheet = xlBook.Sheets("Tabelle" & rstID.Fields("SuWID"))
            Set rstGr = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SuWID, Jahr FROM Abfrage WHERE SuWID = " & rstID.Fields("SuWID"))
            xlSheet.Range("A1").CopyFromRecordset rstGr
            rstGr.Close
            rstID.MoveNext
        Loop
 
Glad you have it all sorted finally.. Good luck.. :)

If you have any more doubts post up a new thread, we will try our best to help you out.. :)
 
Thank you. I have already a other question. I will put it in a other thread.
 
hey there is one more question on the code.

I figured out, that it is stupid to put the data allways in a specific file path.

So i thought that the user can chosse it directly.

I tried to "improve" the code, but there is a error.


Do you know why? (i am sure, cause you are a genious)

Code: 
Private Sub Befehl0_Click()
    Dim xlApp As Object         'Excel.Application
    Dim xlBook As Object        'Excel.Workbook
    Dim xlSheet As Object       'Excel.Worksheet
    Dim rstID As DAO.Recordset, tmpStr As String
    Dim rstGr As DAO.Recordset, strSQL As String
    Dim filepath As String
    Dim dlg As FileDialog
 
    Dim strPfad As String
 
    Set dlg = Application.FileDialog(msoFileDialogOpen)
 
     dlg.Title = "Bitte geben Sie den Pfad der Exceldatei!"
     dlg.InitialFileName = "C:\"
     dlg.ButtonName = "speichern"
     dlg.Filters.Clear
     dlg.Filters.Add "Excel", "*.xlsm"
 
    strSQL = "SELECT SuWID FROM Abfrage_alles GROUP BY SuWID;"
    Set rstID = CurrentDb.OpenRecordset(strSQL)
    If rstID.RecordCount > 0 Then
        If dlg.show Then
 
        filepath = dlg.SelectedItems(1)
 
      [B]  Set xlSheet = xlBook.Sheets("Tabelle1")   (here is the debug)[/B]
        xlSheet.Range("A2:E14").ClearContents
 
        Do While Not rstID.EOF
            Set xlSheet = xlBook.Sheets("Tabelle" & rstID.Fields("SuWID"))
 
 
            xlSheet.Name = "SuWID" & rstID.Fields("SuWID")
            Set rstGr = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SuWID, Jahr FROM Abfrage_alles WHERE SuWID = " & rstID.Fields("SuWID"))
            xlSheet.Range("A2").CopyFromRecordset rstGr
            rstGr.Close
            rstID.MoveNext
        Loop
    Else
        MsgBox "No information to export", vbInformation, "No data exported"
    End If
    End If
 
    rstID.Close
    Set rstID = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
 
Your problem is that xlbook has not been set yet...
i.e. there is no line to create the excel file like in the sample provided above:
Set xlBook = xlApp.Workbooks.Open("C:\Users\GRIMBEN\Desktop\Mappe2.xlsx")

which probably should be re-writen to something like:
Set xlBook = xlApp.Workbooks.Open(filepath & "\Mappe2.xlsx")
 
Well i tried this what you said, but there is still an error.

The error is named

run-time error 91 object variable or with block variable not set


Here is my code:

Code: 
Dim xlApp As Object         'Excel.Application
    Dim xlBook As Object        'Excel.Workbook
    Dim xlSheet As Object       'Excel.Worksheet
    Dim rstID As DAO.Recordset, tmpStr As String
    Dim rstGr As DAO.Recordset, strSQL As String
    Dim filepath As String
    Dim dlg As FileDialog
 
    Dim strPfad As String
 
    Set dlg = Application.FileDialog(msoFileDialogOpen)
 
     dlg.Title = "Bitte geben Sie den Pfad der Exceldatei!"
     dlg.InitialFileName = "C:\"
     dlg.ButtonName = "speichern"
     dlg.Filters.Clear
     dlg.Filters.Add "Excel", "*.xlsm"
 
    strSQL = "SELECT SuWID FROM Abfrage_alles GROUP BY SuWID;"
    Set rstID = CurrentDb.OpenRecordset(strSQL)
    If rstID.RecordCount > 0 Then
        If dlg.show Then
 
        filepath = dlg.SelectedItems(1)
 
    [B][U]    Set xlBook = xlApp.Workbooks.Open(filepath & "\Mappe1.xlsm")  (here is the debug)[/U][/B]
 
 
        Set xlSheet = xlBook.Sheets("Tabelle1")
        xlSheet.Range("A2:E14").ClearContents
 
        Do While Not rstID.EOF
            Set xlSheet = xlBook.Sheets("Tabelle" & rstID.Fields("SuWID"))
 
 
            xlSheet.Name = "SuWID" & rstID.Fields("SuWID")
            Set rstGr = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SuWID, Jahr FROM Abfrage_alles WHERE SuWID = " & rstID.Fields("SuWID"))
            xlSheet.Range("A2").CopyFromRecordset rstGr
            rstGr.Close
            rstID.MoveNext
        Loop
    Else
        MsgBox "No information to export", vbInformation, "No data exported"
    End If
    End If
 
    rstID.Close
    Set rstID = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
 
The best tool a Programmer could have is the Debugging technique.. You should do a bit of debugging. The file selected would return the Path and File Name.. You do not have to append the File name again.. Try..
Code: 
filepath = dlg.SelectedItems(1)
MsgBox filepath
Set xlBook = xlApp.Workbooks.Open(filepath)
See what you get in the MsgBox.
 
what is the content in filepath variable, if that contains the \ at the end then you need to remove it in the string behind it

Further more if you are opening a file (.open) the file must already exist, where as if I understand your problem you are trying to create and then save a (new) file
If you want to create a blank workbook you need to be using
Set xlBook = xlApp.Workbooks.Add

A VERY detailed example of writing information to excel can be found here:
http://access.mvps.org/access/modules/mdl0035.htm
 
Ok,


this is a good idea with the msgbox.

I get the filepath, which i was choosing.

Code: 
        filepath = dlg.SelectedItems(1)
        
        MsgBox filepath
        
        
        [B][U]Set xlBook = xlApp.Workbooks.Open(filepath)
[/U][/B]        

        Set xlSheet = xlBook.Sheets("Tabelle1")

The msgbox Shows me C:\Users\GRIMBEN\Desktop\Mappe1.xlsm.

But why do i have still the run-time error 91 object variable or with block variable not set? :confused:

Cause it is set or not? :confused::confused:

I do the same acutally with importing data to the database. This works perfectly well. I almost have the same code.


To namliam:

No i wanna choose a prepared Excel file, which is allways in different filepath´s. It depends on which user is registered.
 
Save the File as a normal XLS file.. As you do not have any macros, we clear the contents in Access VBA remember so they are not required..
 
You must log in or register to reply here.

Similar threads

Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
732
Gasman
Gasman
zelarra821
Solved Export to Excel and leave the workbook open
Replies
5
Views
765
zelarra821
zelarra821
MattBaldry
Solved Export and Format Excel
Replies
7
Views
1,161
Gasman
Gasman
R
Updating Excel spreadsheet from within the Access
Replies
8
Views
1,618
CJ_London
C
M
Solved Access to Excel link
Replies
8
Views
1,138
bastanu
bastanu

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom