Using Forum to Submit Excel problems

N

Noway

Registered User.
Local time
Today, 13:24
Joined
Dec 18, 2013
Messages
10
Hello,
I am working with Access 2010. I have a big Access Database that I got when I took the place of someone else. The database has never really worked correctly as I am told. I would like to see if this is a common problem which it is weird that the DB does this crazy stuff.
I have a form that I have an option to upload an excel sheet. When I press the button the excel sheet uploads, it pops up, then when I open the table it should be going to it only uploads some of the data but not all the data, and when I reopen the excel sheet all the data is gone / columns are renamed.
Here is the Module code.
Public Function RunExcel()

Dim oExcel As Object
Dim afe As Excel.Application
Dim Filename As String
Filename = Forms!Startup!txtAddFile.Value

'oWB.Open (Filename)

Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open (Filename)


oExcel.Visible = True
With oExcel
.Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,W:W,Y:Y,Z:BQ"). _
Select
.Range("Y1").Activate
.Selection.Delete Shift:=xlToLeft
.Range("A1").Select
.ActiveCell.FormulaR1C1 = "Item_name1"
.Range("B1").Select
.ActiveCell.FormulaR1C1 = " Item_name2"
.Range("C1").Select
.ActiveCell.FormulaR1C1 = " Item_name3"
.Range("D1").Select
.ActiveCell.FormulaR1C1 = " Item_name4"
.Range("E1").Select
.ActiveCell.FormulaR1C1 = " Item_name5"
.Range("F1").Select
.ActiveCell.FormulaR1C1 = " Item_name6"
.Range("G1").Select
.ActiveCell.FormulaR1C1 = " Item_name7"
End With
oExcel.DisplayAlerts = False
oExcel.Save
oExcel.Quit
oExcel.DisplayAlerts = True

Set oExcel = Nothing


I would like to know what the problem is first, and when I fix it I would like for the excel sheet information not to be deleted.
 
Don't think it is a common problem - Looks like a crazy way to load data from excel - you just seem to be excluding two columns (V & X). Suggest you investigate using the transfertext function instead.

I can see in the code there is something that deletes data in the spreadsheet

Selection.Delete Shift:=xlToLeft

Cells are repopulated
ActiveCell.FormulaR1C1 = "Item_name1"

and then it is saved

oExcel.Save


Also, I can't see anything which updates a table in Access
 
Why would I even need this? Why would I want the DB to even touch the excel sheet? I really just want it to fill in the slots on the table, and nothing else? Could I delete this Module?
 
Why would I even need this? Why would I want the DB to even touch the excel sheet?
Click to expand...
Don't know, but your predessor presumably thought it was necessary - do you have any documentation you can refer to?
Could I delete this Module?
Click to expand...
Not for me to say, but can you delete a module - yes, you can.

Before deleting it, suggest you copy it elsewhere or rename it in case it is referred to elsewhere. You can also search all modules for 'RunExcel' to see if it is called from somewhere else - the fact it is public implies it will be. Not sure if you are using macros, but if you are you would beed to search these separately.
 
Yes, there are some Macros. The guy did not leave anything.
 
So I know the problem I think..when it renames the colums it is deleteing all the information in those colums. Why is it deleteing all the data? I know you said the

Selection.Delete Shift:=xlToLeft

Should this only delete the first colum?
 
I'm a bit rusty on Excel vba but

xlToLeft
Click to expand...
is the equvalent of hitting end-left in the spreadsheet - i.e. it will go as far left as it can until it comes to a cell without data (or a cell with data if the starting position is empty)
 
Code: 
.Range("Y1").Activate
.Selection.Delete Shift:=xlToLeft

IMO
This selects cell Y1, then deletes it and moves the rest of row1 1 cell to the left.

But to be honest I'm puzzled by the whole module.

Brian
 
@Brian - you could be right - I said I was rusty:)
 
CJ_London said:
@Brian - you could be right - I said I was rusty:)
Click to expand...

So am I , it's 8 years next month since I retired and except for messing on here I've done nothing, but I no longer have Access so I'm even less use now. :D

The poster should be able to see exactly what happens to the spreadsheet.

Brian
 
Noway said:
Hello,
I am working with Access 2010. I have a big Access Database that I got when I took the place of someone else. The database has never really worked correctly as I am told. I would like to see if this is a common problem which it is weird that the DB does this crazy stuff.
I have a form that I have an option to upload an excel sheet. When I press the button the excel sheet uploads, it pops up, then when I open the table it should be going to it only uploads some of the data but not all the data, and when I reopen the excel sheet all the data is gone / columns are renamed.
Here is the Module code.
Public Function RunExcel()

Dim oExcel As Object
Dim afe As Excel.Application
Dim Filename As String
Filename = Forms!Startup!txtAddFile.Value

'oWB.Open (Filename)

Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open (Filename)


