InputBox

[Forestq]

Hi All,

now I open file like this:

 Dim folder As String
 folder = InputBox("Location of the 'MixMax_Deleted' file :")
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Deleted", folder & "\Deleted.xls", True

Works ok.
It's possibility to do it somethig like in excel vb:

    Set wksDest = Worksheets("Sheet1")
 
    MsgBox "Open file with source data"
    strFile = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls), *.xls)", _
        Title:="Select a File", _
        MultiSelect:=False)
 
 
    If strFile = "False" Then
        Exit Sub
    End If

?

Please help me. Any advice welcome.

regards,
PvK

 

[Forestq]

I found one solution but I get error and I don't know what means (and why it is in code? why it's necessary?) second line:

Dim sFile As String, sFilter As String
WizHook.Key = 51488399
WizHook.GetFileName 0, "", "", "", _
    sFile, "", 0, 0, 0, 0, True
 
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Deleted", WizHook, True

Error:
Run-time error '2498';
An expression you entered is the wrong data type for one of the arguments

 

[Forestq]

little change:

  Dim sFile As String, sFilter As String
    
  WizHook.Key = 51488399
  sFilter = "All Files (*.*)"
  WizHook.GetFileName 0, "", "", "", sFile, "", sFilter, 0, 0, 0, True
  
  zmienna_file_name = sFile
  Open zmienna_file_name For Input As #1
  
  
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Deleted", sFile, True

Works good!

But, when I run query which delete all records from table and one again want to run this macro (to add new data), I have error than file is already open.
How to close file? (stop streaming?)
regards,
PvK

 

[Forestq]

full code,
work great:

  Dim sFile As String, sFilter As String
    
  WizHook.Key = 51488399
  sFilter = "Wszystkie pliki (*.*)"
  WizHook.GetFileName 0, "", "", "", sFile, "", sFilter, 0, 0, 0, True
  
  zmienna_file_name = sFile
  Open zmienna_file_name For Input As #1
  
  
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Deleted", sFile, True
  
  Close #1