Import select data from .xls to Access (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 07:20
Joined
Jan 14, 2017
Messages
18,186
However I wrongly attributed it to Gasman! Since corrected!
 

oxicottin

Learning by pecking away....
Local time
Today, 03:20
Joined
Jun 26, 2007
Messages
851
I missed something sorry thought I was finished.... I also needed to import a single row into a separate table lets call it tbl_TEST with a field called F1. I have imported the row BUT this single line has a bunch of jumbled up text and I need it to say:

Report Runtime: 05/11/2021 07.27.02 AM Days Out : 10

The rows text will stay the same except for the number of "Days Out" and the date and time. Below is the jumbled up text, what would yo do to retrieve the data I needed?

Report Runtime: 05/11/2021 07.27.02 AM SCM_Grid_Supply_Demand_Analysis1 - Grid Supply Demand Analysis Item Number : '%' , Planner Code : '%' , Days Out : '10' , Organization Name : 'Seirzon - 785' , Item Type : '%'
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:20
Joined
May 7, 2009
Messages
19,169
Use Split() function.
 

oxicottin

Learning by pecking away....
Local time
Today, 03:20
Joined
Jun 26, 2007
Messages
851
Ok I thought I had it but when I distributed someone said there was an error.

Error.JPG


Now I looked at the Help and not really sure why I am getting this error?
 

Isaac

Lifelong Learner
Local time
Today, 00:20
Joined
Mar 14, 2017
Messages
8,738
click debug and let us know which line of code is highlighted
 

oxicottin

Learning by pecking away....
Local time
Today, 03:20
Joined
Jun 26, 2007
Messages
851
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl_ImportedGridSupplyDemandAnalysis", .SelectedItems(1), True, "A3:J205"

What's weird is if I open the newly downloaded workbook and open it and save it then open access and try importing the file it works just fine BUT if I don't open and save it first then I get this error.
 

isladogs

MVP / VIP
Local time
Today, 07:20
Joined
Jan 14, 2017
Messages
18,186
.SelectedItems(1) has no meaning if the Excel file isn't open
 

Isaac

Lifelong Learner
Local time
Today, 00:20
Joined
Mar 14, 2017
Messages
8,738
What's weird is if I open the newly downloaded workbook and open it and save it then open access and try importing the file it works just fine BUT if I don't open and save it first then I get this error.
please post whole code
 

oxicottin

Learning by pecking away....
Local time
Today, 03:20
Joined
Jun 26, 2007
Messages
851
click debug and let us know which line of code is highlighted

I did give what was asked.... The highlighted code, below is the whole code.


Code:
Private Sub cmdSelectXLSFile_Click()
'Requires reference to Microsoft Office 12.0 Object Library.
    
    Dim fDialog As Office.FileDialog 'Declares variable
    Dim varfile As Variant 'Variant type variable that will store the selected file path
    Dim myCurrentDir As String
    
'Dialog box to open to users Desktop
    myCurrentDir = "C:\Documents and Settings\" & Environ("username") & "\Desktop\"
    
'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker) 'Instantiates the variable creating a filepicker object using late binding
    With fDialog
        .AllowMultiSelect = False 'Does not allow selecting more than one file
        .InitialFileName = myCurrentDir 'Open the users desktop folder
        .Title = "Please Select Your Grid Supply Demand Analysis File." 'Set the file dialog title
        .Filters.Clear 'Clears the file dialog file type existing filters
        .Filters.Add "Excel", "*.xls" 'This file dialog will only allow the selection of .xls files
        .Show
        
        If .SelectedItems.Count = 1 Then 'A file was selected
        
'**************************************************************************************************Import Excel Data Start

'Clear the existing table
            CurrentDb.Execute "DELETE * FROM tbl_ImportedGridSupplyDemandAnalysis", dbFailOnError
            
'Import from selected .xls file to tbl_ImportedGridSupplyDemandAnalysis table
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl_ImportedGridSupplyDemandAnalysis", .SelectedItems(1), True, "A3:J205"
            
'**************************************************************************************************Cleanup Imported Data Start
            
'Delete not needed rows in tbl_ImportedGridSupplyDemandAnalysis
            Call RemoveOffendingRows
            
'Delete not needed phrases in tbl_ImportedGridSupplyDemandAnalysis
            Call RemoveOffendingPhrases
            
'Trims spaces in front of Discription and at the end in tbl_ImportedGridSupplyDemandAnalysis
            Call LTrimRTrimLeadingSpace
            
'**************************************************************************************************Import Excel Criteria Start

'Clear the existing info in table tbl_ImportDemandAnalysisCriteriaUsed
            CurrentDb.Execute "DELETE * FROM tbl_ImportDemandAnalysisCriteriaUsed", dbFailOnError
            
'Import from selected .xls files row to tbl_ImportDemandAnalysisCriteriaUsed table
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl_ImportDemandAnalysisCriteriaUsed", .SelectedItems(1), False, "A1:A1"
            

'Give completed message
            'MsgBox ("The selected file: " & .SelectedItems(1) & " was successfully imported!")
             MsgBox ("The selected file was successfully imported!"), vbInformation, "Import Results"
            
'Open the report to print
        DoCmd.OpenReport "rpt_GridSupplyDemandAnalysis", acViewPreview, "", "", acWindowNormal
        Me.Visible = False ' Hide the switchboard
        
        Else 'No file was selected
            MsgBox ("No file was selected."), vbInformation, "No File"
        End If
    End With
End Sub
 

Isaac

Lifelong Learner
Local time
Today, 00:20
Joined
Mar 14, 2017
Messages
8,738
.SelectedItems(1) has no meaning if the Excel file isn't open
Although that code is very reminiscent of (bad) Excel vba code, in OP's case it's a reference to the file dialogue stuff and seems valid as long as it's inside the With block (which makes everything more confusing IMHO).
 

oxicottin

Learning by pecking away....
Local time
Today, 03:20
Joined
Jun 26, 2007
Messages
851
No not missing any headers. I think it has to do with the .xls, Recently we introduced a label you have to set for everything (image below).

Capture.JPG



What's weird is when I open the .xls it makes itself really small (like 1"X3") then expands and adjusts itself. It has no macros that I can see... But after I open the xls file and close and save THEN open access and open the same xls it then open fine without the error.
 

Users who are viewing this thread

Top Bottom