Input box for date

[juneau730]

Good day all, I need to pick the brain of the gurus here, to help me figure out one last part of the code I am working on.
What I need, if possible, is for an Input box to prompt the user to input a date to an excel, prior to importing to access.

Here is the over all code of the process for this particular section. I would need this to happen after the bold red line, where it would input the date into O2:O7500

Sub UpdateEPSScan()
    Dim objShell As Object
    Dim strEPSScan As String
    Dim blnContinue, strQuestion
    Dim objExcel
    
    strQuestion = "This action will restrict all database activity until completed. " _
        & "Do you wish to continue?"
    
    blnContinue = MsgBox(strQuestion, vbYesNo, "Import EPS Scan")
    
    If blnContinue = vbNo Then
        Forms![_Navigation Form].NavBtnEPSMonitors.SetFocus
        Exit Sub
    End If
    
 Set objShell = VBA.CreateObject("wscript.shell")
    strEPSScan = Application.CurrentProject.Path & "\eMASS_Scans\MainReport_DrillIn_PhyLoc.xlsx"
    
 Set objExcel = CreateObject("Excel.Application")
 With objExcel
   .Workbooks.Open strEPSScan
   .Visible = False
   .Sheets(1).Rows(2).EntireRow.Delete
   .Sheets(1).Rows(1).EntireRow.Delete
   .Sheets(1).Columns("C:J").EntireColumn.Delete
   .Sheets(1).Rows(1).EntireRow.Delete
   .Sheets(1).Range("O1").Select
[B][COLOR=rgb(184, 49, 47)]   .ActiveCell.FormulaR1C1 = "Date of Scan"[/COLOR][/B]
   .ActiveWorkbook.Close (True)
   .Quit
End With
 
 MyFunctions.SleepNow 2000
        
    Forms![_Navigation Form].NavigationSubform.Form.Requery
 
  DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM [tbl EPS Scan]"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl EPS Scan", strEPSScan, True
    DoCmd.SetWarnings True
    Forms![_Navigation Form].NavigationSubform.Form.Requery
    
End Sub

 

[JonXL]

What does "Date of scan" represent?

 

[juneau730]

What does "Date of scan" represent?

A date.

Column O already exists on the spreadsheet, but does not have a Header. "Date of Scan" is the header name being created for that column

 

[juneau730]

So, I think calling a sub from within the main sub might be the best way to attack this. I've modified the original code to call a new sub named EnterDateofScan, and it's partly working.

The new sub works properly in an xlsx file, but not in Access, it's erroring out on the Range line.

Sub EnterDateofScan_Click()
Dim dte As Date
mbox = InputBox("Enter Date of Scan")
If IsDate(mbox) Then
dte = CDate(mbox)
Range("O2:O7500") = dte
End If
End Sub

 

[Micron]

There is no such thing as a Range object in Access. If you want to affect a range or cell in Excel from Access code you will have to use automation:

EXCEL Import And Export

I use the same naming convention, so I suspect bln and str are Boolean and String. Both of those variables are Variants because you have not explicitly declared them.

Dim blnContinue, strQuestion

Use blnContinue As Boolean, strQuestion As String if that was the intent.

 

[welshgasman]

Plus for column O, whatever that will be in the Access table, you may as well run an update query to update that field with the required date?