oExcel.Visible = True
With oExcel
.Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,W:W,Y:Y,Z:BQ"). _
Select
.Range("Y1").Activate
.Selection.Delete Shift:=xlToLeft
.Range("A1").Select
.ActiveCell.FormulaR1C1 = "Item_name1"
.Range("B1").Select
.ActiveCell.FormulaR1C1 = " Item_name2"
.Range("C1").Select
.ActiveCell.FormulaR1C1 = " Item_name3"
.Range("D1").Select
.ActiveCell.FormulaR1C1 = " Item_name4"
.Range("E1").Select
.ActiveCell.FormulaR1C1 = " Item_name5"
.Range("F1").Select
.ActiveCell.FormulaR1C1 = " Item_name6"
.Range("G1").Select
.ActiveCell.FormulaR1C1 = " Item_name7"
End With
oExcel.DisplayAlerts = False
oExcel.Save
oExcel.Quit
oExcel.DisplayAlerts = True

Set oExcel = Nothing


I would like to know what the problem is first, and when I fix it I would like for the excel sheet information not to be deleted.
Click to expand...

Have you actually given us the complete module? I ask because in the code shown there is an undefined object oWB and another afe that is never used.

The code as shown would only modify the Excel spreadsheet and would not write anything to an Access table.
 
CJ_London said:
oWB is actually commented out:D
Click to expand...

Mea Culpa. Eyesight not always as good as it was:).

Still not using the defined afe and still not code to extract any data from excel spreadsheet. Code just changes A1 to G1 and deletes Y1.

From OPs description Access table should be updated
 
What I thinks happens is that this set ups the excel sheet.

This is what I have:
Public Function RunExcel()
Dim oExcel As Object
Dim afe As Excel.Application
Dim Filename As String
Filename = Forms!Startup!txtAddFile.Value
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open (Filename)

oExcel.Visible = True
With oExcel
.Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,W:W,Y:Y,Z:BQ"). _
Select
.Range("Y1").Activate
.Selection.Delete Shift:=xlToLeft
.Range("A1").Select
.ActiveCell.FormulaR1C1 = "Item_Name1"
.Range("B1").Select
.ActiveCell.FormulaR1C1 = "Item_Name2"
.Range("C1").Select
.ActiveCell.FormulaR1C1 = "Item_Name3"
.Range("D1").Select
.ActiveCell.FormulaR1C1 = "Item_Name4"
.Range("E1").Select
.ActiveCell.FormulaR1C1 = "Item_Name5"
.Range("F1").Select
.ActiveCell.FormulaR1C1 = "Item_Name6"
.Range("G1").Select
.ActiveCell.FormulaR1C1 = "Item_Name7"
.Range("H1").Select
End With
oExcel.DisplayAlerts = False
oExcel.Save
oExcel.Quit
oExcel.DisplayAlerts = True
Set oExcel = Nothing
Set oWB = Nothing
End Function
Function Teststs()

Call ImportNewData2

End Function
Public Function ImportNewData2()
Dim Filename As String

Filename = "G:----.xlsx"
DoCmd.SetWarnings False

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Input_Compare", Filename, True
DoCmd.SetWarnings True
End Function
 
Thanks for the update.

The functions do the following

RunExcel reformats the spreadsheet.

ImportNewData2 imports the data from the spreadsheet to a table called "Input_compare"

Teststs just call ImportNewData2

Without knowing what the spreadsheet looks like or how Input_compare is used by the rest of the database I can't make sensible suggestions on how to change it.
 
Here is my SQL in my Module...I need it to go from one table to another table, and fill in the spaces that are open..

Public Function SQL_Input_Parts()
Dim strSQL As String
Dim Special As String
DoCmd.SetWarnings False
Special = """ / Name """
strSQL = "INSERT INTO Input_Parts ( Item_Name1, Item_Name2, Item_Name3, Item_Name4, Item_Name5," _
& " Item_Name6, Item_Name7, Item_Name8)" _
& " SELECT Input.Item_Name1, Input.Item_Name2, Input.Item_Name3, Input.Item_Name4, Input.Item_Name5," _
& " IIf(IsNull([Input].[Item_Name7]),[Input].[Item_Name6],[Input].[Item_Name6] & " & Special & " & [Input].[Item_Name7])" _
& " AS Expr1, All_Parts.Part_Name, All_Parts.Nomenclature " _
& " FROM All_Parts INNER JOIN [Input] ON (All_Parts.Item_Name7 = Input.Item_Name7) " _
& " AND (All_Parts.Part_Number = Input.Part_Number)"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Function
 
Last edited:
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
677
Gasman
Gasman
D
How to add a title to the search results in Excel 2016 using VBA
Replies
6
Views
598
Isaac
Isaac
oxicottin
Solved Export search form data to excel
Replies
11
Views
1,083
oxicottin
oxicottin
Y
Use values in Access to input in excel specific cell
Replies
6
Views
757
Yuly17
Y
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
2,052
Cotswold
Cotswold

Users who are viewing this thread

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